Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Creating a Microsoft Outlook Appointment

Devoted readers of the Office Space column (both of you) might have noticed that, during its first month of existence, the column barely even mentioned Microsoft Outlook. There’s a reason for that: because of all the new security features added to Outlook it can be difficult to write scripts that run in truly automated fashion. For example, you can still send mail from a script; however, before the mail actually gets sent you have to manually click a dialog box that requests permission to use your Outlook account to send mail. Needless to say, this somewhat defeats the whole idea of scripting, which is supposed to provide a quick and easy way to carry out tasks, and without any user intervention whatsoever.

On the other hand, just because you can’t do some tasks in fully-automated fashion doesn’t mean you can’t do any tasks in automated fashion. Therefore, we’ll try to do a better job of incorporating Outlook scripts into the rotation, making sure we cover those tasks that are still easy to script. Tasks like, say, adding an appointment to your Outlook calendar.

We decided to start with creating an appointment for two reasons. First, it’s a very common and very useful task; second, it’s also very easy to create an appointment using a script. In fact, all you really have to do is call the CreateItem method and tell CreateItem that you want to create an appointment. You then set the appointment properties, call the Save method and, just like that, you’ve added an appointment to your calendar.

Man, had we known Outlook was going to be this easy we would have written a bunch of columns about it!

Here’s what a sample appointment script looks like:

Const olAppointmentItem = 1

Set objOutlook = CreateObject("Outlook.Application")
Set objAppointment = objOutlook.CreateItem(olAppointmentItem)

objAppointment.Start = #4/11/2005 11:00 AM#
objAppointment.Duration = 60
objAppointment.Subject = "Scripting Guys Planning Session"
objAppointment.Body = "Meet with Scripting Guys to discuss upcoming plans."
objAppointment.Location = "44/2039"
objAppointment.ReminderMinutesBeforeStart = 15
objAppointment.ReminderSet = True
 
objAppointment.Save

As you can see, we begin by defining a constant named olAppointmentItem and setting the value to 1; this happens to be the value that tells CreateItem that we want to create an appointment rather than, say, a contact or a mail message. Next we create an instance of the Outlook.Application object; in this sample script, we’re assuming that Outlook is already running. We then call the CreateItem method to create a “blank” appointment.

All that’s really left at this point is to assign values to the appointment properties. Most of these properties should be self-explanatory; we’ll just point out two things:

To ensure that your appointment Start time gets passed as a date-time value, surround the date and time with pound signs (#) instead of double quote marks. In other words, don’t do this:

objAppointment.Start = "4/11/2005 11:00 AM"

Instead, use the pound sign as your delimiter:

objAppointment.Start = #4/11/2005 11:00 AM#

The truth is, VBScript is pretty savvy when it comes to handling dates and times, and it’s unlikely to do something weird, like, say, see 4/11/2005 as a mathematical equation (4 divided by 10 divided by 2005). But no sense taking any chances, right?

Second, note that both the Duration and the ReminderMinutesBeforeStart properties are expressed in minutes. Want to set a 2-hour appointment? Then set the value of the Duration property to 120 (120 minutes):

objAppointment.Duration = 120

After that all you have to do is call the Save method to save the appointment to your calendar.

When you run the script, you should end up with a new appointment that looks a lot like this:

But don’t get too excited just yet; after all, there’s more to come. The preceding script sets a one-time appointment for you. That’s nice, but what if you need to set a recurring appointment? What are the odds that you could quickly and easily do that with a script?

Actually, the odds are pretty good:

Const olAppointmentItem = 1
Const olRecursWeekly = 1

Set objOutlook = CreateObject("Outlook.Application")
Set objAppointment = objOutlook.CreateItem(olAppointmentItem)

objAppointment.Start = #4/11/2005 11:00 AM#
objAppointment.Duration = 60
objAppointment.Subject = "Scripting Guys meeting"
objAppointment.Body = "Meeting with Scripting Guys to discuss upcoming plans."
objAppointment.Location = "44/2039"
objAppointment.ReminderMinutesBeforeStart = 15
objAppointment.ReminderSet = True

Set objRecurrence = objAppointment.GetRecurrencePattern
objRecurrence.RecurrenceType = olRecursWeekly
objRecurrence.PatternStartDate = #4/11/2005#
objRecurrence.PatternEndDate = #6/11/2005#
 
objAppointment.Save

As you can see all we’ve done is taken our one-time appointment script and added a few additional lines of code to it. To begin with, we defined a second constant (olRecursWeekly); we’ll use that to tell Outlook that this recurring appointment should take place each and every week. (As recurring appointments are wont to do, the appointment will occur on the same day and at the same time each week.)

Next we create an instance of the oddly-named RecurrencePattern object; this is done by calling the GetRecurrencePattern method:

Set objRecurrence = objAppointment.GetRecurrencePattern

With this object in tow, we then set the following three properties:

RecurrenceType. This simply indicates the frequency with which the appointment recurs. In our sample script, we chose to have the appointment recur once a week. Other recurrence types include the following:

PatternStartDate. The date that the recurring pattern should begin. Note that this doesn’t have to be the date of the first meeting. For example, suppose you set the Start property to #4/11/2005 11:00 AM#. You can set the value of PatternStartDate to any time before April 11th (for example, 4/9/2005).

PatternEndDate. The date that the recurring pattern should end. Again, this doesn’t have to be exact. Say you’ve scheduled appointments for each Monday. The PatternEndDate does not have to be a Monday; if you want appointments to run only through June 6th you can set the PatternEndDate to, say, 6/11/2005. Outlook will simply schedule an appointment every Monday through June 11th, then won’t schedule any additional appointments after the 11th.

With the RecurrencePattern properties all filled out we call the Save method, and we now have a recurring appointment that begins on Monday, April 11th and continues on through Monday, June 6th.

And there you have it: a fully-automated way to add appointments to your Outlook calendar. And now that we know how to set appointments like this, heck, we might even use it to remind ourselves to include more Outlook scripts in the Office Space column.