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

Federico F. · Accepted Answer · 2017-11-17 16:33:20Z

2

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!

edited Nov 17, 2017 at 16:33

answered Nov 17, 2017 at 11:27

Federico F.

215 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Karl Hoaglund Over a year ago

This worked for me brilliantly. The .Display call seems to complete the formatting of a blank email message, including the pulling in of the signature. You can grab the HTMLBody property of that blank message (a string which is then pages long) and replace the contents of the blank body with your own. In my case, the blank body appeared as the element <o:p> </o:p>. I just split the huge string and replaced the   with my contents. Thanks, Frederico!

msmf14 · Accepted Answer · 2014-03-06 19:50:46Z

1

Solved. Simply restarting Outlook solved it.

answered Mar 6, 2014 at 19:50

msmf14

1,5094 gold badges13 silver badges19 bronze badges

1 Comment

msmf14 Over a year ago

Seems to be a bug in the Outlook/Excel connection I guess. Even with the above fix, I sometimes get the error. The finals solution was just having Outmail.Display several times (i.e. before and after every signature change/insertion etc.

Rodrigo Lemos · Accepted Answer · 2024-05-08 01:49:17Z

This is the function that worked for me and I use it as follows.

.HTMLBody = "My email text" & GetOutlookSignature()

Function code:

Function GetOutlookSignature() As String
Dim fso As Object
Dim ts As Object
Dim strSig As String
Dim strHTML As String

On Error Resume Next
' Path of Outlook signature file
sigPath = Environ("APPDATA") & "\Microsoft\Signatures\"

' Checks if the signatures folder exists
If Dir(sigPath, vbDirectory) <> "" Then
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.OpenTextFile(sigPath & Dir(sigPath & "*.htm"))
    strSig = ts.ReadAll
    ts.Close
    Set fso = Nothing
    Set ts = Nothing
    
    ' Convert signature to HTML format
    strHTML = Replace(strSig, vbCrLf, "<br>")
    
    ' Remove trailing empty lines
    Do While Right(strHTML, 4) = "<br>"
        strHTML = Left(strHTML, Len(strHTML) - 4)
    Loop
    
    GetOutlookSignature = strHTML
    
Else
    GetOutlookSignature = ""
End If
End Function

takanuva15 · Accepted Answer · 2017-06-14 20:55:34Z

0

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

answered Jun 14, 2017 at 20:55

takanuva15

1,7902 gold badges19 silver badges33 bronze badges

Comments

Dmitry Streblechenko · Accepted Answer · 2022-05-03 20:33:48Z

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 (I am its author) 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 = "[email protected]"
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

SandPiper · Accepted Answer · 2022-09-26 01:58:34Z

I'm programming a Submit button on a Word document to send it to support, and found the solution below delivers the signature. This VBA would work the same on an Excel spreadsheet by swapping out the reference to Word, replacing it with an Excel object. Basically, you have to call the .Display command FIRST before asking for the signature. There's a little 'flash' on the email message as the body gets added, but found I can live with that :-).

Private Sub btnSubmit_Click()
    On Error Resume Next
    Dim objOutlook As Object
    Dim objEmail As Object
    Dim objDoc As Document
    Dim emailMsg As String
    
    'let's turn off the screen for a bit...
    Application.ScreenUpdating = False

    Set objOutlook = CreateObject("Outlook.Application")
    Set objEmail = objOutlook.CreateItem(0)     '0=olMailItem    

    'the 'Submit' button is on this Word document, so we'll need
    'to save the file before we can attach it...
    Set objDoc = ActiveDocument
    objDoc.Save
    
    emailMsg = "Here's the body of the email... edit as needed."
          
    'send the email
    objEmail.Subject = "Testing Outlook Signatures"
    objEmail.bodyFormat = 2                         '2=olFormatHTML
     
    objEmail.To = "[email protected]"
    objEmail.Importance = 1                         '1=olImportantceNormal
    objEmail.Attachments.Add objDoc.FullName
    
    'if we don't call this, the email message isn't 'real' yet...
    objEmail.Display
    
    'NOW, we can add the message and the HTML Body, 
    'which is where the signature is...
    objEmail.HTMLBody = emailMsg & objEmail.HTMLBody
    
    'we'll do some cleanup here
    Set objDoc = Nothing
    Set objEmail = Nothing
    Set objOutlook = Nothing
    Application.ScreenUpdating = True
End Sub

Tedinoz · Accepted Answer · 2024-05-31 01:37:46Z

0

Simplifying the solution by @FedericoF, and assuming that the signature auto-populates, just prepend the desired body text before the current HTMLBody:

With CreateObject("Outlook.Application").CreateItem(0)
    'open email first to populate signature into HTMLBody
    .Display
    .To = "Whoever"
    .Subject = "Anything"
    .HTMLBody = <Your text> & .HTMLBody
End With

edited May 31, 2024 at 1:37

Tedinoz

8,3943 gold badges30 silver badges44 bronze badges

answered May 28, 2024 at 20:09

Eric J

1

Comments

La-comadreja · Accepted Answer · 2014-06-25 15:31:14Z

-1

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 :)

edited Jun 25, 2014 at 15:31

La-comadreja

5,74512 gold badges38 silver badges64 bronze badges

answered Jun 25, 2014 at 14:35

Viktor

1

1 Comment

msmf14 Over a year ago

I've been using mine successfully since, I send the same email (and others) everyday with no problem by using Outmail.Display several times. Though friends do not need the Outmail.Display for it to work.

Collectives™ on Stack Overflow

Inserting Signature into Outlook email from Excel VBA

8 Answers 8

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related