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.
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.Basically, you need to set the
PR_ATTACH_CONTENT_ID MAPIproperty (the DASL name is "http://schemas.microsoft.com/mapi/proptag/0x3712001F") using theAttachment.PropertyAccessor.SetPropertymethod and refer to that attachment using thesrcattribute that matches the value ofPR_ATTACH_CONTENT_IDwhich is set on the attachment.