Results 1 to 2 of 2

Thread: Macro for Outlook Email Signatures

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro for Outlook Email Signatures

    Hello Experts!

    I am using the following to successfully create an email and attach a pdf to it. All works great, except the email signature does not show.

    I believe the signature is being replaced with the body text.
    outlookmailitem.body="Hello,".....

    I am very new to macros (less than 24 hours). Please be real specific and don't assume I know anything. Thanks,

    Sub CreateEmail()
    Dim edress As String
    Dim cc As String
    Dim subj As String
    Dim message As String
    Dim filename As String
    Dim outlookapp As Object
    Dim outlookmailitem As Object
    Dim myAttachments As Object
    Dim path As String
    Dim attachment As String


    Set outlookapp = CreateObject("Outlook.Application")
    Set outlookmailitem = outlookapp.createitem(0)
    Set myAttachments = outlookmailitem.Attachments
    path = "C:\Users\llundstrom\Desktop\Quotes\"


    edress = ActiveSheet.Range("c46")
    cc = Sheets("Formulas & Macros").Range("a27")
    subj = ActiveSheet.Range("c4")
    filename = ActiveSheet.Range("c48")
    attachment = path + filename


    outlookmailitem.To = edress
    outlookmailitem.cc = cc
    outlookmailitem.bcc = ""
    outlookmailitem.Subject = subj
    outlookmailitem.body = "Hello," & vbCrLf & "Please find your quote attached." & vbCrLf & "Thanks,"


    myAttachments.Add (attachment)
    outlookmailitem.display


    End Sub

  2. #2
    Board Regular
    Join Date
    Mar 2016
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Macro for Outlook Email Signatures

    Hello! Welcome to the forum and to programming with VBA. This is an amazing effort in such a short amount of time.

    Prior to displaying the email, there is no signature. So we can display the email and then change the body. However, that also leads to the 2 types of Body that can be changed: Body (as you have it) and HTMLBody.

    Body is useful to add simple text, but formatting the text is not possible. If you use Body, you can put your text in front of the signature, but any formatting you had in the signature will disappear, and it will show up in plain text. If that is okay, then Body will work fine.

    HTMLBody is where you want to go to include text formatting, though it can be tricky to get used to the way that HTML codes work. In the code below, I used HTMLBody so that any formatting in the signature will be preserved. Note the change in your body text where, instead of including vbCrLf, the text includes < br>, which does the same/similar thing in HTML-speak.

    NOTE: in order to get the < br> to show up as text in this reply, a space was needed between the "<" and "b". Funny thing, not having the space works as it should in this reply which is HTML-based and creates the new paragraph instead of showing the text. In the code below, after you paste it into Excel, remove the space.

    Code:
    Sub CreateEmail()
    Dim edress As String
    Dim cc As String
    Dim subj As String
    Dim message As String
    Dim filename As String
    Dim outlookapp As Object
    Dim outlookmailitem As Object
    Dim myAttachments As Object
    Dim path As String
    Dim attachment As String
    
    
    Set outlookapp = CreateObject("Outlook.Application")
    Set outlookmailitem = outlookapp.createitem(0)
    Set myAttachments = outlookmailitem.Attachments
    path = "C:\Users\llundstrom\Desktop\Quotes\"
    
    
    edress = ActiveSheet.Range("c46")
    cc = Sheets("Formulas & Macros").Range("a27")
    subj = ActiveSheet.Range("c4")
    filename = ActiveSheet.Range("c48")
    attachment = path + filename
    
    outlookmailitem.To = edress
    outlookmailitem.cc = cc
    outlookmailitem.bcc = ""
    outlookmailitem.Subject = subj
    
    myAttachments.Add (attachment)
    outlookmailitem.display
    outlookmailitem.HTMLBody = "Hello,< br>Please find your quote attached.< br>Thanks," & outlookmailitem.HTMLBody
    
    End Sub
    Last edited by shknbk2; Sep 19th, 2019 at 12:57 PM. Reason: lost the code somehow
    Hobby: VBA and some .Net
    Career: the world of patent and trademark protection

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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