Page 477 - Microsoft Office Excel 2003 Programming Inside Out

Excel and Other Office Applications

Sub MakeOutlookAppointmentLateBinding()
Dim olApp As Object
Dim olAppointment As Object
Const olAppointmentItem = 1

Set olApp = CreateObject("Outlook.Application”)
Set olAppointment = olApp.CreateItem(olAppointmentItem)

With olAppointment
.Subject = "Spring Sales Initiative Meeting"
.Location = "Radisson: Meeting Room A"
.Start = DateSerial(2005, 3, 7) + TimeSerial(9, 30, 0)
.End = DateSerial(2005, 3, 7) + TimeSerial(11, 30, 0)
.ReminderPlaySound = True
.Save
End With
olApp.Quit
Set olApp = Nothing
End Sub

The basic technique in programming another application is to create an object variable refer-
ring to that application. The object variable in this case is olApp. You then use the olApp vari Chapter 21
able to refer to objects in the external application’s object model. In this example, the
CreateItem method of Outlook’s Application object is used to create a reference to a new
AppointmentItem object.
Because Outlook’s constants are not available when late binding is used, you must define
your own constants, such as olAppointmentItem in this example, or substitute the value of the
constant as the parameter value. The properties and methods of the Appointment object in
the With…End With structure modify the new object that was created.

When declaring the olApp and olAppointment as generic Object types, late binding is forced
on the VBA procedure. All the links to Outlook can’t be established until the procedure exe
cutes the CreateObject function. The CreateObject input argument defines the application
name and class of object to be created. Outlook is the name of the application and Application
is the class. Many applications allow you to create objects at different levels in the object
model. For example, Excel allows you to create WorkSheet or Chart objects from other applica
tions, using Excel.WorkSheet or Excel.Chart as the input parameter of the CreateObject function.

Tip Save Your Memory
It’s good programming practice to close the external application when you are finished with
it and set the object variable to Nothing. This releases the memory used by the link and the
application.

If you execute this macro in Excel, it appears as though nothing has happened. However, if you
open Outlook and navigate to March 7, 2005, you’ll see the appointment has been added to
the Calendar. Figure 21-2, displays the appointment created in the Calendar on March 7, 2005.

451
Part 6: Excel and the Outside World: Collaborating Made Easy