Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email VBA in Excel - Small Help
Just wondering if somone could help me with some VBA. I have written a macro so when I press a button, it creates an email with the email addresses, relevant attachment and text in the body of the email. However, I also want to add data from one tab called 'stats' (A5:A8 to be precise) below my first line of the body of the message. Can anybody help. My exisiting code is below: Sub Mail_workbook_Outlook() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail To = " CC = "" BCC = "" Subject = "Hello" Dim strbody As String strbody = "Please find attached today's spreadsheet and statistics below." & vbNewLine & vbNewLine & _ "Kind regards" & vbNewLine & _ "Me" Attachments.Add ("C:\desktop\name.pdf") Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- twogoodtwo ------------------------------------------------------------------------ twogoodtwo's Profile: http://www.excelforum.com/member.php...o&userid=12738 View this thread: http://www.excelforum.com/showthread...hreadid=565129 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email VBA in Excel - Small Help
you can do this
Dim strbody As String Dim cell As Range strbody = "Please find attached today's spreadsheet and statistics below." & _ vbNewLine & vbNewLine & "Kind regards" & vbNewLine & "Me" & vbNewLine & vbNewLine For Each cell In ThisWorkbook.Sheets("stats").Range("A5:A8") strbody = strbody & cell.Value & vbNewLine Next -- Regards Ron de Bruin http://www.rondebruin.nl "twogoodtwo" wrote in message ... Just wondering if somone could help me with some VBA. I have written a macro so when I press a button, it creates an email with the email addresses, relevant attachment and text in the body of the email. However, I also want to add data from one tab called 'stats' (A5:A8 to be precise) below my first line of the body of the message. Can anybody help. My exisiting code is below: Sub Mail_workbook_Outlook() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail To = " CC = "" BCC = "" Subject = "Hello" Dim strbody As String strbody = "Please find attached today's spreadsheet and statistics below." & vbNewLine & vbNewLine & _ "Kind regards" & vbNewLine & _ "Me" Attachments.Add ("C:\desktop\name.pdf") Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- twogoodtwo ------------------------------------------------------------------------ twogoodtwo's Profile: http://www.excelforum.com/member.php...o&userid=12738 View this thread: http://www.excelforum.com/showthread...hreadid=565129 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email VBA in Excel - Small Help
Thanks Ron, Therefore, in trying to piece these two bits together, how would th whole macro look as when try to and put the two together, it does no work... atttempt below: Sub Mail_workbook_Outlook() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = " .CC = "" .BCC = "" .Subject = "" Dim strbody As String Dim cell As Range strbody = "Please find attached today's spreadsheet." vbNewLine & vbNewLine & _ "Kind regards" & vbNewLine & _ "Robin" ThisWorkbook.Sheets("stats").Range ("A5:A8") strbody = strbody & cell.Value & vbNewLine Next .Attachments.Add ("C:\Documents and Settings\test.pdf") .Display End With Set OutMail = Nothing Set OutApp = Nothing End Su -- twogoodtw ----------------------------------------------------------------------- twogoodtwo's Profile: http://www.excelforum.com/member.php...fo&userid=1273 View this thread: http://www.excelforum.com/showthread.php?threadid=56512 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email VBA in Excel - Small Help
Use this
Sub Mail_workbook_Outlook() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Dim strbody As String Dim cell As Range Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) strbody = "Please find attached today's spreadsheet." & vbNewLine & vbNewLine & _ "Kind regards" & vbNewLine & _ "Robin" & vbNewLine & vbNewLine For Each cell In ThisWorkbook.Sheets("stats").Range("A5:A8") strbody = strbody & cell.Value & vbNewLine Next With OutMail .To = " .CC = "" .BCC = "" .Subject = "" .Attachments.Add ("C:\Documents and Settings\test.pdf") .Body = strbody .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "twogoodtwo" wrote in message ... Thanks Ron, Therefore, in trying to piece these two bits together, how would the whole macro look as when try to and put the two together, it does not work... atttempt below: Sub Mail_workbook_Outlook() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail To = " CC = "" BCC = "" Subject = "" Dim strbody As String Dim cell As Range strbody = "Please find attached today's spreadsheet." & vbNewLine & vbNewLine & _ "Kind regards" & vbNewLine & _ "Robin" ThisWorkbook.Sheets("stats").Range ("A5:A8") strbody = strbody & cell.Value & vbNewLine Next Attachments.Add ("C:\Documents and Settings\test.pdf") Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- twogoodtwo ------------------------------------------------------------------------ twogoodtwo's Profile: http://www.excelforum.com/member.php...o&userid=12738 View this thread: http://www.excelforum.com/showthread...hreadid=565129 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email VBA in Excel - Small Help
Change .Display to .Send
-- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Use this Sub Mail_workbook_Outlook() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Dim strbody As String Dim cell As Range Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) strbody = "Please find attached today's spreadsheet." & vbNewLine & vbNewLine & _ "Kind regards" & vbNewLine & _ "Robin" & vbNewLine & vbNewLine For Each cell In ThisWorkbook.Sheets("stats").Range("A5:A8") strbody = strbody & cell.Value & vbNewLine Next With OutMail .To = " .CC = "" .BCC = "" .Subject = "" .Attachments.Add ("C:\Documents and Settings\test.pdf") .Body = strbody .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "twogoodtwo" wrote in message ... Thanks Ron, Therefore, in trying to piece these two bits together, how would the whole macro look as when try to and put the two together, it does not work... atttempt below: Sub Mail_workbook_Outlook() Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail To = " CC = "" BCC = "" Subject = "" Dim strbody As String Dim cell As Range strbody = "Please find attached today's spreadsheet." & vbNewLine & vbNewLine & _ "Kind regards" & vbNewLine & _ "Robin" ThisWorkbook.Sheets("stats").Range ("A5:A8") strbody = strbody & cell.Value & vbNewLine Next Attachments.Add ("C:\Documents and Settings\test.pdf") Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- twogoodtwo ------------------------------------------------------------------------ twogoodtwo's Profile: http://www.excelforum.com/member.php...o&userid=12738 View this thread: http://www.excelforum.com/showthread...hreadid=565129 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email VBA in Excel - Small Help
Thanks Ron - much appreciated for all your help - it works a treat. Now I am going to see how I can add another feature of PDF'ing the excel spreadsheet to the same file name using Excel 2003 and Adobe Professional 7 using a macro - that should definately be possible? -- twogoodtwo ------------------------------------------------------------------------ twogoodtwo's Profile: http://www.excelforum.com/member.php...o&userid=12738 View this thread: http://www.excelforum.com/showthread...hreadid=565129 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISERROR,SMALL,INDEX, MATCH, SMALL?? | Excel Discussion (Misc queries) | |||
Email addresses in Excel need to format for mass email | Excel Worksheet Functions | |||
Email editor closes when forwarding Excel-embedded email | Setting up and Configuration of Excel | |||
working on excel document in email saved changes in email not in . | Excel Discussion (Misc queries) | |||
body of email disappears when I send an email from Excel | Excel Discussion (Misc queries) |