Image of signature disappearing in email after VBA

Question

Hi everyone,

I am currently facing an issue programming a macro.

What I am trying to do is generate an email automatically with some of my excel data in the body of the email. Here are my steps:

Have some text
Add a copied selection from my spreadshhet
Add some text again
And finally add my signature to the email (I use my standard, preset signature in outlook)

My Signature contains an image with an hyperlink, but the problem is that it disappears in my end result

Here is my code:

Dim Outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object
Set Outlook = CreateObject("Outlook.Application")
Set newEmail = Outlook.CreateItem(0)

With newEmail
    .Display
End With
    Signature = newEmail.HTMLBody

With newEmail
    .Display
    .To = Sheets("OPOS").Range("H1")
    .CC = ""
    .BCC = ""
    .Subject = "Data"
    .HTMLBody = "Please find the requested information:" & "

" & "

" & "

" & ""

    Set xInspect = newEmail.GetInspector
    Set pageEditor = xInspect.WordEditor
    Sheets("OPOS").Range("A6", "C" & Last_row2 - 2).Copy
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
    Set pageEditor = Nothing
    Set xInspect = Nothing

    .HTMLBody = .HTMLBody & "Best regards" & Signature

    .Display

End With

Set newEmail = Nothing
Set Outlook = Nothing

I have noticed that if I add my signature in my first line of .HTMLBody as follows:
.HTMLBody = "Please find the requested information:" & "

" & "

" & "

" & "" & Signature
then the signature appears correctly, with the image.

Does any body knows where the problem lies when I try to add the signature in the end?
For information, I am on Windows 8 and work with MS Office Professional Plus 2016.

Best regards,
Guillaume

Answer

Maybe the Signature is a variable that contains full HTML, i.e. ‘……’. Then the final .HTMLBody, after concatenations, will represent an invalid HTML, which contains unexpected inner element, that cannot be always recognized.

Make sure that the final .HTMLBody is a valid text according to HTML rules. For example, maybe extract and use the part between … from Signature variable, using some string functions.

Perhaps you can also use the Value property of Range to get the text from OPOS sheet and use string manipulations instead of inspector and page editor.

Answer

Hi,

I got the same problem and it was resolved using the Collapse method. Try calling it before calling the PasteandFormat

Image of signature disappearing in email after VBA

2 answers