Image in Signature not Displayed – Mail Sent by VBA



Email | Excel Tips

Dear Friends,

I was keep getting this question from many of you that whenever your outlook signature has an Image then rest of the texts are displayed correctly but Image was never displayed. It was always appearing with a red cross sign (missing image) image. In order to get rid of this problem there are two solutions.

outlook Signature with image

Method 1 : A Trick while displaying the HTML signature (Applicable for any Signature)

Before we jump in to the solution, lets see what goes wrong when we try to display a signature with Image via VBA code. As I had explained in my previous post, How to add signatures from outlook before sending an email via Excel VBA?

Outlook stores all the Signatures in AppData folder in windows in different format like Text, HTML etc. Therefore to display an HTML Signature, first we find the HTML file, read the whole HTML file and append it at the end of the mail body. This is exactly where the problem was when a HTML signature had an Image.
All the texts are part of HTML but as you know for image, HTML has a tag where you provide the path where image is located. All the images are stored in a separate within the Signature folder as you can see in the below picture.

Note: In HTML, if the files are within the same folder where your HTML file is then you do not need to provide the complete path, rather you just give the folder name and file name. HTML by default look for that folder and image within the same directory. This is how outlook reads it and displays the images in mails.
But when we read it from HTML via VBA then this image URL becomes unknown for outlook to display the image in Signature. I hope you understood the problem.

Now you know the problem, then solution is simple :

Solution

Before we use the HTML code for signature at the end of Mail body, we need to change all the path (incomplete URLs) with complete URLs.

Read my comments in the code where I have made the changes.

Method 1: Any Outlook Signature with Image


Sub With_HTML_Signature_With_Image()

    '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
    Dim signImageFolderName As String
    Dim completeFolderPath 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\Default.htm"
    ' Files folder name is always same name as html file name appended by
    ' "_files"
    signImageFolderName = "Default_files"
    ' in outlook HTML forward slashes are used for
    ' the file path
    completeFolderPath = Environ("appdata") & "\Microsoft\Signatures\" & signImageFolderName

    ' If the path and file name given by you is not
    ' correct then code you insert a blank Signature
    
    If Dir(sPath) &lt;&gt; "" Then
        StrSignature = GetSignature(sPath)
        ' Now replace this incomplete file path
        ' with complete path wherever it is used
        StrSignature = VBA.Replace(StrSignature, signImageFolderName, completeFolderPath)
    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
        .display
        .send
    End With
    On Error GoTo 0
    Set NeMail = Nothing
    Set OlApp = Nothing
End Sub

Important : Do not Forget

Do not forget to copy paste the below function in your module
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

Method 2 : Simple and Easy Trick (Only for Default Signature)

All you need to do is, in your VBA code, before you assign a Body of your email, display it once on the screen. As soon as VBA displays the mail on screen, outlook, by default, adds its default signature at the end of the body of your email.

Every time before sending the email, if you display your mail, you can see this method will be slow. Therefore it is not good for sending bulk emails in a loop. By the way this method, can be used for all type of signatures (Text, HTML, image etc.). But important to note than this is applicable for Default Signature in outlook only

Important .Display statement should be used anywhere but before the Email Body statement as explained above. If you display your email after putting the email body, then outlook will not append the default signature at the end of your email body.


Sub Outlook_Default_Signature_With_Image()

    '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"

    On Error Resume Next
    With NewMail
        .To = "info@learnexcelmacro.com"
        .CC = "info@learnexcelmacro.com"
        .BCC = "info@learnexcelmacro.com"
        .Subject = "Type your Subject here"
        ' Important! Before writing the body of your email
        ' you should display the mail
        .display
        ' Here at the end of the Email Body
        ' HTML Signature is inserted.
        .htmlBody = EmailBody
        .send
    End With
    On Error GoTo 0
    Set NeMail = Nothing
    Set OlApp = Nothing
End Sub

Buy a coffee for the author

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.

Hurray..Lets Go

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

Feb 9, 2024

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

Feb 3, 2024

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

Feb 3, 2024

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 (=).

Know more

How to Declare a Public Variable in VBA

Feb 3, 2024

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the...

How to Get Excel version using VBA Code

Feb 2, 2024

Dear Friends, Usually while working on any of the VBA projects, it becomes important for me to first check the version...

Calculate MOD of Large Numbers in Excel

Feb 2, 2024

Excel Trick to Calculate MOD of Big Numbers. This article is all about MOD function in Excel. Every aspect of the MOD...

Bruce on January 13, 2017 at 6:56 pm

I am using the code you provided in Office 2016. I use to have the image hidden and attached then placed at the end of the email when I was using office 2010. The update just happened and now the image will not display when it is attached to the email and it also gives me the image can not be displayed issue with your code. Any thoughts on a non default signature with an image? Thank you!
Reply
- Bruce on February 20, 2017 at 6:21 pm
  
  Just wanted to follow up and see if anyone has been able to figure this 2016 issue out?
  Reply
- Vishwamitra Mishra on July 18, 2017 at 4:38 am
  
  Dear Bruce,
  
  Sorry for the late response.. are you still facing this issue?
  Reply

Nelchael on July 17, 2017 at 2:22 pm

hi, i need you help. i cannot display pictures inside my signature, this is my code:

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Dim objMail As Outlook.MailItem
    Dim objRecipients As Outlook.Recipients
    Dim objRecipient As Outlook.Recipient
    Dim strRecipientAddress As String
    Dim strSignatureFile As String
    Dim objFileSystem As Object
    Dim objTextStream As Object
    Dim strSignature As String
 
    If TypeOf Item Is MailItem Then
       Set objMail = Item
       Set objRecipients = objMail.Recipients
    End If
 
    'Select different signature files based on recipients
    'You can change the conditions as per you actual needs
    If objRecipients.Count = 1 Then
       Set objRecipient = objRecipients.Item(1)
       strRecipientAddress = objRecipient.Address
        If InStr(1, strRecipientAddress, "email user 1 ") &gt; 0 Or InStr(1, LCase(objRecipient.Name), "name surname") &gt; 0 Then
            strSignatureFile = CStr(Environ("USERPROFILE")) &amp; "\AppData\Roaming\Microsoft\Signatures\test4.htm"
        ElseIf InStr(1, strRecipientAddress, "email user 2") &gt; 0 Or InStr(1, LCase(objRecipient.Name), "name surname") &gt; 0 Then
            strSignatureFile = CStr(Environ("USERPROFILE")) &amp; "\AppData\Roaming\Microsoft\Signatures\test3.htm"
        Else
        strSignatureFile = CStr(Environ("USERPROFILE")) &amp; "\AppData\Roaming\Microsoft\Signatures\DefaultSign.htm"
       End If
    Else
       strSignatureFile = CStr(Environ("USERPROFILE")) &amp; "\AppData\Roaming\Microsoft\Signatures\DefaultSign.htm"
    End If
 
    'Read the specific signature file
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    Set objTextStream = objFileSystem.OpenTextFile(strSignatureFile)
    strSignature = objTextStream.ReadAll
 
    'Insert the signature to this email
    objMail.HTMLBody = objMail.HTMLBody &amp; "" &amp; strSignature &amp; ""
End Sub

but i can’t send pictures!!

can you help me?

Vishwamitra Mishra on July 18, 2017 at 4:17 am

Hi,
This article was mainly for this issue. If you look at the code which I have in the article, after reading the Signature file, it is important to replace the incomplete image file path with complete file path. Due to this incomplete path of the image, Outlook is not able to display any images in the Signature.

Append the below piece of code in your main code, it should work. Let me know if this helps.


'Find the htm name and the folder name where images are stored
 Dim signImageFolderName as String
SignatureFileName = VBA.Mid(strSignatureFile, VBA.InStrRev(strSignatureFile, "\", , vbTextCompare) + 1)
signImageFolderName = VBA.Left(SignatureFileName , VBA.InStrRev(SignatureFileName, ".", , vbTextCompare) - 1) & "_files"
completeFolderPath = Environ("appdata") & "\Microsoft\Signatures\" & signImageFolderName

‘Read the specific signature file
Set objFileSystem = CreateObject(“Scripting.FileSystemObject”)
Set objTextStream = objFileSystem.OpenTextFile(strSignatureFile)
strSignature = objTextStream.ReadAll
StrSignature = VBA.Replace(StrSignature, signImageFolderName, completeFolderPath)

‘Insert the signature to this email
objMail.HTMLBody = objMail.HTMLBody & “” & strSignature & “”
End Sub

Łukasz on August 4, 2017 at 10:20 am

Hi second code is not working:

i have tested it and when you are pasting htmlbody – whole email text is deleted and replaced by hmtlbody text…
Reply
jas on October 23, 2017 at 12:23 am

This worked a treat for me with MS Office 2013. Thank you very much.
Reply
- Vishwamitra Mishra on October 23, 2017 at 10:06 am
  
  I am glad Jas that it helped you 🙂
  Reply
Feipe on November 16, 2017 at 2:24 pm

it works excelent, thanks from Chile!!
Reply

faris on November 28, 2017 at 6:15 am

Hello,

i need ur help, because my signature can’t display in my email.

this is my code vba

Sub Send_Files()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim cell As Range
    Dim FileCell As Range
    Dim rng As Range
    Dim StrSignature As String
    Dim sPath As String

    StrSignature = GetSignature(Environ("User-pc") & "\AppData\Roaming\Microsoft\Signatures\Faris.htm")
    


    
    Set sh = Sheets("Kirim")
    Set OutApp = CreateObject("Outlook.Application")
    
    
    filePath = Range("K2").Value
    
    On Error Resume Next
        For Each cell In sh.Columns("C").Cells.SpecialCells(xlCellTypeConstants)
        Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
        If cell.Value Like "?*@?*.?*" And _
           Application.WorksheetFunction.CountA(rng) > 0 Then
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = cell.Value
                .cc = "" & Cells(cell.Row, 1).Range("g1:g1")
                .Subject = "" & Cells(cell.Row, 1).Range("h1:h1")
                .htmlbody = "" & cell.Offset(0, -1).Value & vbNewLine & "" & vbNewLine & "" & _
                 StrSignature

                
                For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                    If Trim(FileCell)  "" Then
                        If Dir(FileCell.Value)  "" Then
                            .Attachments.Add FileCell.Value
                            
                        End If
                    End If
                Next FileCell
            .display
            
            End With
            Set OutMail = Nothing
        End If
    Next cell
    Set OutApp = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Function GetSignature(fPath As String) As String
    Dim fso As Object
    Dim TSet As Object
    fPath = "C:\Users\User-pc\AppData\Roaming\Microsoft\Signatures\Faris.htm"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set TSet = fso.GetFile(fPath).OpenAsTextStream(1, -2)
    GetSignature = TSet.readall
    TSet.Close
End Function

Geo on December 14, 2017 at 9:59 am

Excellent, Thanks
Reply
chris terrell on March 19, 2019 at 6:25 pm

Great article. I had an issue with a space in the Signature file name and notices the HTML needed the space to be converted to %20
Reply

Trevor on August 6, 2019 at 10:51 pm

I am having an issue where the images are now displaying when I send the email to myself but when I send the email to a third party the images are not displaying. Any ideas on what might be causing this?

Function GetSignature(Spath As String) As String
  On Error GoTo eh
    Dim fso As Object
    Dim TSet As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set TSet = fso.GetFile(Spath).OpenAsTextStream(1, -2)
    GetSignature = TSet.readall
    TSet.Close
Exit Function
eh:
Logger "Error", Err.Source, Err.Description
End Function

Public Sub SubmissionToProcessing()
On Error GoTo eh
'Checking Required fields for this step
If Sheets("Conditions").Range("J4").Value = "" Or Sheets("Conditions").Range("J4").Value = " " Or Sheets("Conditions").Range("J3").Value = " " _
Or Sheets("Conditions").Range("J3").Value = "" Or Sheets("Conditions").Range("J2").Value = " " Or Sheets("Conditions").Range("J2").Value = "" Then

MsgBox "Please make sure that the Application #, Loan Officer:, and Branch: fields are complete.", vbExclamation, "UW Condition Sheet"
Exit Sub
End If

Dim objOutlook As Outlook.Application
    Dim objMail As Outlook.MailItem
    Dim lCounter As Long
    'Set objects
    Set objOutlook = Outlook.Application
    'Read details from Excel sheet and send emails
    
    'To
    Dim TO1 As String
    TO1 = Sheets("Automation Data").Range("D5").Value
    
    'CC
    Dim CC1 As String
    CC1 = Sheets("Automation Data").Range("D2").Value
    
    'Application #
    Dim AN1 As String
    AN1 = Sheets("Conditions").Range("J2").Value
    Dim Spath As String
    Dim StrSignature As String
    
   
        
        Spath = Environ("appdata") & "\Microsoft\Signatures\signature.htm"
        signImageFolderName = "Signature_files"
        completeFolderPath = Environ("appdata") & "\Microsoft\Signatures\" & signImageFolderName
        StrSignature = GetSignature(Spath)
        StrSignature = VBA.Replace(StrSignature, signImageFolderName, completeFolderPath)
    
        'Create a new email item
        Set objMail = objOutlook.CreateItem(olMailItem)
        'Sent from
        objMail.SentOnBehalfOfName = "example.example@example.com"
        'Importance
        objMail.Importance = 1
        'To
        objMail.To = TO1
        'Cc
        objMail.CC = CC1
        'Subject
        objMail.Subject = "Application # " + AN1 + " has been recieved by processing."
        'Email Body
        objMail.HTMLBody = "Test" & "" & "" & StrSignature
        'Add Attachment
        'objMail.Attachments.Add "S:\Community Banking\"
        'objMail.Attachments.Add "S:\Community Banking\"
        'Send email
        objMail.Send
        'Close the object
        Set objMail = Nothing
        
MsgBox "A notification has been succesfully sent to the MLR and BM."

Exit Sub

eh:
   Logger "Error", Err.Source, Err.Description

        
End Sub

Nilson on October 28, 2019 at 12:43 pm

Olá Vishwamitra
Não entendi os parâmetros If Dir(sPath) <> “” Then
Em lt e gt, qual o caminho a considerar?
Desde já lhe parabenizo pelo ótimo trabalho.

Hello Vishwamitra
I didn’t understand the parameters If Dir(sPath) <> “” Then
In lt and gt, which way to consider?
I congratulate you in advance for the great work.
Reply

Prakash on November 29, 2019 at 8:33 am

Hello,

i am using below module for multiple mails but I dont know how to add signature in that.
please help.

"Dear Customer," & _
                "Kind Attn :-" & Cells(i, 7) & ", Company " & Cells(i, 5) & vbNewLine & _
                "Thank you for your recent order " & Cells(i, 6) & "to us." & vbNewLine & vbNewLine & _
                "We are pleased to inform you that the items listed are now on the way to you." & vbNewLine & _
                "Your order has been executed against Sales Order " & Cells(i, 1) & " & Invoiced with " & Cells(i, 2) & " dated " & Cells(i, 3) & ". Materials  have been shipped to your address through Transporter " & Cells(i, 13) & "against docket no. " & Cells(i, 11) & " dated " & Cells(i, 12) & vbNewLine & _
                "Your order should reach you within Few days as mentioned in the website of the Transporter." & vbNewLine & _
                "It has been a pleasure to serve you, and once again, thank you fot choosing our product." & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "Regards," & vbNewLine & _
                "Prakash Patil" & vbNewLine & _
                "Swagelok"

Harris on February 11, 2020 at 1:38 pm

Hi, great code, thank you.
However I would like the signature to appear in the open email that I want to send, and not to open a new email as this code does.
Can you tell me what I need to change to do so?

Regards
Harris
Reply
Harris on February 18, 2020 at 7:45 am

Hi
Great code!
However, I would like to add the signature to an existing email instead of opening a new email message.
How do I do this?
Is your code easily adaptable for this?
Reply
Vanessa on July 10, 2020 at 1:47 am

Hi, I need help on this. I already followed everything. But the image on my signature still could not be displayed.
Reply
Tina on May 11, 2021 at 6:10 pm

For anyone struggling to get this working as expected in later versions, I found the only way to get the file to display was to use this line instead:
StrSignature = VBA.Replace(StrSignature, signImageFolderName + “/”, “file:///” + completeFolderPath + “\”)

Also, if your signImageFolderName contains spaces, ensure that the spaces are replaced by %20 to match how it is referenced in the htm file.
Reply
SRB on August 30, 2021 at 12:10 pm

In my case it does not work (Office 365): the email contains the html tags, like this:

BODY_TEST

[…]
Reply

xsonngu on August 2, 2023 at 4:04 am

I hope this modification can run to display image in signature. I have test and it is ok.

sPath = Environ("appdata") & "\Microsoft\Signatures\Default.htm"
    ' Files folder name is always same name as html file name appended by
    ' "_files"
    signImageFolderName = "Default_files"

    'Add this line to replace  "space" to "%20"
    signImageFolderName = VBA.Replace(signImageFolderName, " ", "%20")

    ' in outlook HTML forward slashes are used for
    ' the file path
    completeFolderPath = Environ("appdata") & "\Microsoft\Signatures\" & signImageFolderName

'Add this line to replace  "/" to "\"
    completeFolderPath = VBA.Replace(completeFolderPath, "/", "\")
    
' 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)
        ' Now replace this incomplete file path
        ' with complete path wherever it is used
' Change string completeFolderPath)
       ' StrSignature = VBA.Replace(StrSignature, signImageFolderName, completeFolderPath)
	 StrSignature = VBA.Replace(StrSignature, signImageFolderName, "file:///" & completeFolderPath)
    Else
        StrSignature = ""
    End If

Hang Bannon on January 29, 2018 at 10:11 am

Dear Vishwamitra
Thank you for your powerfull VBA code 🙂
However, I am experiencing this small problem
– If Dir(sPath) <> “” Then – this part is highlighted in red
I am not sure what went wrong
Please advise
Thank you for your help
Best wishes

Hang Bannon
Reply

Trackbacks/Pingbacks

Welcome to LearnExcelMacro.com How to insert Outlook Signature in Email by Excel VBA - […] To overcome this issue, there are two solutions.. refer to my new article […]