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)
Signature = newEmail.HTMLBody
.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
Set pageEditor = Nothing
Set xInspect = Nothing
.HTMLBody = .HTMLBody & "Best regards" & Signature
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.