Inserting a Range into an Outlook Appointment

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:

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.

8 thoughts on “Inserting a Range into an Outlook Appointment

  1. Dick, I’d be interested in the code that “would convert the range to HTML and use that property.” I’m working on a once-a-year mailing now where I just build the the email body with a bunch of .HTMLBody = .HTMLBody & ... statements. It works okay but it's a bit of a pain to edit hyperlinks and whatnot.

    Before I figured out how HTMLBody works (I'm not sure what my issue was) I did have a painful afternoon of cobbling together a bunch of those RTF codes. I tried to switch to the dentist option midstream but it was too late :)

  2. Doug: Ron has a RangeToHTML function here http://www.rondebruin.nl/win/s1/outlook/bmail2.htm. I think I had a hand in writing that (he’s improved it so much I don’t recognize it), but I don’t use that method anymore. There’s too much baggage in letting MS generate the HTML. So I figure out what elements are important, and code them by hand. I don’t need to check every possible cell property, just the ones I care about. If borders are important, then handle them. If italics is important, check for it. Etc.

    That requires that you know HTML, but everyone should know that anyway. Here’s the function I use to wrap values in html tags

    I’ll post an example next week.

  3. Why not just copy and paste the range direct from the Speadsheet to the body of the appointment, works very well for me

  4. I get a Run-time error ‘9’

    Subscript out of range?

    “Sheet1.ListObjects(1).Range.Copy”

    What did I do wrong?

  5. @John You probably don’t have your data in a table. The error is telling you there is no

    . You can reference the range directly, like

    or use Ctrl+T to change your range into a table.

  6. your code would be exactly what I’m looking for – but when running I get “Method WordEditor of Object _Inspector failed”

    what did I make wrong ?
    Is ist working under office365 ?

Leave a Reply

Your email address will not be published. Required fields are marked *