Saturday, July 14, 2007

Automating Outlook with Ruby: Calendar Appointments

In a previous article, we looked at how to automate Microsoft Outlook to create and send a new email message. This time around, we'll use Ruby to extract appointments data from the Outlook Calendar, delete an appointment, and create a new appointment.

As usual, we'll use the win32ole library to create a new instance (or connect to a currently running instance) of the Outlook application object:


require 'win32ole'
outlook = WIN32OLE.new('Outlook.Application')

Next we'll get the MAPI namespace:

mapi = outlook.GetNameSpace('MAPI')

Outlook consists of several Folder objects, including Inbox, Tasks, and Calendar. To get a folder object, call the MAPI object's GetDefaultFolder method, passing it an integer representing the folder to return. For the Calendar folder, this number is 9:

calendar = mapi.GetDefaultFolder(9)

The calendar folder's Items method returns a collection of all appointments, which you can iterate over. Each appointment object includes dozens of properties. The following code prints out six of the most-often used properties:

calendar.Items.each do |appointment|
puts appointment.Subject
puts appointment.Location
puts appointment.Start
puts appointment.Duration
puts appointment.End
puts appointment.Body
end

To delete an appointment, call its Delete method. For example, the following code locates an appointment based on its Subject value, then deletes it, if found:

calendar.Items.each do |appointment|
if appointment.Subject == 'Punch Bud Selig in the Nose'
appointment.Delete
end
end

To create a new appointment, call the Outlook Application object's CreateItem method, passing it the number 1, which represents a Calendar Item. This returns a new Appointment object:

appointment = outlook.CreateItem(1)

Then set values for various properties of the new Appointment object:

appointment.BusyStatus = 2
appointment.Start = '7/29/2007 11:00 AM'
appointment.Duration = 300
appointment.Subject = 'Baseball Hall of Fame Induction'
appointment.Body = 'Tony Gwynn and Cal Ripken Jr.'
appointment.Location = 'Cooperstown, NY'
appointment.ReminderMinutesBeforeStart = 15
appointment.ReminderSet = true

The BusyStatus value is an integer with the following possible values:

olFree = 0
olTentative = 1
olBusy = 2
olOutOfOffice = 3

The Duration value is an integer representing the appointment length in minutes.

For an all-day event, forget the Duration property and instead set the AllDayEvent property to true:

appointment.AllDayEvent = true

To make an appointment recurring, you'll want to work with the Appointment object's Recurrence child object. You get this object by calling the GetRecurrencePattern method:

recurrence = appointment.GetRecurrencePattern

Now you'll set the RecurrenceType to one of the following values:

0 -- To set an appointment that occurs each and every day.
1 -- To set an appointment that occurs on the same day each week.
2 -- To set an appointment that occurs on the same day each month.
5 -- To set an appointment that occurs on the same day each year.

So, for an appointment that occurs on this day and time (as defined in your Start property above) each week:

recurrence.RecurrenceType = 1

Next, we'll set the PatternStartDate property. This value needs to be on or before the Start value defined above:

recurrence.PatternStartDate = '8/6/2007'

Finally, we set the PatternEndDate property:

recurrence.PatternEndDate = '8/27/2007'

When you've completed setting property values, call the Appointment object's Save method:

appointment.Save

And there you have it. Further details can be found in this Microsoft TechNet article.

Questions? Comments? Suggestions? Post a comment here or send me email.

Thanks for stopping by!


Digg my article

9 comments:

GimbalGambit said...

Dave,

Great series on Outlook automation, this is very handy! Just a quick question: In setting an AllDayEvent, if you don't use appointment.Start to set the date of that appointment, how do you do it? Also, any pointers on recurrence?

Thanks!

Anonymous said...

Thanks for a very useful site!

Do you have any tips on reading appointments that are in shared/public calendars?

David Mullet said...

@warren:

Good catch, thanks!

Yes, you do need to define the Start value [slaps forehead].

I have updated the article with this correction, and added new info about creating a recurring appointment.

David

GimbalGambit said...

Beautiful, thanks for the update! I manage the on-call calendar for our software team (24x7 healthcare apps) and now I can automate the scheduling process without all the manual work... Thank you!

David Mullet said...

That's excellent news, Warren!

"Use the Force, Luke!"

Anonymous said...

Wow - what a great set of information you posted. I used it to create appointments from within database application. One question though, are you aware of a way to set the "Show time as:" field to be "busy, out of office, tentative" etc.

Thanks for a incredible set of articles.

Norb

David Mullet said...

@Norb:

"a way to set the "Show time as:" field to be "busy, out of office, tentative" etc."

Set the BusyStatus property:

olFree = 0
olTentative = 1
olBusy = 2
olOutOfOffice = 3

appointment.BusyStatus = olBusy

I'll add this to the article.

Thanks!

David

Unknown said...

Hi,

great blog!

But one question, where can I find the documentation for the ole interface, so that I can for example find out the values like

olFree = 0
olTentative = 1
olBusy = 2
olOutOfOffice = 3

myself? I searched the msdn, but I couldn't find useful stuff.

Thanks.

davidbe said...

Great articels. Though, I'm puzzled with something.

I would like to filter on appointments. I'm doing the same thing like mentioned on the tasks-blog (which works) and I try:

strFind = "[Start] >= 2009/07/27 00:00:00 AND [End] < 2009/08/03 00:00:00"
for app in calendar.Items.Restrict(strFind)
puts app.Subject
end

When running this, I get an error:
calendar.rb:30:in `method_missing': Restrict (WIN32OLERuntimeError)
OLE error code:4096 in Microsoft Office Outlook
Kan het criterium niet analyseren. Fout bij 00.
HRESULT error code:0x80020009

Any idea what's going wrong?

Thanks in advance!