HI everyone, hope someone can help ? :)
I have a spreadsheet which I would like to automatically email to
selected users, based on the email address entered into a cell within
the spreadsheet.
The whole spreadsheet should be sent as values only, eg: no formulae
but keep the formatting.

Is this possible ??
Thanks for any help :)
Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=513117

See www.rondebruin.nl/sendmail.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"AmyTaylor" wrote in
message ...

HI everyone, hope someone can help ? :)
I have a spreadsheet which I would like to automatically email to
selected users, based on the email address entered into a cell within
the spreadsheet.
The whole spreadsheet should be sent as values only, eg: no formulae
but keep the formatting.

Is this possible ??
Thanks for any help :)
Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=513117

Typical, Ron has deleted it.

Try this code

Sub Mail_HTML_File__Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim TempFile As String

TempFile = Environ$("temp") & "/" & ActiveWorkbook.Name & _
Format(Now, " dd-mm-yy h-mm-ss") & ".htm"

With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _

Source:=ActiveSheet.Cells.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)

End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Range("A1").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add TempFile
.send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Kill TempFile ' delete the htm file
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"AmyTaylor" wrote in
message ...

HI everyone, hope someone can help ? :)
I have a spreadsheet which I would like to automatically email to
selected users, based on the email address entered into a cell within
the spreadsheet.
The whole spreadsheet should be sent as values only, eg: no formulae
but keep the formatting.

Is this possible ??
Thanks for any help :)
Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=513117

Hi Bob

Typical, Ron has deleted it.

You have .html in the lonk

This will work
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob Phillips" wrote in message ...
Typical, Ron has deleted it.

Try this code

Sub Mail_HTML_File__Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim TempFile As String

TempFile = Environ$("temp") & "/" & ActiveWorkbook.Name & _
Format(Now, " dd-mm-yy h-mm-ss") & ".htm"

With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _

Source:=ActiveSheet.Cells.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)

End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Range("A1").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add TempFile
.send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Kill TempFile ' delete the htm file
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"AmyTaylor" wrote in
message ...

HI everyone, hope someone can help ? :)
I have a spreadsheet which I would like to automatically email to
selected users, based on the email address entered into a cell within
the spreadsheet.
The whole spreadsheet should be sent as values only, eg: no formulae
but keep the formatting.

Is this possible ??
Thanks for any help :)
Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=513117

You have .html in the lonk

In the link

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Hi Bob

Typical, Ron has deleted it.

You have .html in the lonk

This will work
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob Phillips" wrote in message ...
Typical, Ron has deleted it.

Try this code

Sub Mail_HTML_File__Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim TempFile As String

TempFile = Environ$("temp") & "/" & ActiveWorkbook.Name & _
Format(Now, " dd-mm-yy h-mm-ss") & ".htm"

With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _

Source:=ActiveSheet.Cells.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)

End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Range("A1").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add TempFile
.send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Kill TempFile ' delete the htm file
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"AmyTaylor" wrote in
message ...

HI everyone, hope someone can help ? :)
I have a spreadsheet which I would like to automatically email to
selected users, based on the email address entered into a cell within
the spreadsheet.
The whole spreadsheet should be sent as values only, eg: no formulae
but keep the formatting.

Is this possible ??
Thanks for any help :)
Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=513117

Oops, sorry Ron, I keep forgetting that you use .htm <G

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ron de Bruin" wrote in message
...
You have .html in the lonk

In the link

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message
...
Hi Bob

Typical, Ron has deleted it.

You have .html in the lonk

This will work
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob Phillips" wrote in message
...
Typical, Ron has deleted it.

Try this code

Sub Mail_HTML_File__Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim TempFile As String

TempFile = Environ$("temp") & "/" & ActiveWorkbook.Name & _
Format(Now, " dd-mm-yy h-mm-ss") & ".htm"

With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _

Source:=ActiveSheet.Cells.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)

End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Range("A1").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add TempFile
.send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Kill TempFile ' delete the htm file
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"AmyTaylor"
wrote in
message ...

HI everyone, hope someone can help ? :)
I have a spreadsheet which I would like to automatically email to
selected users, based on the email address entered into a cell within
the spreadsheet.
The whole spreadsheet should be sent as values only, eg: no formulae
but keep the formatting.

Is this possible ??
Thanks for any help :)
Amy xx


--
AmyTaylor

------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread:
http://www.excelforum.com/showthread...hreadid=513117

Hi Bob

I use Frontpage and it create a htm file

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob Phillips" wrote in message ...
Oops, sorry Ron, I keep forgetting that you use .htm <G

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ron de Bruin" wrote in message
...
You have .html in the lonk

In the link

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message
...
Hi Bob

Typical, Ron has deleted it.

You have .html in the lonk

This will work
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob Phillips" wrote in message
...
Typical, Ron has deleted it.

Try this code

Sub Mail_HTML_File__Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim TempFile As String

TempFile = Environ$("temp") & "/" & ActiveWorkbook.Name & _
Format(Now, " dd-mm-yy h-mm-ss") & ".htm"

With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _

Source:=ActiveSheet.Cells.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)

End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Range("A1").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add TempFile
.send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Kill TempFile ' delete the htm file
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"AmyTaylor"
wrote in
message ...

HI everyone, hope someone can help ? :)
I have a spreadsheet which I would like to automatically email to
selected users, based on the email address entered into a cell within
the spreadsheet.
The whole spreadsheet should be sent as values only, eg: no formulae
but keep the formatting.

Is this possible ??
Thanks for any help :)
Amy xx


--
AmyTaylor

------------------------------------------------------------------------
AmyTaylor's Profile:
http://www.excelforum.com/member.php...o&userid=20970
View this thread:
http://www.excelforum.com/showthread...hreadid=513117

You can also avoid Outlook and just use your default email program this way...

Option Explicit

Sub EmailActiveSheetWithMessage()

Dim ThisDate As String, Recipient(1 To 10) As String, N As Long

ThisDate = Format(Date, "dd mmm yy")
For N = 1 To 10
'put your email addies in Sheet1, A1:A10
Recipient(N) = Sheet1.Range("A" & N)
Next

ActiveSheet.Copy
With Cells
'get rid of formulas
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

'Send the new workbook with a message
ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Recipients = Recipient()
.Subject = "Files for " & ThisDate
.Message = "Hi," & vbNewLine & _
"" & vbNewLine & _
"Attached files are for...blah blah..." & vbNewLine & _
"...more blah here.... " & vbNewLine & _
"" & vbNewLine & _
"Regards," & vbNewLine & _
"Your name" & vbNewLine & _
"" & vbNewLine & _
"" & vbNewLine & _
"" & vbNewLine & _
""
.Delivery = xlAllAtOnce
.ReturnWhenDone = False
End With
ActiveWorkbook.Route

'Delete the senders copy of the recipients book
'(this was only a temp book for the sender)
On Error Resume Next
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False

'Let user know what's happened
MsgBox "File sent by email ", , "Emailed..."
Sheet1.Activate

End Sub
--
The major part of getting the right answer lies in asking the right
question...


"AmyTaylor" wrote:


HI everyone, hope someone can help ? :)
I have a spreadsheet which I would like to automatically email to
selected users, based on the email address entered into a cell within
the spreadsheet.
The whole spreadsheet should be sent as values only, eg: no formulae
but keep the formatting.

Is this possible ??
Thanks for any help :)
Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=513117