Inserting Signature into Outlook email from Excel

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

标签： excel vba outlook signature

5条回答

做个烂人

2楼-- · 2020-02-02 01:17

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!

0人赞添加讨论(0) 举报

\"骚年 ilove

3楼-- · 2020-02-02 01:26

Another potential solution is to grab the signature directly from the directory where Windows stores it and append it to the body. A Microsoft MVP explains the (somewhat lengthy) process here: https://www.rondebruin.nl/win/s1/outlook/signature.htm

0人赞添加讨论(0) 举报

叼着烟拽天下

4楼-- · 2020-02-02 01:33

You can read the signature file from the Signatures folder (keep in mind that the folder name is localized) and merge it with the message body (you cannot simply concatenate two well formed HTML documents and get back a valid HTML document). You would also need to be careful with the signature styles and images - they must be processed separately.

You can also display the message (Outlook populates the unmodified message body with the signature when the message is displayed) and then either read the HTMLBody property and close the inspector (the flicker is unavoidable). If you want to display the message anyway, display it first so that the signature is inserted by Outlook, and only then add your data (it needs to be inserted, not concatenated).

If using Redemption is an option, it exposes the RDOSignature object. You can use its ApplyTo method to insert any signature into any message.

set Session = CreateObject("Redemption.RDOSession")
Session.MAPIOBJECT = Application.Session.MAPIOBJECT
set Drafts = Session.GetDefaultFolder(olFolderDrafts)
set Msg = Drafts.Items.Add
Msg.To = "user@domain.demo"
Msg.Subject = "testing signatures"
Msg.HTMLBody = "<html><body>some <b>bold</b> message text<br></body></html>"
set Account = Session.Accounts.GetOrder(2).Item(1) 'first mail account
if Not (Account Is Nothing) Then
  set Signature = Account.NewMessageSignature
  if Not (Signature Is Nothing) Then
    Signature.ApplyTo Msg, false 'apply at the bottom
  End If
End If
Msg.Display

0人赞添加讨论(0) 举报

你好瞎i

5楼-- · 2020-02-02 01:34

cut the html source of your signature (for example in an editor or mozilla - source view) and then copy it into a cell. then you can add the value of the cell to your .htmlbody and will be perfect. all other solutions are awful, i tried them all :)

0人赞添加讨论(0) 举报

我想做一个坏孩纸

6楼-- · 2020-02-02 01:38

Solved. Simply restarting Outlook solved it.

0人赞添加讨论(0) 举报

Inserting Signature into Outlook email from Excel

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间