Jesse asks:
In VBA, how do I add a range of cells to the body of an appointment?
Unlike email, the AppointmentItem does not have an HTMLBody property. If it did, then I would convert the range to HTML and use that property. Formatted text in the body of an AppointmentItem is Rich Text Format (RTF). I don’t know of any good ways to convert a range to RTF. Sure, you could learn what all the RTF codes are and build the string to put into the RTFBody property of the AppointmentItem. Then you could go to the dentist for a no-novocaine root canal. I’m not sure which of those would be more fun.
A better way is to programmatically copy the range and paste it into the body of the appointment. Since Office 2007, almost every Outlook object allows you to compose in Word. That’s an option I quickly turn off, but it’s still there under the hood. We’ll use that to our advantage. But first, let’s set up some data. Here I have a Table in Excel with some sample data.
To create a new appointment with this range in the body, I used this code:
Sub MakeApptWithRangeBody()
Dim olApp As Outlook.Application
Dim olApt As Outlook.AppointmentItem
Const wdPASTERTF As Long = 1
Set olApp = Outlook.Application
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt
.Start = Now + 1
.End = Now + 1.2
.Subject = "Test Appointment"
Sheet1.ListObjects(1).Range.Copy
.Display
.GetInspector.WordEditor.Windows(1).Selection.PasteAndFormat wdPASTERTF
End With
End Sub
Be sure to set a reference to the Microsoft Outlook 1x.x Object Library (VBE – Tools – References). The code produces this happy customer.
The code creates an appointment and fills in some properties, like Start, End, and Subject. The Excel Table is copied to the clipboard ready to be pasted into the appointment. Before we can get to the AppointmentItem’s WordEditor, we have to display it. That’s why the .Display method comes before the paste operation.
The last bit is to paste the range. Starting with an AppointmentItem, we have to get the Inspector object, then the WordEditor object, then a Window object, and finally we can use the PasteAndFormat method on the Selection object. Gool ol’ Word where everything is a Selection object.
This code simply displays the appointment, you will need the .Save method, .Close method, or .Send method if you want to automate any of that.
