Inserting a Range into an Outlook Appointment

Posted on January 21, 2015January 17, 2015 by Dick Kusleika

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

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.

8 thoughts on “Inserting a Range into an Outlook Appointment”

Doug Glancy says:

January 21, 2015 at 10:31 pm

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 :)
snb says:

January 22, 2015 at 2:39 am

Doug,

You might have a look at:

http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html#H2

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

Function Tag(sValue As String, sTag As String, Optional sAttr As String = "", Optional bIndent As Boolean = False) As String
   
    Dim sReturn As String
   
    If Len(sAttr) > 0 Then
        sAttr = Space(1) & sAttr
    End If
   
    If bIndent Then
        sValue = vbTab & Replace(sValue, vbNewLine, vbNewLine & vbTab)
        sReturn = "<" & sTag & sAttr & ">" & vbNewLine & sValue & vbNewLine & "</" & sTag & ">"
    Else
        sReturn = "<" & sTag & sAttr & ">" & sValue & "</" & sTag & ">"
    End If
   
    Tag = sReturn
   
End Function

Function Tag(sValue As String, sTag As String, Optional sAttr As String = "", Optional bIndent As Boolean = False) As String

Dim sReturn As String

If Len(sAttr) > 0 Then

sAttr = Space(1) & sAttr

End If

If bIndent Then

sValue = vbTab & Replace(sValue, vbNewLine, vbNewLine & vbTab)

sReturn = "<" & sTag & sAttr & ">" & vbNewLine & sValue & vbNewLine & "</" & sTag & ">"

Else

sReturn = "<" & sTag & sAttr & ">" & sValue & "</" & sTag & ">"

End If

Tag = sReturn

End Function

I’ll post an example next week.

Pete Crich says:

January 23, 2015 at 9:03 am

Why not just copy and paste the range direct from the Speadsheet to the body of the appointment, works very well for me
Dick Kusleika says:

January 25, 2015 at 8:06 am

In code? I think that’s what the code does.
JOHN MCLAUGHLIN says:

December 10, 2016 at 6:41 am

I get a Run-time error ‘9’

Subscript out of range?

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

What did I do wrong?
Dick Kusleika says:

December 14, 2016 at 9:58 am

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

Visual Basic

ListObjects(1)

1

ListObjects(1)

. You can reference the range directly, like

Visual Basic

Sheet1.Range("A1:D10")

1

Sheet1.Range("A1:D10")

or use Ctrl+T to change your range into a table.
Peter Sturm says:

May 31, 2017 at 5:12 am

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 ?