Turning Spreadsheet into attachement in an Email

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

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


"twogoodtwo" <[email protected]> wrote in message
news:[email protected]...
>
> 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 = "[email protected]"
> CC = "[email protected]"
> 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
>
>
> --
> twogoodtwo
> ------------------------------------------------------------------------
> twogoodtwo's Profile:
http://www.excelforum.com/member.php...o&userid=12738
> View this thread: http://www.excelforum.com/showthread...hreadid=346026

>

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


"twogoodtwo" <[email protected]> wrote in message
news:[email protected]...
>
> 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 = "[email protected]"
> CC = "[email protected]"
> 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
>
>
> --
> twogoodtwo
> ------------------------------------------------------------------------
> twogoodtwo's Profile:
> http://www.excelforum.com/member.php...o&userid=12738
> View this thread: http://www.excelforum.com/showthread...hreadid=346026
>

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 = "[email protected]"
.CC = "[email protected]"
.BCC = ""
.Subject = "Text"
.Body = strbody
.Attachments.Add ("L:\somewhere\somewhere\somewhere\spreadsheet.xls")
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


"twogoodtwo" <[email protected]> wrote in message
news:[email protected]...
>
> 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 = "[email protected]"
> CC = "[email protected]"
> 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
>
>
> --
> twogoodtwo
> ------------------------------------------------------------------------
> twogoodtwo's Profile:
> http://www.excelforum.com/member.php...o&userid=12738
> View this thread: http://www.excelforum.com/showthread...hreadid=346026
>

Thanks all who replied - the first response worked so I have gone for that one.

Now one final question, Is the any way of adding to the macro in order for the body of the email to reference a cell in the actual spreadsheet.

Basically I would like it to read, "Please find attached. The answer is [cell G7]

Thanks again