emailing message body with vba

[PointerDog]

Hello,
I am using the following code from Ron DeBruin 'how to email site' and I would like to add a message to each email. Any help is appreciated. thanks much

this what I am using - but I do not know how to add message:
Sub Mail_sheets()
Dim MyArr As Variant
Dim last As Long
Dim shname As Long
Dim a As Integer
Dim Arr() As String
Dim N As Integer
Dim strdate As String
For a = 1 To 253 Step 3
If ThisWorkbook.Sheets("mail").Cells(1, a).Value = "" Then
Exit Sub
End If
Application.ScreenUpdating = False
last = ThisWorkbook.Sheets("mail").Cells(Rows.Count, _
a).End(xlUp).Row
N = 0
For shname = 1 To last
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = ThisWorkbook.Sheets("mail").Cells(shname, a).Value
Next shname
ThisWorkbook.Sheets(Arr).Copy

With ThisWorkbook.Sheets("mail")
MyArr = .Range(.Cells(1, a + 1), .Cells(Rows.Count, _
a + 1).End(xlUp))
End With

ActiveWorkbook.SendMail MyArr, ThisWorkbook.Sheets("mail").Cells(1, a + 2).Value

ActiveWorkbook.Close False
Application.ScreenUpdating = True
Next a
End Sub

 

[eblake]

You need to check the ActiveWorkbook.SendMail to see if it has a variable for a message, or you can code it into that function, look for the place its setting to, bcc... and look for a .Body , or .HTMLBody

 

[PointerDog]

I think that is my problem - there is no message variable, and I am not exactly sure how to code it in.

 

[eblake]

Can you post your Sendmail function, without seeing it I couldn't tell you where to add that in.

 

[PointerDog]

Sub SendMail(Recipients, [Subject], [ReturnReceipt])

 

[Michael M]

Unless there's been a change to the system
I'm afraid the short answer is no, you can't add messages in the SendMail method.

You could perhaps use a RoutingSlip but I think what I would
recommend would be automating your email client. eg Outlook

This may explain it a little clearer

http://www.ozgrid.com/VBA/send-email.htm

 

[PointerDog]

Thanks Michael and eblake, I will try some more tomorrow at work if time allows. btw and I should have said this in first post - I am using Excel 2010, but I don't think that makes any difference.

Again i greatly appreciate the help.

 

[PointerDog]

Anyone else have an idea on this one? I want to add a message to the above code which allows me to send separate sheets to different email addresses -- just would like to add the same message to the body of the emails.

 

[Michael M]

More ideas for what ?
You can't add a message body to a SendMail....unless you make the recipients blank and then add it manually, but that kinda defeats the purpose.
I think you'd be better off using the Outlook method rather than SendMail

 

[PointerDog]

Michael, thanks and I am still a novice with vba so bear with me. It sounds like the Outlook method is a possible way for me to achieve my goal of sending separate sheets to different email addresses - and adding the same message to each email body. I am not sure how to incorporate the Outlook method in my code so if you can direct me to a site or provide some coding advice I would forever be indebted to you. My coworkers are clueless on vba and this is quite a tedious weekly task for our group.

Kind regards and thanks.

using excel 2010, windows xp