How to insert signature (w pic) into Outlook invite containing HTMLBody in excel macro (VBA)

Question

I've created an automation to send out outlook invites, the only thing I cannot manage to do is to get the signature at the end of the invite right. The picture does not display unfortunately.

I'm a beginner in VBA, so I kind of put this together from other macros. I'm using outlook 16

Option Explicit

Sub SendInviteToMultiple()
    Dim OutApp As Outlook.Application, Outmeet As Outlook.AppointmentItem
    Dim I As Long, setupsht As Worksheet, Mtext As Worksheet
    Dim m As Outlook.MailItem
    Dim rtf() As Byte
    Dim S As String

 S = Environ("appdata") & "\Microsoft\Signatures\"
    If Dir(S, vbDirectory) <> vbNullString Then S = S & Dir$(S & "*.htm") Else: S = ""
 S = CreateObject("Scripting.FileSystemObject").GetFile(S).OpenAsTextStream(1, -2).ReadAll
 
    Set setupsht = Worksheets("Outlook")
    Set Mtext = Worksheets("Legend")
    
    For I = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Set OutApp = Outlook.Application
        Set m = OutApp.CreateItem(olMailItem)
        Set Outmeet = OutApp.CreateItem(olAppointmentItem)
        
        With Outmeet
            .Subject = Mtext.Range("I1")
            .RequiredAttendees = setupsht.Range("W" & I).Value
            .OptionalAttendees = setupsht.Range("J" & I).Value
            .Start = setupsht.Range("P" & I).Value
            .Duration = 30
            .Importance = olImportanceHigh
            m.BodyFormat = olFormatHTML
            m.HTMLBody = "Bonjour " & setupsht.Range("H" & I).Value & ",<BR><BR>" & _
            "Dans le cadre de votre " & setupsht.Range("E" & I).Value & ", vous " & Mtext.Range("I4") & " convoqué(e) <b>le " & setupsht.Range("Q" & I).Value & " la médecine du travail qui se trouve:<BR><BR>" & _
            "<b>" & setupsht.Range("C" & I).Value & " - " & setupsht.Range("D" & I).Value & "</b><BR><BR>" & _
            setupsht.Range("AF" & I).Value & "<BR><BR>" & _
            Mtext.Range("I5").Value & "<BR><BR>" & _
            Mtext.Range("I6").Value & "<span style=""color:#ff0000""><b> merci de nous faire parvenir votre <u>fiche d’aptitude.</u></b></span><BR><BR>" & _
            Mtext.Range("I7").Value & "<BR><BR>" & _
            "Cordialment," & S
            m.GetInspector().WordEditor.Range.FormattedText.Copy
            .GetInspector().WordEditor.Range.FormattedText.Paste
            m.Close False
            .Location = setupsht.Range("D" & I).Value
            .MeetingStatus = olMeeting
            .ReminderMinutesBeforeStart = 15
            .Display
            
            '.Send
        End With
        
    Next I
    Set OutApp = Nothing
    Set Outmeet = Nothing
End Sub

I could not even get the signature at all so far, except for the S string but that brings only the text, the pic is an empty shell.

Also, can somebody tell me how can I bring up a window to browser for files to attach to the invite?

Thanks in advance.

Do you get the same picture when you copy the same data in Word and paste it to the appointment body manually? — Eugene Astafiev, Jun 21 '23 at 18:25

score 0 · Answer 1 · answered Jun 21 '23 at 18:30

The Outlook object model doesn't provide any property for setting up the HTML markup for the appointment body. You need to use the RTFBody property for calendar bodies.

However, you can try to set up a low-level property PR_HTML using the PropertyAccessor object in Outlook. If you want to embed images you need to attach corresponding files and then refer to them in the HTML markup using the cid: prefix.

attachment = item.Attachments.Add("c:\temp\Picture.jpg")
attachment.PropertyAccessor.SetProperty("http://schemas.microsoft.com/mapi/proptag/0x3712001F", "YourId")
MailItem.HTMLBody = "<html><body>Test image <img src=""cid:YourId""></body></html>"

Basically, you need to set the PR_ATTACH_CONTENT_ID MAPI property (the DASL name is "http://schemas.microsoft.com/mapi/proptag/0x3712001F") using the Attachment.PropertyAccessor.SetProperty method and refer to that attachment using the src attribute that matches the value of PR_ATTACH_CONTENT_ID which is set on the attachment.

How to insert signature (w pic) into Outlook invite containing HTMLBody in excel macro (VBA)

1 Answers1