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:" & "<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 .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:" & "<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.