Turning Spreadsheet into attachement in an Email

[twogoodtwo]

Can someone possibly help. I have written the following macro to send an
excel spreadsheet as an attachment. It all seems to work well except,
for some reason, it does not put the text "Please find attached" that I
want in the body of the email. It only adds the attachment and the
subject title.

Can someone tell me where I am going wrong?



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 = "(e-mail address removed)"
..BCC = ""
..Subject = "Text"
Dim strbody As String
strbody = "Please find attached" & vbNewLine & vbNewLine & _
"Kind regards" & vbNewLine & _
"Me"
..Attachments.Add
("L:\somewhere\somewhere\somewhere\spreadsheet.xls")
..Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Bob Phillips]

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 = "(e-mail address removed)"
..BCC = ""
..Subject = "Text"
Dim strbody As String
strbody = "Please find attached" & vbNewLine & vbNewLine & _
"Kind regards" & vbNewLine & _
"Me"
..Body = strbody
..Attachments.Add ("L:\somewhere\somewhere\somewhere\spreadsheet.xls")
..Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Trevor Shuttleworth]

Try:

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 = "(e-mail address removed)"
.BCC = ""
.Subject = "Text"
.Body = "Please find attached" & _
vbNewLine & vbNewLine & _
"Kind regards" & _
vbNewLine & vbNewLine & _
"Me"
.Attachments.Add _
"L:\somewhere\somewhere\somewhere\spreadsheet.xls"
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Regards

Trevor

 

[John]

Hi,

How about something like the following: (you've assigned the variable but
haven't assigned it to the "Body" property)

Best regards

John

UNTESTED...

Sub Mail_workbook_Outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
Dim strbody As String
strbody = "Please find attached" & vbNewLine & vbNewLine & _
"Kind regards" & vbNewLine & _
"Me"
With OutMail
.To = "(e-mail address removed)"
.CC = "(e-mail address removed)"
.BCC = ""
.Subject = "Text"
.Body = strbody
.Attachments.Add ("L:\somewhere\somewhere\somewhere\spreadsheet.xls")
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub