Automating the Calendar

Below are some examples for using the Calendar in Outlook. Before you can use these example you must set a reference to the Outlook Object Library.

Creating an Appointment

Sub SetAppt()

    Dim olApp As Outlook.Application
    Dim olApt As AppointmentItem

    Set olApp = New Outlook.Application
    Set olApt = olApp.CreateItem(olAppointmentItem)

    With olApt
        .Start = Date + 1 + TimeValue("19:00:00")
        .End = .Start + TimeValue("00:30:00")
        .Subject = "Piano lesson"
        .Location = "The teachers house"
        .Body = "Don't forget to take an apple for the teacher"
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = 120
        .ReminderSet = True
        .Save
    End With

    Set olApt = Nothing
    Set olApp = Nothing

End Sub

Comments:

The CreateItem method is used to create the new appointment. This method can be used to create just about anything in Outlook; Emails, Tasks, Journal Entries, etc. olAppointmentItem is a built in constant that tells VBA which type of item to create.

The With/End With block shows some typical properties that can be set for an AppointmentItem object. The .Start and .End properties accept Date data types. In this example, I use the Date keyword which returns the current date, add one (for tomorrow) and use the TimeValue function to make my start time 7:00 P.M. Because Excel stores dates as the number of days since January 1, 1900, adding these three elements together will result in the desired start date and time. To set the end time, I added 30 minutes to the value of the .Start property. There is also a .Duration property which I could have used to have Outlook set the .End property automatically. I use the .End property out of habit, but the Duration property would probably be the more logical choice for this example. Note that the .Duration property accepts a Long Integer data type, not a Date. The Long Integer that .Duration accepts is the number of minutes the appointment will last.

The .BusyStatus property is set using a built-in constant, namely olBusy. The other options are olFree, olOutOfOffice, and olTentative. Note that if you use late binding, you must convert any built-in constants that you use to their numeric equivalent.

The .ReminderMinutesBeforeStart property accepts a Long Integer data type similar to the .Duration property. Although it has a long property name, it was nice of the developers to include the word Minutes in there to remind me what I'm supposed to be entering. You can set the .ReminderMinutesBeforeStart property all you like, but if you don't set the .ReminderSet property to True, you won't get a reminder. It doesn't matter which order the properties are set, however. If you set the .ReminderSet property to True, but don't set the .ReminderMinutesBeforeStart property, Outlook uses the number of minutes that is set under Tools - Options - Preferences.

.Save says it all. But oh boy is it important. You can also use the .Display method to show the unsaved appointment. This is useful during testing while you're working out the kinks.

Prev Page: Retrieving E-Mail Top of This Page Next Page: Contacts

Retrieving an Appointment

Sub GetAppt()

    Dim olApp As Outlook.Application
    Dim olNs As NameSpace
    Dim olFldr As MAPIFolder
    Dim olApt As AppointmentItem

    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    Set olFldr = olNs.GetDefaultFolder(olFolderCalendar)

    For Each olApt In olFldr.Items
        If InStr(1, olApt.Subject, "Piano", vbTextCompare) > 0 Then
            Debug.Print olApt.Subject, Format(olApt.Start, "mm/dd/yy")
        End If
    Next olApt

    Set olApt = Nothing
    Set olFldr = Nothing
    Set olNs = Nothing
    Set olApp = Nothing

End Sub

Comments:

The NameSpace object isn't a typical object. There is no physical representation of the NameSpace as there is for a Folder or an AppointmentItem. It serves as a gateway for access to much of the objects in Outlook's object model. To get at the NameSpace, you use the GetNameSpace method of the Application object. The only argument that is supported for this method is "MAPI", so that makes it nice and easy.

The Folder object represents actual folders in Outlook. To see these folders, choose Folder List from the View menu in Outlook. You may have nested folders, that is, folders that contain MailItems that are subordinate to the Inbox or folders that contain AppointmentItems that are subordinate to the Calendar. This procedures uses a default folder which is particularly easy because Outlook gives us the GetDefaultFolder method of the NameSpace object. If the GetDefaultFolder method wasn't available, that line of code would look like this:

Set Fldr = olNs.Folders("Personal Folders").Folders("Calendar")

Understanding this technique is useful when you need to access a folder that is not a default folder. Nesting the .Folders property allows access to any folder in Outlook. You just have to remember that the top folder is Personal Folders and the rest is easy when you see the folder list by using View - Folder List in Outlook.

The meat of the procedure simply uses a For Each statement to loop through all of the AppointmentItems in the Folder. When it finds one that satisfies the criteria, it prints certain properties to the Immediate Window. This example tests for the word "Piano" in the .Subject property by using the InStr function. You can test for whatever properties that suit your situation. Also, once the object variable, olApt, points to the correct AppointmentItem, you can do anything you want to it. You can write its properties to cells or even change its properties. If you change anything, be sure to use the .Save method or you will be disappointed.

Prev Page: Retrieving E-Mail Top of This Page Next Page: Contacts

Showing the Calendar

Sub ShowCalendar()

    Dim olApp As Outlook.Application
    Dim olNs As NameSpace

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")

    If Err.Number = 429 Then
        Set olApp = CreateObject("Outlook.Application")
    End If

    On Error GoTo 0

    Set olNs = olApp.GetNamespace("MAPI")

    If olApp.ActiveExplorer Is Nothing Then
        olApp.Explorers.Add _
            (olNs.GetDefaultFolder(olFolderCalendar)).Activate
    Else
        Set olApp.ActiveExplorer.CurrentFolder = _
            olNs.GetDefaultFolder(olFolderCalendar)
        olApp.ActiveExplorer.Display
    End If

    Set olNs = Nothing
    Set olApp = Nothing

End Sub

Comments:

I created this example in response to a newsgroup question. I hadn't thought of doing it before that, but it was such an interesting question, that it warranted inclusion here. I also got the opportunity to use some Outlook objects that I hadn't used before.

To set the olApp object variable, I use statements similar to what might be used if the procedure was late bound. It is decidedly not late bound, but I include it here for illustrative purposes. The lines that start with "On Error Resume Next" and end with "On Error Goto 0", inclusive, could be replaced by one line, namely

Set olApp = Outlook.Application

Note that I don't use the New keyword here, as I do in many of the other examples.

The ActiveExplorer object represents what would be showing if you switched over to Outlook. It may be the Inbox or Tasks, or just about anything. If Outlook is running when the procedure is run, ActiveExplorer will definitely be something. But if Outlook isn't running, there won't be an ActiveExplorer. I test for this condition using the Is and Nothing keywords. "Is" is like "=" but you use it when you are comparing two objects. Nothing is a special word that an object variable "Is" when it doesn't point to an object. That explanation stinks, but it's all I've got.

If ActiveExplorer is Nothing, then I use the .Add method of the Explorers collection to create one. And, using its argument, set the CurrentFolder property and activate the Explorer. If Outlook is running (meaning there is an ActiveExplorer) then I set its CurrentFolder property and use the .Display method to show it. The .CurrentFolder property may already be set to the folder I want, but there's no harm in setting again to be sure.

Prev Page: Retrieving E-Mail Top of This Page Next Page: Contacts

Home

Contact Me Visit Daily Dose of Excel - an Excel weblog