Inserting Signature into Outlook email from Excel VBA

Question

I am trying to automate some emails using VBA for Excel. Everything so far is fine, except trying to keep my signature in the email.

Once you tell VBA to create a new email, it will already contain your default signature. This can be seen if you try Outmail.Display. However if you overwrite the .HTMLBody property, it will be deleted.

My simple attempt was to save the contents of .HTMLBody to a signature (string), and then reassign it to .HTMLBody just to test it out. However the resulting email will be void of any signature.

Code to copy and re-insert the signature:

Dim myOlApp As Outlook.Application
Dim MyItem As Outlook.MailItem

Dim signature As String


Set myOlApp = CreateObject("Outlook.Application")
Set Outmail = myOlApp.CreateItem(0)

signature = Outmail.HTMLBody
Outmail.HTMLBody = signature

Outmail.Display

Code to show that signature is automatically inserted:

Dim myOlApp As Outlook.Application
Dim MyItem As Outlook.MailItem 
Set myOlApp = CreateObject("Outlook.Application")
Set Outmail = myOlApp.CreateItem(0)
Outmail.Display

Edit: I tried replacing .HTMLBody with .Body. That works, but obviously takes out the HTML formatting of the signature

Edit 2: The code works on my friend's computer but not on mine

Answer 1

I solved that issue with this trick:

Set myOutlook = CreateObject("Outlook.Application")
Set tempMail = myOutlook.CreateItem(olMailItem)

With tempMail
  ' Trick to preserve Outlook default signature
  ' MailItem need to be displayed to be "fully created" as object (maybe VBA bug)

  .Display
  HTMLBody = .HTMLBody
  .Close olDiscard

  ' --- Trick to split HTMLBody into Head and Signature ---
  ' Search for the position of the tag before signature
  bodyTag = "<body"
  PosBody = InStr(HTMLBody, bodyTag)
  pTag = "<o:p>"
  PosSignature = InStr(PosBody, HTMLBody, pTag)

  Head = Left(HTMLBody, PosSignature - 1)
  Signature = Right(HTMLBody, Len(HTMLBody) - PosSignature + 1)

End With

Then you can simply put your HTML text between Head and Signature:

Set myOutlook = CreateObject("Outlook.Application")
Set myMail = myOutlook.CreateItem(olMailItem)

With myMail
  .To = Recipients
  .Subject = Subject
  .CC = CC
  .HTMLBody = Head & "Here the HTML text of your mail" & Signature
End With

I think it's a sort of VBA bug: if you don't use the .Display method, the MailItem object is not "fully" created.

Try to place a brakepoint and look at ?mymail.HTMLbody values on Immediate Window (Ctrl + G) before and after the .Display method line....

You can also obtain the same simply expanding mymail object in the Locals Window!