Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Searching Outlook 2007 Appointments by Using a Macro

Office 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn how to run a macro in Microsoft Office Outlook 2007 to list items on a calendar that occur within a specified date range and that contain a specified term in the subject.

Microsoft Office Outlook 2007 provides Instant Search that allows you to specify search criteria to find items in Outlook folders. Instant Search is available in the user interface to search the Mail, Calendar, Contacts, Tasks, and Notes folders. For example, when you expand the Instant Search Query Builder in the Calendar folder, you can fine-tune your search criteria by specifying the following: a string that an attachment, the subject, or the body of the calendar item contains; the start or end date; or the organizer of the calendar item. If you need to regularly produce a list of meetings that you have on a certain subject and distribute the list to other people, it might be helpful to have an Outlook macro available. This article shows how an Outlook macro can search for items in the default calendar folder, based on a specific date range and a specific term in the subject field of the calendar item.

NoteNote

Compared with Instant Search, which allows you to search only for the previous, current, or next day, week, or month, searching programmatically allows you to identify specific dates for the date range.

The macro in this article searches for appointment items in your default calendar that start and end within the next 30 days and that have the term "team" in the subject. The following Visual Basic for Applications (VBA) code sample shows the entire macro. Afterward, the remainder of this topic describes how the macro works.

Sub FindAppts()
    Dim daStart, daEnd As Date
    Dim oCalendar As Outlook.Folder
    Dim oItems As Outlook.Items
    Dim oItemsInDateRange As Outlook.Items
    Dim oFinalItems As Outlook.Items
    Dim oAppt As Outlook.AppointmentItem
    Dim strRestriction As String
    
    daStart = Format(Date, "mm/dd/yyyy hh:mm AMPM")
    daEnd = DateAdd("d", 30, daStart)
    daEnd = Format(daEnd, "mm/dd/yyyy hh:mm AMPM")
    Debug.Print "Start:", daStart
    Debug.Print "End:", daEnd
          
    ' Construct a filter for the next 30-day date range.
    strRestriction = "[Start] >= '" & daStart _
    & "' AND [End] <= '" & daEnd & "'"
    Debug.Print strRestriction
    
    Set oCalendar = Application.Session.GetDefaultFolder(olFolderCalendar)
    Set oItems = oCalendar.Items
    
    ' To include recurring appointments, sort by using the Start property.
    oItems.IncludeRecurrences = True
    oItems.Sort "[Start]"
    
    ' Restrict the Items collection for the 30-day date range.
    Set oItemsInDateRange = oItems.Restrict(strRestriction)
    
    ' Construct a filter for subjects that contain ”team”.
    Const PropTag  As String = "http://schemas.microsoft.com/mapi/proptag/"
    strRestriction = "@SQL=" & Chr(34) & PropTag _
        & "0x0037001E" & Chr(34) & " like '%team%'"

    ' Restrict the last set of filtered items for the subject.
    Set oFinalItems = oItemsInDateRange.Restrict(strRestriction)
    
    ' Sort and print the final results.
    oFinalItems.Sort "[Start]"
    For Each oAppt In oFinalItems
        Debug.Print oAppt.Start, oAppt.Subject
    Next
End Sub

To perform a search programmatically, the macro constructs and applies a filter. A filter basically specifies the criteria of a search in a set of items (for example, a search of default calendar items for an appointment that lasts for the entire day). A filter is always applied to a property of each item in the set of items. The AllDayEvent property of an AppointmentItem object in the Outlook object model specifies whether the appointment lasts for the entire day.

Depending on the nature of the criteria, you can specify the filter in either the Microsoft Jet query language syntax or the DAV Searching and Locating (DASL) syntax and apply the filter to the Items.Restrict method. The Restrict method will search a specified set of items (for example, a set of appointment items in the default calendar) and return a subset of items that match the search criteria. For more information about Jet and DASL filter support in the Outlook object model, see Filtering Items.

The macro in this article carries out the search by using two filters. The first filter, a Jet filter, searches all appointment items in the default calendar based on date-range criteria applied to the Start and End properties of an AppointmentItem. The second filter, a DASL filter, searches the set of appointment items returned by the first filter (which specified the date-range criteria). This second filter searches based on the subject criterion applied to the property ''http://schemas.microsoft.com/mapi/proptag/0x0037001E" of an appointment item.

To return a set of appointment items that start and end between the current date and 30 days from today, the macro does the following:

  1. The macro first builds the start and end points of the date range for the filter. It uses the VBA Date function to obtain the current date and assigns the start point to the Start property. The macro then uses the VBA DateAdd function to add 30 days to the current date and assigns the end point to the End property. For more information about VBA data functions, see The Built-In VBA Date Functions.

        daStart = Format(Date, "mm/dd/yyyy hh:mm AMPM")
        daEnd = DateAdd("d", 30, daStart)
        daEnd = Format(daEnd, "mm/dd/yyyy hh:mm AMPM")
    
  2. The macro then builds the first filter by using the Jet query language. Note that in Jet, property names are delimited by brackets ([]).

        strRestriction = "[Start] >= '" & daStart _
        & "' AND [End] <= '" & daEnd & "'"
    
  3. The macro obtains the set of appointment items in the default calendar specified by the current Outlook user profile.

        Set oCalendar = Application.Session.GetDefaultFolder(olFolderCalendar)
        Set oItems = oCalendar.Items
    
  4. To ensure that recurring appointment items are also included as individual appointment items in this set of items, the macro sets the IncludeRecurrences property of the Items collection, and then sorts the set of appointment items by their Start property.

        oItems.IncludeRecurrences = True
        oItems.Sort "[Start]"
    
  5. The macro applies the filter to the Items.Restrict method on the set of appointment items in the default calendar folder. The appointment items that meet the date-range criteria are returned in the collection oItemsInDateRange.

        Set oItemsInDateRange = oItems.Restrict(strRestriction)
    

Variations of the Date Filter

The following code shows the basic date filter discussed in the preceding section.

    daStart = Format(Date, "mm/dd/yyyy hh:mm AMPM")
    daEnd = DateAdd("d", 30, daStart)
    daEnd = Format(daEnd, "mm/dd/yyyy hh:mm AMPM")
    strRestriction = "[Start] >= '" & daStart _
    & "' AND [End] <= '" & daEnd & "'"

This filter returns appointments that fall strictly within the next 30 days, beginning no earlier than today and ending no later than 30 days from today. This type of appointment is identified as type 1 in the following appointment date-range diagram.

Figure 1. Types of appointments that fall within, overlap, or fall outside of the next 30-day date range

Date-range appointment types

If you want to include appointments that overlap the specified date range, you can use the following filter instead.

    daStart = Format(Date, "mm/dd/yyyy hh:mm AMPM")
    daEnd = DateAdd("d", 30, daStart)
    daEnd = Format(daEnd, "mm/dd/yyyy hh:mm AMPM")
    strRestriction = "[End] >= '" & daStart _
    & "' AND [Start] <= '" & daEnd & "'"

This filter returns appointments that are of one of the following types:

  • Appointments that begin on or after today and end within 30 days (type 1).

  • Appointments that begin before today and extend into the next 30 days or beyond (type 2 and type 4).

  • Appointments that begin on or after today and extend beyond the next 30 days (type 3).

NoteNote

Neither of the suggested two filters returns appointments that occur entirely outside of the next 30-day range (type 5 and type 6).

Specifying Seconds in a Date Filter

You should not specify seconds in a date/time string that is used as part of a filter. This is because Outlook evaluates dates according to the short date format, and time according to the time format without seconds. Specifying seconds in the date/time string, as in the following example, will cause the query to fail.

    daStart = Format(Date, "mm/dd/yyyy hh:mm:ss AMPM")
    daEnd = DateAdd("d", 30, daStart)
    daEnd = Format(daEnd, "mm/dd/yyyy hh:mm:ss AMPM")
    strRestriction = "[Start] >= '" & daStart _
    & "' AND [End] <= '" & daEnd & "'"

Based on the set of appointment items that occur in the next 30 days returned by the first filter, the macro builds a second filter to search for appointment items that have the term "team" in their subject line, as follows:

  1. The macro builds a filter by using DASL syntax, applying the filter to the property "http://schemas.microsoft.com/mapi/proptag/0x0037001E" to search for the term "team" as part of the subject. This property is a Messaging API (MAPI) named property that Outlook uses and is equivalent to the Subject property of the AppointmentItem object in the Outlook object model. For more information about MAPI named properties, see MAPI Named Properties.

        Const PropTag  As String = "http://schemas.microsoft.com/mapi/proptag/"
        strRestriction = "@SQL=" & Chr(34) & PropTag _
            & "0x0037001E" & Chr(34) & " like '%team%'"
    
  2. The macro applies the filter to the Items.Restrict method on the collection oItemsInDateRange returned from the first filter. The appointment items that further meet the subject-line criterion are returned in the collection oFinalItems.

        Set oFinalItems = oItemsInDateRange.Restrict(strRestriction)
    
  3. To list the final set of appointment items that satisfy the first and second filters, the macro sorts these items by the start time of the appointments, and then prints them in the Immediate window of the Visual Basic Editor.

        oFinalItems.Sort "[Start]"
        For Each oAppt In oFinalItems
            Debug.Print oAppt.Start, oAppt.Subject
    

The default macro security setting is Warnings for signed macros; all unsigned macros are disabled. This setting allows a macro to run without any security prompts if the macro is digitally signed by a trusted publisher. All unsigned macros are disabled without notification. Therefore, unless your code sample is signed by a trusted publisher, you will not be able to run the code sample with this security setting. For more information about trusted publishers, see Add, remove, or view a trusted publisher.

The code sample in this article is not digitally signed. Therefore, you should set macro security to the Warnings for all macros option to run just this code sample. After changing to this option, restart Outlook. The Warnings for all macros option initially disables all macros. The first time you attempt to run a macro or start the Visual Basic Editor, you will see the Microsoft Office Outlook Security Notice dialog box. Click Enable Macros.

Important noteImportant

Because the Warnings for signed macros; all unsigned macros are disabled security setting provides a higher level of security for Outlook than the Warnings for all macros setting, you should set your macro security to Warnings for signed macros; all unsigned macros are disabled after you run this code sample. Restart Outlook for this setting to take effect.

To change the macro security setting

  1. On the Tools menu, click Trust Center.

  2. Click Macro Security.

  3. Select Warnings for all macros.

  4. Click OK.

  5. Restart Outlook.

To run the code sample as an Outlook macro

  1. In Outlook 2007, press ALT+F11 to start the Visual Basic Editor.

  2. If this is the first time you have started the Visual Basic Editor in the current Outlook session, you will see the Microsoft Office Outlook Security Notice dialog box. Click Enable Macros.

  3. In the Project Explorer, expand the Project1 node.

  4. Expand the Microsoft Office Outlook Objects node.

  5. Double-click ThisOutlookSession.

  6. On the Tools menu, click References.

  7. In the References dialog box, ensure that Microsoft Outlook 12.0 Object Library and Visual Basic for Applications are selected.

  8. Click OK to close the References dialog box.

  9. To create the macro, copy and paste the preceding code sample into the Code window.

  10. Press F5 to run the macro.

  11. Close the Visual Basic Editor.

To reset macro security after running this sample

  1. On the Tools menu, click Trust Center.

  2. Click Macro Settings.

  3. Select Warnings for signed macros; all unsigned macros are disabled.

  4. Click OK.

  5. Restart Outlook.

You can use a macro to search Outlook items in a manner similar to Instant Search. In addition, you can further refine searches, such as explicitly specifying appointment dates, as shown in this article.

Angela Chu-Hatoun is a programmability writer for Outlook.

Show: