Email VBA in Excel - Small Help

**twogoodtwo** · 07-26-2006, 09:19 AM

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 = "[email protected]"
.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

Ron de Bruin · 07-26-2006, 11:00 AM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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 = "[email protected]"
> 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
>

**twogoodtwo** · 07-26-2006, 11:25 AM

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 = "[email protected]"
.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

Ron de Bruin · 07-26-2006, 01:10 PM

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 = "[email protected]"
.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" <[email protected]> wrote in message
news:[email protected]...
>
> 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 = "[email protected]"
> 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
>

Ron de Bruin · 07-26-2006, 02:25 PM

Change .Display to .Send

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

"Ron de Bruin" <[email protected]> wrote in message news:%[email protected]...
> 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 = "[email protected]"
> .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" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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 = "[email protected]"
>> 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
>>
>
>

**twogoodtwo** · 07-27-2006, 06:00 AM

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?