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

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.
Image in Outlook signature

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

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!