Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Email workbook with message (macro) (2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Email workbook with message (macro) (2003)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email workbook with message (macro) (2003)

    Thank you Hans. That works out just perfectly!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •