OL2000: Using Find and Restrict to Retrieve Items (201081)

MORE INFORMATION

IMPORTANT: Before using the code in this article, please be aware that:

All of the Automation code examples in this article can be run from Visual Basic, or Visual Basic for Applications.
In your Visual Basic, or Visual Basic for Applications project, you need to add a reference to the Microsoft Outlook 9.0 Object Library.
You need to have a Contacts folder selected before running the code examples. It is recommended that you create a new, temporary Contacts folder for testing purposes and copy some of your contacts into the new folder. This way you can freely change any necessary fields to match the needs of the examples, later you can delete the folder.
All of the individual "sFilter" example lines of code can be plugged into the two example procedures, replacing the existing sFilter lines of code in those procedures.
The example procedures output information in the Immediate window of the Visual Basic Editor. You may want to customize this output if you are substituting other sFilter lines of code.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

The Find and FindNext Methods

The Find method is used to find a single item in a folder that matches certain field criteria.

If you need to find additional items matching the same criteria, you can use the FindNext method to search again, or you can repeatedly use the FindNext method to find all items that match the criteria. This provides the same functionality as using the Restrict method (discussed later in the article).

The following Automation example finds all contacts that work at Microsoft:

Sub FindContacts()
   Dim ol As Outlook.Application
   Dim oItms As Outlook.Items
   Dim sFilter As String
   Dim oItm As Outlook.ContactItem
   
   Set ol = New Outlook.Application

   ' Get the items in the currently selected folder
   Set oItms = ol.ActiveExplorer.CurrentFolder.Items

   ' The filter string to search with
   sFilter = "[CompanyName] = 'Microsoft'"
   
   ' Find the first item
   Set oItm = oItms.Find(sFilter)

   ' Loop through to find additional items
   Do While Not oItm Is Nothing
      Debug.Print oItm.FullName & " (" & oItm.CompanyName & ")"
      Set oItm = oItms.FindNext
   Loop
   
   Set oItm = Nothing
   Set oItms = Nothing
   Set ol = Nothing
End Sub

The Restrict Method

The Restrict method applies a filter to the Items collection, returning a new collection containing all items from the original collection that match the filter. This method is an alternative to using the Find and FindNext methods.

The following Automation example uses the Restrict method to apply a filter to the Contact items to find only those contacts who work at Microsoft. It functions exactly like the Find/FindNext example discussed earlier.

Sub RestrictContacts()
   Dim ol As Outlook.Application
   Dim oItms As Outlook.Items
   Dim oResItems As Outlook.Items
   Dim sFilter As String
   Dim oItm As Outlook.ContactItem
   
   Set ol = New Outlook.Application

   ' Get the items in the currently selected folder
   Set oItms = ol.ActiveExplorer.CurrentFolder.Items

   ' The filter string to search with
   sFilter = "[CompanyName] = 'Microsoft'"

   ' Find all items that meet the search criteria
   Set oResItems = oItms.Restrict(sFilter)

   ' Loop through all of the found items
   For Each oItm In oResItems
      Debug.Print oItm.FullName & " (" & oItm.CompanyName & ")"
   Next
      
   Set oItm = Nothing
   Set oItms = Nothing
   Set ol = Nothing
End Sub

Using Data Types with Filters

String (for Text fields)

When searching Text fields, you can use either an apostrophe ('), or double quotation marks (""), to delimit the values that are part of the filter. For example, all of the following lines function correctly when the field is of type String:

sFilter = "[CompanyName] = 'Microsoft'"

sFilter = "[CompanyName] = ""Microsoft"""

sFilter = "[CompanyName] = " & Chr(34) & "Microsoft" & Chr(34)

Date

Although dates and times are typically stored with a Date format, the Find and Restrict methods require that the date and time be converted to a string representation. To make sure that the date is formatted as Outlook expects, use the Format function.

The following example creates a filter to find all contacts that have been modified after January 15, 1999 at 3:30 P.M.

sFilter = "[LastModificationTime] > '" & Format("1/15/99 3:30pm", "ddddd h:nn AMPM") & "'"

Boolean operators

Booleans operators, TRUE/FALSE, YES/NO, ON/OFF, and so on, should not be converted to a string. For example, to determine whether journaling is enabled for contacts, you can use this filter:

sFilter = "[Journal] = True"

NOTE: If you use quotation marks as delimiters with Boolean fields, then an empty string will find items whose fields are False and all non-empty strings will find items whose fields are True.

Keywords (or Categories)

The Categories field is of type keywords, which is designed to hold multiple values. When accessing it programmatically, the Categories field behaves like a Text field, and the string must match exactly. Values in the text string are separated by a comma and a space.

This typically means that you cannot use the Find and Restrict methods on a keywords field if it contains more than one value. For example, if you have one contact in the "Business" category and one contact in the Business and "Social" categories, you cannot easily use the Find and Restrict methods to retrieve all items that are in the Business category. Instead, you can loop through all contacts in the folder and use the Instr function to test whether the string "Business" is contained within the entire keywords field.

NOTE: A possible exception is if you limit the Categories field to two, or a low number of values. Then you can use the Find and Restrict methods with the OR logical operator to retrieve all Business contacts. For example (in pseudocode): "Business" OR "Business, Personal" OR "Personal, Business."

Category strings are not case sensitive.

Integer

You can search for Integer fields with, or without quotation marks as delimiters. The following filters will find contacts created with Outlook 2000:

sFilter = "[OutlookInternalVersion] = 92711"

sFilter = "[OutlookInternalVersion] = '92711'"

Using Variables as Part of the Filter

As the Restrict Method example in the Outlook Visual Basic Help file (Vbaoutl9.chm) illustrates, you can use values from variables as part of the filter.

The following VBScript code sample illustrates syntax that uses variables as part of the filter.

sFullName = "John Smith"

' This approach uses Chr(34) to delimit the value.
sFilter = "[FullName] = " & Chr(34) & sFullName & Chr(34)

' This approach uses double quotation marks to delimit the value.
sFilter = "[FullName] = """ & sFullName & """"

Using Logical Operators as Part of the Filter

Logical operators that allowed are AND, OR, and NOT. The following are variations of the clause for the Restrict method so you can specify multiple criteria.

OR: The following code returns all contact items that have either Business or Personal as their category.
```
sFilter = "[Categories] = 'Personal' Or [Categories] = 'Business'"
					
```

AND: The following code retrieves all personal contacts who work at Microsoft.

sFilter = "[Categories] = 'Personal' And [CompanyName] = 'Microsoft'"

NOT: The following code retrieves all personal contacts who don't work at Microsoft.

sFilter = "[Categories] = 'Personal' And Not([CompanyName] = 'Microsoft')"

Common Questions and Issues

If you are trying to use the "Find or Restrict" method with user-defined fields, the fields must be defined in the folder. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

201438 OL2000: Working With User-defined Fields in Solutions

There is no way to perform a "contains" operation. For example, you cannot use Find or Restrict to search for items that have a particular word in the Subject field. Instead, loop through all of the items in the folder and use the InStr function to perform a search within a field.
You can use the Find and Restrict methods to search for items that begin within a certain range of characters. For example, to search for all contacts with a last name beginning with the letter M, use this filter:
```
sFilter = "[LastName] > 'LZZZ' And [LastName] < 'N'"
					
```