How to insert Outlook Signature in Email by Excel VBA

.

In this Article you are going to learn how to insert Outlook Signature in outlook email while sending an email via Excel VBA. It means while sending an email from Outlook via Excel Macro, if you want already saved signature to be inserted at the end of your email, then here is the code to do so.
 
<< Return to Send Email Tutorial Page
 

 
Important:
 
As soon as you create a signature in Outlook it saves the signature in 3 different types of files: .HTM, TXT and RTF as shown below:

These files get stored at the following location in your system:
 
Windows XP :
C:\Documents and Settings\Vish\Application Data\Microsoft\Signatures
 

Windows 7 and 8 :
C:\Users\Vish\AppData\Roaming\Microsoft\Signatures

Note:
In the below code when we are creating an email in Outlook then at the end of the email, we will insert the Signature from the .txt file or .htm file.

Signature

Signature

 

In the below code we are first checking if these files exists and have some values in it. If yes then we are reading the text from the signature file and inserting that signature in the email.
Thus we need a function which read and return the Signature Texts from the file wherever we need to insert it in the body of the Outlook email.

Function to read the Signature file and return the Signature Text


Function GetSignature(fPath As String) As String
    Dim fso As Object
    Dim TSet As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set TSet = fso.GetFile(fPath).OpenAsTextStream(1, -2)
    GetSignature= TSet.readall
    TSet.Close
End Function

Below is the code to create the Outlook Email with the Signature at the end.
Here there could be two types of Email and Signatures:

  • Email and Signature with Simple Text
  • Email and Signature with the HTML


We will see the code of both the above methods below one by one:

Email and Signature with Simple Text

Sub With_Text_Signature()

    'Do not forget to change the email ID
    'before running this code

    Dim OlApp As Object
    Dim NewMail As Object
    Dim EmailBody As String
    Dim StrSignature As String
    Dim sPath As String

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

    EmailBody = "Type the Body of your email"

    '*****************************************************
    '                    Important
    '*****************************************************
    ' go to the appdata path as mentioned in
    ' the above important point. Check the name of
    ' the signature file. For example: here in my system
    ' the signature's file name is vish.txt, vish.htm
    ' Therefore before running this code, check the
    ' name fo the signature file in your system and
    ' replace vish.txt with your file name.
    '****************************************************

    sPath = Environ("appdata") & "\Microsoft\Signatures\vish.txt"

    ' If the path and file name given by you is not
    ' correct then code you insert a blank Signature
    
    If Dir(sPath) <> "" Then
        StrSignature = GetSignature(sPath)
    Else
        StrSignature = ""
    End If

    On Error Resume Next
    With NewMail
        .To = "info@learnexcelmacro.com"
        .CC = "info@learnexcelmacro.com"
        .BCC = "info@learnexcelmacro.com"
        .Subject = "Type your Subject here"
        ' Here at the end of the Email Body
        ' Text Signature is inserted.
        .Body = EmailBody & vbNewLine & vbNewLine & StrSignature
        .send
    End With
    On Error GoTo 0
    Set NeMail = Nothing
    Set OlApp = Nothing
End Sub

Email and Signature with HTML Body and HTML Signature

Sub With_HTML_Signature()

    'Do not forget to change the email ID
    'before running this code

    Dim OlApp As Object
    Dim NewMail As Object
    Dim EmailBody As String
    Dim StrSignature As String
    Dim sPath As String

    Set OlApp = CreateObject("Outlook.Application")
    Set NewMail = OlApp.CreateItem(0)
    
    ' Here Since we are talking about
    ' the HTML email then we need to
    ' write the Body of the Email in
    ' HTML.

    EmailBody = "Hello Friends !!" & "

Welcome to LearnExcelMacro.com" & vbNewLine & _ "Here i will make you awesome in Excel Macro.

You can mail me at info@learnexcelmacro.com" '***************************************************** ' Important '***************************************************** ' go to the appdata path as mentioned in ' the above important point. Check the name of ' the signature file. For example: here in my system ' the signature's file name is vish.txt, vish.htm ' Therefore before running this code, check the ' name of the signature file in your system and ' replace vish.txt with your file name. '**************************************************** sPath = Environ("appdata") & "\Microsoft\Signatures\vish.htm" ' If the path and file name given by you is not ' correct then code you insert a blank Signature If Dir(sPath) <> "" Then StrSignature = GetSignature(sPath) Else StrSignature = "" End If On Error Resume Next With NewMail .To = "info@learnexcelmacro.com" .CC = "info@learnexcelmacro.com" .BCC = "info@learnexcelmacro.com" .Subject = "Type your Subject here" ' Here at the end of the Email Body ' HTML Signature is inserted. .htmlBody = EmailBody & "

" & StrSignature .send End With On Error GoTo 0 Set NeMail = Nothing Set OlApp = Nothing End Sub

Important : Outlook Signature with an Image

If your signature contains some image, then above method will not display the image in signature. You will see that all the texts are displayed in signature but not the image.

To overcome this issue, there are two solutions.. refer to my new article

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

11 Comments

  1. Rizwan

    PLease send the exel macro from outlook with Signature

    Have tried but its not taking

    Reply
    • Chris

      you will need to create a separate function for GetSignature(SigString) eg. below

      Function Getsignature(ByVal sFile As String) As String

      Dim fso As Object

      Dim ts As Object

      Set fso = CreateObject("Scripting.FileSystemObject")

      Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)

      GetSignature = ts.readall

      ts.Close

      End Function

      Reply
      • Pankaj

        Awesome code..was looking for same for a while. Thanks a ton.

        My default signature has two images in it as well it showing as “image cannot be displayed” and a cross within it. Do we need to tweak this code to use the signature with its images also?

        Thanks a ton.

        Reply
  2. Eric Vanzo

    Vish, this works, but the font used in the message body does not match my default font, which is used in my signature (e.g. Thanks, Eric) so it looks odd.

    Is there any way to fix this? I have tried creating an email with only the signature, and then using SendKeys to paste my message into the body, but there must be a better way.

    Thanks,

    Eric

    Reply
  3. Devesh

    Hello,

    I am getting the same error, My default signature has image in it as well it showing as “image cannot be displayed” and a cross within it. Please advise

    Reply
  4. Adrian

    Hello … The code is great but when i open te new email ( replaced .send with .display ) the image in the signature isn’t showing 🙁

    Reply
  5. Glenn

    what if your using a citrix based system?

    Reply
  6. Wessel

    The code to add my signature does not want to display my signature, instead I get a “image cannot be displayed” message. I would be great full for some advice to solve this problem

    Reply
  7. Dev

    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)

    What is the meaning of this line?? in the above function??

    Reply
  8. Tim

    Thanks. Pity about the image.

    I think there’s a typo in your code on line 58.

    Set NeMail = Nothing

    should be

    Set NewMail = Nothing

    Reply
  9. Melissa

    I cannot get the html version to work. In your example you say put the emailBody in as HTML, but you don’t do that in your example. For some reason, I can get the signature OR the body to appear, but not both at the same time. 🙁

    Reply

Trackbacks/Pingbacks

  1. How to add my outlook signature to this code: - […] document.write(''); Try this link: Welcome to LearnExcelMacro.com How to insert Outlook Signature in Email by Excel VBA […]
  2. Signature Image Appearing as Red Cross in Email Sent by Excel VBA - […] Before we jump in to the solution, lets see what goes wrong when we try to display a signature…

Submit a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest