GuillaumeWaeber-6603 avatar image
0 Votes"
GuillaumeWaeber-6603 asked ·

Image of signature disappearing in email after VBA

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
 End With
     Signature = newEmail.HTMLBody
 With newEmail
     .To = Sheets("OPOS").Range("H1")
     .CC = ""
     .BCC = ""
     .Subject = "Data"
     .HTMLBody = "Please find the requested information:" & "<br></br>" & "<br></br>" & "<br></br>" & ""
     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
 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:" & "<br></br>" & "<br></br>" & "<br></br>" & "" & 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,

· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @GuillaumeWaeber-6603,

Welcome to our forum.

Please kindly understand under Outlook tag, we mainly focus on general issues about Outlook desktop client, considering your issue may be more related to development, I removed outlook tag, thanks for your understanding and hope your issue would be resolved soon.

0 Votes 0 ·

The same reason as Outlook, I would remove the tag "office-excel-itpro", as it focus more on general issues of Excel.
Thanks for your understanding.

0 Votes 0 ·

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered ·

Maybe the Signature is a variable that contains full HTML, i.e. ‘<html>…<body>…</body></html>’. Then the final .HTMLBody, after concatenations, will represent an invalid HTML, which contains unexpected inner <html> 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 <body>…</body> 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.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you very much for the quick answer!

However, I have to admit, I am quite beginner in programming. Do you have a concrete example of a string function?
I have tried to do some research about it and made a couple of trials in my code, but so far, it is inconclusive...


0 Votes 0 ·