Consulting

Results 1 to 3 of 3

Thread: Solved: Adding comment to excel / outlook script

  1. #1
    VBAX Regular
    Joined
    Sep 2004
    Posts
    32
    Location

    Solved: Adding comment to excel / outlook script

    Hello,


    I have a script that automatically attaches a spreadsheet to an email and sends it to an address list.. Some of the users of the spreadsheet have asked for the facilicty to add a comment to the email before it sends.. is this possible?

    the code is:

    Sub eMailActiveWorkbook()

    Dim OL As Object
    Dim EmailItem As Object
    Dim Wb As Workbook

    ActiveWorkbook.Save

    Application.ScreenUpdating = False
    Set OL = CreateObject("Outlook.Application")
    Set EmailItem = OL.CreateItem(olMailItem)
    Set Wb = ActiveWorkbook
    Wb.Save
    With EmailItem
    .Subject = "I Fowards Spreadsheet has been updated"
    .Body = "" & vbCrLf & _
    "" & vbCrLf & _
    ""
    .To = "email address list
    .Importance = olImportanceHigh 'Or olImprotanceHigh Or olImprotanceLow
    .Attachments.Add Wb.FullName
    .Send
    End With

    Application.ScreenUpdating = True

    Set Wb = Nothing
    Set OL = Nothing
    Set EmailItem = Nothing

    End Sub



    Many many thanks

    Bacon

  2. #2
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Hi Bacon,

    When you say Comment, do you mean some text in the body of the e-mail?

    If so, a simple solution would be to prompt for the text and then add it, something like ..

    Dim EMailComment
    EMailComment = InputBox("Please enter your comment for the E-mail")
    .Body = EMailComment

    Or do you want something with more flexibility?
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  3. #3
    VBAX Regular
    Joined
    Sep 2004
    Posts
    32
    Location
    Quote Originally Posted by TonyJollans
    Hi Bacon,

    When you say Comment, do you mean some text in the body of the e-mail?

    If so, a simple solution would be to prompt for the text and then add it, something like ..

    Dim EMailComment
    EMailComment = InputBox("Please enter your comment for the E-mail")
    .Body = EMailComment

    Or do you want something with more flexibility?
    you are a star.... thank you thank you...

Posting Permissions

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