|
emailing a whole spreadsheet
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 |
emailing a whole spreadsheet
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 |
emailing a whole spreadsheet
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 |
emailing a whole spreadsheet
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 |
emailing a whole spreadsheet
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 |
emailing a whole spreadsheet
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 |
emailing a whole spreadsheet
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 |
emailing a whole spreadsheet
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 |
emailing a whole spreadsheet
Frontpage ... ugh ... get the garlic and the silver cross
"Ron de Bruin" wrote in message ... 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 |
emailing a whole spreadsheet
Hi Bob
Frontpage ... ugh ... get the garlic and the silver cross Most people don't like the program But updating my website is so easy and fast with Frontpage. It is Superb for me. -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Frontpage ... ugh ... get the garlic and the silver cross "Ron de Bruin" wrote in message ... 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 |
emailing a whole spreadsheet
Dear Bob and Ron, many thanks for your replies, Love 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 |
emailing a whole spreadsheet
Is your problem solved Amy?
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "AmyTaylor" wrote in message ... Dear Bob and Ron, many thanks for your replies, Love 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 |
| All times are GMT +1. The time now is 12:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com