Email workbook with message (macro) (2003)

Topic

New Reply

Currently I have a SendMail macro that sends the whole workbook to a number of people.

I’d like to alter it so that so that the outgoing email has the following message: “The total units for this week is (value in cell A120). Here’s the kicker though: The value each week will always move over by two columns; so one week the value is in A120, the next week C120, the next week E120 etc. Is this too much trouble? If so, I don’t mind if I could even get the macro just to open Outlook, include the attachment, include the recipient’s names and I will type in the message each week.

Thanks for helping with this one.

Deb

Reply | Quote

Replies

SendMail doesn’t offer additional options such as specifying the message text. You could use code like this to show the mail envelope with some items already filled in:

Dim strCell As String
Dim strRecipient As String
Dim strSubject As String

strCell = … ‘ for example “A120”
strRecipient = … ‘ for example “billg@microsoft.com”
strSubject = … ‘ for example “Weekly update”

With ActiveSheet.MailEnvelope
.Introduction = “The total units for this week is ” & Range(strCell)
.Item.To = strRecipient
.Item.Subject = strSubject
End With

‘ Show mail envelope
CommandBars.FindControl(ID:=3738).Execute

You’ll have to click the Send button manually.

If you use Outlook, an alternative would be to automate Outlook, i.e. control it in code from Excel.

Reply | Quote

Thank you Hans. That works out just perfectly!

Reply | Quote