Add signature to your automated emails

Tired of issues connected with email automation in Outlook application? Here is another one! In todays article we are going to get rid of it and learn how to add signature to your automated emails.

If You’ve ever tried to automate your correspondence in Outlook, You probably already did fight with that kind of problem. As far as I remember it was my main, maybe not the only one but main, issue – adding the signature in the end of newly created email using VBA. Now, knowing a few approaches to this topic, I decided to share my knowledge and describe in this article how to add signature to automated emails.

How to add signature in email using VBA?

For todays artcile I prepared 3 ways to do this. Sorted from the easiest to the most complex, but clear, approach.

1. Paste signature from prepared Word doc

This is the easiest way to get this done. In previous article I described how to paste the content of Word document easily, so having signature prepared already in doc file would be very convenient.

The same effect You could get prepared signature inside in code.

2. Create signature from email body

The second option is to get default signature from the email body and put that after the email content. Unfortunately this method has 2 downgrades.

The first disadvantage is that You need to remember to first Display the email and the second is that the final result is probably not that we expect to have.

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

OutMail.Display
Let Signature = OutMail.Body

With OutMail
    .Body = "Email content" & Signature
End With

3. Get signature from Outlook folder

Finally, the most complex approach is to get the signature from the .htm file, which is located in Outlook directory.

Firstly it finds the directory to the users Signatures folder, then looking for .htm file and as the last step is getting the content of this file into string variable.

Dim S As String
 S = Environ("appdata") & "\Microsoft\Signatures\"
 If Dir(S, vbDirectory) <> vbNullString Then S = S & Dir$(S & "*.htm") Else S = ""
 S = CreateObject("Scripting.FileSystemObject").GetFile(S).OpenAsTextStream(1, -2).ReadAll

The string variable is getting the content of the .htm file – singature with html code. It means that thanks to the method You keep whole formatting of your signature, in contrast to 2nd approach above.

Not the only solution

In this article I showed You 3 different solutions of how to add signature to automated emails using VBA. I hope it will help You in your macros. Choose the best solution for your needs and requirements. Sometimes even the simpliest is enough.

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

19 thoughts on “Add signature to your automated emails”

  1. Your third option worked like a charm. Been looking for this solution for a long time.

    Thank you!

  2. Option 3 worked for me, however, the signature had pictures that would not show up. I had to edit the Signature HTML file with the full file path opposed to the relative path from the location of the HTM file.

    1. Hi, may you explain to me how you did it in a few steps?
      I’m new to vba and it just won’t display my images in the final mail.

  3. Hi! I have code block for my e mails, Where should I add the third solution on my code block ?

    Sub send_all_mails()

    Dim EApp As Object
    Set EApp = CreateObject(“Outlook.Application”)
    Dim EItem As Object
    On Error Resume Next

    Dim RList As Range
    Set RList = Range(“A2”, Range(“a4”).End(xlDown))

    Dim R As Range
    For Each R In RList

    Set EItem = EApp.CreateItem(0)
    With EItem
    .To = R.Offset(0, 15)
    .CC = “”
    .BCC = “”
    .Subject = “RESTRICTED TRAVEL POLICY”
    .body = R.Offset(0, 29)
    .display

    End With
    Next R

    Set EItem = Nothing
    Set EApp = Nothing

    End Sub

  4. Just wanna say you blew my mind with option 3 – I am currently fixing an issue where I can’t get the pictures to show.
    We’ve been writing emails without nice signatures via VBA for the longest time. This will save us a lot of time.
    Legend among us peasants

  5. Hi Tomasz! I have tried adding the third option but when I execute it Separately It’s working fine, but when I tried it in the Code It is disappearing. Could you please help me where to add or modify the Code

    Sub VIL_3_Monthly()

    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim OutAccount As Outlook.Account
    Dim strbody As String

    If Range(“C2”).Value = “Active” Then

    Set OutApp = CreateObject(“Outlook.Application”)
    Set OutMail = OutApp.CreateItem(olMailItem)

    Set OutAccount = OutApp.Session.Accounts.Item(2)

    myfldr = “Z:\Documentation\External\Customer Relations\Bosch\Reports\”

    myfile = Dir(myfldr)

    strbody = “Please find the attached monthly metrics reports for your VIL solution.” & vbNewLine & vbNewLine & “Should you have trouble with any of the files, or if you have any questions or concerns, please do not hesitate to contact us.” & vbNewLine & vbNewLine & “Thank you for choosing MOTOR for your automotive service information solution.” & vbNewLine & vbNewLine & Signature & vbNewLine & vbNewLine & ” ”
    On Error Resume Next
    With OutMail
    .To = Range(“B2”).Value
    .CC = “”
    .BCC = Range(“F3”).Value
    .Subject = “MOTOR Delivery: VIL Monthly Metrics Report ” & Format$(DateAdd(“m”, -1, Now()), “mmmm -yyyy “) & Range(“A2”).Value
    .Body = strbody
    .SendUsingAccount = OutAccount
    .Display

    Do While myfile “”

    .Attachments.Add myfldr & myfile

    myfile = Dir

    Loop

    .Display

    End With
    On Error GoTo 0
    End If

    If Range(“C3”).Value = “Active” Then

    Set OutApp = CreateObject(“Outlook.Application”)
    Set OutMail = OutApp.CreateItem(olMailItem)

    Set OutAccount = OutApp.Session.Accounts.Item(1)

    myfldr = “Z:\Documentation\External\Customer Relations\EBSCO\Reports\”

    myfile = Dir(myfldr)

    strbody = “Please find the attached monthly metrics reports for your VIL solution.” & vbNewLine & vbNewLine & “Should you have trouble with any of the files, or if you have any questions or concerns, please do not hesitate to contact us.” & vbNewLine & vbNewLine & “Thank you for choosing MOTOR for your automotive service information solution.” & vbNewLine & vbNewLine & Signature
    On Error Resume Next
    With OutMail
    .To = Range(“B3”).Value
    .CC = “”
    .BCC = Range(“F3”).Value
    .Subject = “MOTOR Delivery: VIL Monthly Metrics Report ” & Format$(DateAdd(“m”, -1, Now()), “mmmm -yyyy “) & Range(“A3”).Value
    .Body = strbody
    .SendUsingAccount = OutAccount
    .Display ‘or use .Send

    Do While myfile “”

    .Attachments.Add myfldr & myfile

    myfile = Dir

    Loop

    .Display

    End With
    On Error GoTo 0
    End If

    Set OutMail = Nothing
    Set OutApp = Nothing
    Set OutAccount = Nothing
    End Sub

    1. Hello
      I can’t really see where You declare the Signature, so from my point of view sure it should not work 🙂

  6. Hi Tomasz,

    I tried to use the option 3, with the run-time error no 5. Is there something wrong with my code?

    Sub SendEmail()
    Dim objOutlook As Object
    Dim objMail As Object
    Dim subject As String
    Dim recipient As String
    Dim ccRecipient As String
    Dim body As String
    Dim S As String

    Set objOutlook = CreateObject(“Outlook.Application”)
    Set objMail = objOutlook.CreateItem(0)

    subject = “Request for Latest Quotation for ” & Cells(ActiveCell.Row, 1).Value & ” to ” & Cells(ActiveCell.Row, 4).Value
    recipient = Cells(ActiveCell.Row, 6).Value
    ccRecipient = Cells(ActiveCell.Row, 5).Value
    body = “Dear ” & ccRecipient & “, ” & vbNewLine & vbNewLine & “We hope this email finds you well. We are writing to request the latest quote or estimate price for shipping to ” & Cells(ActiveCell.Row, 4).Value & ” , ” & Cells(ActiveCell.Row, 3).Value & “. Our company is planning to expand our operations to the ” & Cells(ActiveCell.Row, 3).Value & ” market, and we are in the process of evaluating different freight forwarder options. ” & vbNewLine & vbNewLine & “We would like to request a quotation that includes the cost of shipping, as well as any additional fees such as customs clearance, handling and storage, and insurance. If possible, please also include an estimated transit time for the shipment. ” & vbNewLine & vbNewLine & “Please let us know if there are any other information or documents that we need to provide for the quotation process. ” & vbNewLine & vbNewLine & “We look forward to hearing from you soon.”
    S = Environ(“appdata”) & “\Roaming\Microsoft\Signatures\”
    If Dir(S, vbDirectory) vbNullString Then S = S & Dir$(S & “Send PO.htm”) Else S = “”
    S = CreateObject(“Scripting.FileSystemObject”).GetFile(S).OpenAsTextStream(1, -2).ReadAll
    With objMail
    .To = recipient
    .CC = ccRecipient
    .subject = subject
    .body = body & S
    .Send
    End With
    Set objMail = Nothing
    Set objOutlook = Nothing
    End Sub

    1. Hello
      First of all please specify worksheet You refer calling the values from cells, e.g. instead of Cells(1,2).Value –> ThisWorkbook.Sheet(1).Cells(1,2).Value
      Secondly, please don’t use ActiveCell/ActiveSheet or any kind of Active prefixed if not necessary, be specified as possible -> it can and is the cause of many problems.
      After cleaning the code, if it still give the error, try to Display email before pasting variable S into the email body, paste that and then Send.
      Give here a sign how that goes 🙂

  7. Any update on how to get signature with images working? I have the same issue.

  8. I found a way to make the images work in option 3, by adding the path to ‘src=’:

    Sub SendEmails()
    Dim OutlookObj As Object
    Dim MailObj As Object
    Dim Path As String, FName As String, Signature As String
    Path = Environ(“appdata”) & “\Microsoft\Signatures\”
    If Dir(Path, vbDirectory) vbNullString Then
    FName = Path & Dir$(Path & “*.htm”)
    Else
    FName = “”
    End If
    Signature = CreateObject(“Scripting.FileSystemObject”).GetFile(FName).OpenAsTextStream(1, -2).ReadAll
    Signature = Replace(Signature, “src=”””, “src=””” & Path)
    Set OutlookObj = CreateObject(“Outlook.Application”)
    Set MailObj = OutlookObj.CreateItem(0)
    With MailObj
    .Display
    .To = ThisWorkbook.Sheets(1).Range(“A1”).Value2
    .Subject = “Test subject”
    .HTMLBody = “Test content” & Signature
    End With
    End Sub

  9. Hi Tomasz.
    I’m thinking from your responses that Option 3 does not work with images so will be going with option 1 as you have suggested to others. I took it that if I had an image in the word document that this should work? However I see to only get the text to come into my email.

    Should I expect that images can work with this method?

    Thanks
    Mike

Leave a Reply

Your email address will not be published. Required fields are marked *