Email VBA in Excel - Small Help

  • Thread starter Thread starter twogoodtwo
  • Start date Start date
T

twogoodtwo

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 = "(e-mail address removed)"
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
 
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
 
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 = "(e-mail address removed)"
.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
 
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 = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = ""
.Attachments.Add ("C:\Documents and Settings\test.pdf")
.Body = strbody
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
 
Change .Display to .Send


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



Ron de Bruin said:
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 = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = ""
.Attachments.Add ("C:\Documents and Settings\test.pdf")
.Body = strbody
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Click to expand...
 
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?
 
Back
Top