Microsoft KB Archive/201081: Difference between revisions
m (Text replacement - ">" to ">") |
m (Text replacement - """ to """) |
||
(One intermediate revision by the same user not shown) | |||
Line 68: | Line 68: | ||
* In your Visual Basic, or Visual Basic for Applications project, you need to add a reference to the Microsoft Outlook 9.0 Object Library. | * 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. | * 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 | * 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. | * 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. | ||
Line 105: | Line 105: | ||
' The filter string to search with | ' The filter string to search with | ||
sFilter = | sFilter = "[CompanyName] = 'Microsoft'" | ||
' Find the first item | ' Find the first item | ||
Line 112: | Line 112: | ||
' Loop through to find additional items | ' Loop through to find additional items | ||
Do While Not oItm Is Nothing | Do While Not oItm Is Nothing | ||
Debug.Print oItm.FullName & | Debug.Print oItm.FullName & " (" & oItm.CompanyName & ")" | ||
Set oItm = oItms.FindNext | Set oItm = oItms.FindNext | ||
Loop | Loop | ||
Line 140: | Line 140: | ||
' The filter string to search with | ' The filter string to search with | ||
sFilter = | sFilter = "[CompanyName] = 'Microsoft'" | ||
' Find all items that meet the search criteria | ' Find all items that meet the search criteria | ||
Line 147: | Line 147: | ||
' Loop through all of the found items | ' Loop through all of the found items | ||
For Each oItm In oResItems | For Each oItm In oResItems | ||
Debug.Print oItm.FullName & | Debug.Print oItm.FullName & " (" & oItm.CompanyName & ")" | ||
Next | Next | ||
Line 161: | Line 161: | ||
==== String (for Text fields) ==== | ==== String (for Text fields) ==== | ||
When searching Text fields, you can use either an apostrophe ('), or double quotation marks ( | 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''': | ||
<pre class="codesample">sFilter = | <pre class="codesample">sFilter = "[CompanyName] = 'Microsoft'" | ||
sFilter = | sFilter = "[CompanyName] = ""Microsoft""" | ||
sFilter = | sFilter = "[CompanyName] = " & Chr(34) & "Microsoft" & Chr(34) | ||
</pre> | </pre> | ||
<span id="Topic3b" class="bookmark"></span> | <span id="Topic3b" class="bookmark"></span> | ||
Line 174: | Line 174: | ||
<br /> | <br /> | ||
The following example creates a filter to find all contacts that have been modified after January 15, 1999 at 3:30 P.M. | The following example creates a filter to find all contacts that have been modified after January 15, 1999 at 3:30 P.M. | ||
<pre class="codesample">sFilter = | <pre class="codesample">sFilter = "[LastModificationTime] > '" & Format("1/15/99 3:30pm", "ddddd h:nn AMPM") & "'" | ||
</pre> | </pre> | ||
<span id="Topic3c" class="bookmark"></span> | <span id="Topic3c" class="bookmark"></span> | ||
Line 181: | Line 181: | ||
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:<br /> | 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:<br /> | ||
<pre class="codesample">sFilter = | <pre class="codesample">sFilter = "[Journal] = True" | ||
</pre> | </pre> | ||
'''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'''.<br /> | '''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'''.<br /> | ||
Line 189: | Line 189: | ||
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.<br /> | 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.<br /> | ||
<br /> | <br /> | ||
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 | 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.<br /> | ||
<br /> | <br /> | ||
'''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): | '''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."<br /> | ||
<br /> | <br /> | ||
Category strings are not case sensitive. <span id="Topic3e" class="bookmark"></span> | Category strings are not case sensitive. <span id="Topic3e" class="bookmark"></span> | ||
Line 198: | Line 198: | ||
You can search for Integer fields with, or without quotation marks as delimiters. The following filters will find contacts created with Outlook 2000:<br /> | You can search for Integer fields with, or without quotation marks as delimiters. The following filters will find contacts created with Outlook 2000:<br /> | ||
<pre class="codesample">sFilter = | <pre class="codesample">sFilter = "[OutlookInternalVersion] = 92711" | ||
</pre> | </pre> | ||
<pre class="codesample">sFilter = | <pre class="codesample">sFilter = "[OutlookInternalVersion] = '92711'" | ||
</pre> | </pre> | ||
<span id="Topic4" class="bookmark"></span> | <span id="Topic4" class="bookmark"></span> | ||
Line 208: | Line 208: | ||
<br /> | <br /> | ||
The following VBScript code sample illustrates syntax that uses variables as part of the filter. | The following VBScript code sample illustrates syntax that uses variables as part of the filter. | ||
<pre class="codesample">sFullName = | <pre class="codesample">sFullName = "John Smith" | ||
' This approach uses Chr(34) to delimit the value. | ' This approach uses Chr(34) to delimit the value. | ||
sFilter = | sFilter = "[FullName] = " & Chr(34) & sFullName & Chr(34) | ||
' This approach uses double quotation marks to delimit the value. | ' This approach uses double quotation marks to delimit the value. | ||
sFilter = | sFilter = "[FullName] = """ & sFullName & """" | ||
</pre> | </pre> | ||
<span id="Topic5" class="bookmark"></span> | <span id="Topic5" class="bookmark"></span> | ||
Line 224: | Line 224: | ||
<ul> | <ul> | ||
<li><p>'''OR''': The following code returns all contact items that have either Business or Personal as their category.</p> | <li><p>'''OR''': The following code returns all contact items that have either Business or Personal as their category.</p> | ||
<pre class="codesample">sFilter = | <pre class="codesample">sFilter = "[Categories] = 'Personal' Or [Categories] = 'Business'" | ||
</pre></li> | </pre></li> | ||
<li><p>'''AND''': The following code retrieves all personal contacts who work at Microsoft.</p> | <li><p>'''AND''': The following code retrieves all personal contacts who work at Microsoft.</p> | ||
<pre class="codesample">sFilter = | <pre class="codesample">sFilter = "[Categories] = 'Personal' And [CompanyName] = 'Microsoft'" | ||
</pre></li> | </pre></li> | ||
<li><p>'''NOT''': The following code retrieves all personal contacts who don't work at Microsoft.</p> | <li><p>'''NOT''': The following code retrieves all personal contacts who don't work at Microsoft.</p> | ||
<pre class="codesample">sFilter = | <pre class="codesample">sFilter = "[Categories] = 'Personal' And Not([CompanyName] = 'Microsoft')" | ||
</pre></li></ul> | </pre></li></ul> | ||
Line 236: | Line 236: | ||
=== Common Questions and Issues === | === Common Questions and Issues === | ||
If you are trying to use the | 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: | ||
<div class="indent"> | <div class="indent"> | ||
Line 244: | Line 244: | ||
</div> | </div> | ||
<ul> | <ul> | ||
<li>There is no way to perform a | <li>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.</li> | ||
<li><p>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:</p> | <li><p>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:</p> | ||
<pre class="codesample">sFilter = | <pre class="codesample">sFilter = "[LastName] > 'LZZZ' And [LastName] < 'N'" | ||
</pre></li></ul> | </pre></li></ul> | ||
Latest revision as of 12:47, 21 July 2020
Article ID: 201081
Article Last Modified on 11/23/2006
APPLIES TO
- Microsoft Outlook 2000 Standard Edition
This article was previously published under Q201081
SUMMARY
This article demonstrates how to use the Find and Restrict methods in the Microsoft Outlook object model. Both methods are used to programmatically retrieve items from a folder based on the value of Outlook fields. The following topics are discussed:
The Find and FindNext Methods
The Restrict Method
Using Data Types with Filters
Using Variables as Part of the Filter
Using Logical Operators as Part of the Filter
Common Questions and Issues
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:
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
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'"
REFERENCES
For additional information about available resources and answers to commonly asked questions about Microsoft Outlook 2000 solutions, please see the following article in the Microsoft Knowledge Base:
146636 OL2000: Questions About Custom Forms and Outlook Solutions
Additional query words: OutSol OutSol2000 script OL2K vbscript
Keywords: kbdtacode kbhowto kbprogramming KB201081