How to add an html signature to Outlook message from Access with VBA?

Question

Code posted here and been reported to work, doesn't work for me.

This code throws

Run Time Error '287 Application-defined or object defined error

on the line Signature = Outmail.body.

I want the default signature for the current Outlook user.

Private Sub Command33_Click()

Dim OutApp As Object
Dim OutMail As Object
Dim currentDate As Date
Dim DeliveryDate As String
Dim Recipients As String
Dim CarbonCopy As String
Dim Signature As Variant

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

currentDate = Format(Date, "dd/mm/yyyy")
Recipients = "a@gmail.com"
CarbonCopy = "b@gmail.com"
Signature = OutMail.Body

'msg hasn't been defined so it's commented out. msg in the body has been replaced with "msg".
'msg = "<span style='color:black'><p>Dear Team,</p>"

'msg = msg & "Thank you in advance</span>"

On Error Resume Next
With OutMail
    'Capture signature block.
    .Display
    Signature = .HTMLBody
    .To = Recipients
    .CC = CarbonCopy
    .Subject = "PSR " & currentDate
    .HTMLBody = "<span style = 'color:#1F497D'>" & "msg" & "</span>" & Signature
    .Attachments.Add ThisWorkbook.FullName
    .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Does this answer your question? [How to add default signature in Outlook](https://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook) — narengi, Nov 19 '20 at 16:28
The solutions in the suggested threads do NOT work. They also fail on the line for setting Signature variable = Outmail.body, or some variation thereof. — Michael Rousseau, Nov 19 '20 at 18:23
Remove that line. I don't think you can access the body before the `.Display` line. Your code works for me. You're correctly setting the value of `Signature = .HTMLBody` just below the `.Display` line and therefore, do not need `Signature = OutMail.Body` — Super Symmetry, Nov 19 '20 at 19:09

Andre · Answer 1 · 2020-11-19T19:32:18.810

0

As stated, remove Signature = OutMail.Body.

Then put a break point after Signature = .HTMLBody, and inspect its value.

This is what I do in Outlook 365 (it also worked in Outlook 2010):

' Outlook HTML: after Mailitem.Display, .HtmlBody starts with
' <p class=MsoNormal><o:p>&nbsp;</o:p></p>
' Exactly in this line we want to put our HTML encoded mail text.
oItem.HtmlBody = Replace(oItem.HtmlBody, "&nbsp;", strMyBody, Count:=1)

The default signature is below this line, and remains unchanged.

Edit: our HTML encoded mail text means: I pass plain text to my function, and replace vbCrLf by <br>.

If you have actual HTML with <p> and so on, use Jeremy's answer instead.

edited Nov 19 '20 at 19:32

answered Nov 19 '20 at 19:18

Andre

26,751
7
36
80

Inspection of value with break point as you suggested shows Signature = Empty. The signature is there though, but disappears the moment this line runs: .HTMLBody = "" & "msg" & "" & Signature – Michael Rousseau Nov 19 '20 at 21:27
Remove `On Error Resume Next` to see what's going on. @MichaelRousseau – Andre Nov 19 '20 at 22:54

score 0 · Answer 2 · answered Nov 19 '20 at 19:31

If removing the line Signature = OutMail.Body doesn't work try the WordEditor approach. Each mail item has a word editor which is simply a word document. It is much easier to work with than generating your own html. You can also simply copy and paste (even excel ranges) and all formats will be preserved.

Private Sub Command33_Click()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim currentDate As String
    Dim DeliveryDate As String
    Dim Recipients As String
    Dim CarbonCopy As String
    
    Dim EmailMsg As String
    EmailMsg = "msg"
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    currentDate = Format(Date, "dd/mm/yyyy")
    Recipients = "a@gmail.com"
    CarbonCopy = "b@gmail.com"
    
    EmailMsg = "Dear Team," & Chr(10) & "Thank you in advance"
        
    With OutMail
        .To = Recipients
        .CC = CarbonCopy
        .Subject = "PSR " & currentDate
        .Display
        
        With .GetInspector.WordEditor
            .Range(0, 0).Text = EmailMsg
            .Range(0, Len(EmailMsg)).Font.TextColor = RGB(31, 73, 125)
        End With
        
        ' display the generated html body in the immediate window
        ' remove after testing
        Debug.Print .HTMLBody
        
        .Attachments.Add ThisWorkbook.FullName
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Also note Format() returns a string and therefore, currentDate should be a string (not a date)

Thanks, but the code you posted also throws a Run Time Error 287. This time on With .GetInpector.WordEditor. Could it be a Reference that I need to check besides Word 16.0 Object library? — Michael Rousseau, Nov 19 '20 at 21:17
You're using late binding so you don't need any particular reference. Is your outlook set up properly? Try to create a new message manually in outlook and send it. See if any message box comes up. Keep your outlook application running and try executing the code again — Super Symmetry, Nov 19 '20 at 21:32
Thanks Super Symmetry. I wish it were something as simple as that. Outlook is set up properly, a new message created in Outlook brings the default signature with no issues. — Michael Rousseau, Nov 20 '20 at 02:35
"Application-defined or object defined error" is VBA's way of telling you that outlook is complaining and I don't know why. So sometimes performing the same steps manually highlights the actual issue. 2 more suggestions: (1) Try to run this code from within outlook - open outlook and press Alt+F11. (2) Try to send some test emails using word's mail merge. The first might give you a more specific error and the second might reset something that was causing the issue. — Super Symmetry, Nov 20 '20 at 05:35

How to add an html signature to Outlook message from Access with VBA?

2 Answers2