In this article, Rick Dobson discusses managing Outlook contact data from Access programmatically and searching through that contact data. He also takes a look at the Outlook Security Guard and explores a way to work around it.

During the early days of Access, contact management solutions used to be a very popular type of application to build. After the release of Outlook, the widespread availability of Outlook diminished the demand for contact management applications built with Access. However, the growing size and diversity of uses for contact resources may be re-opening opportunities for Access-based contact management applications. In addition, the Outlook Security Guard feature forces you to intervene when working with any VBA-automated tasks for contact item and mail item objects in Outlook. Copying contacts from Outlook to Access provides Access developers a familiar, powerful way to query contacts, and provides VBA developers with a way to avoid the intrusive Outlook Security Guard.

This article explores how to programmatically manage Outlook contact data from Access. In addition, you’ll learn one approach to working around the Outlook Security Guard for VBA tasks. I’ll also compare different approaches to searching through Outlook contact data, from programmatically examining data in Outlook to copying the data from Outlook to Access and querying the data in Access.

Listing Outlook contacts

Enumerating items from the Outlook Contacts folder requires a reference to the Microsoft Outlook object model. Use the Tools | References menu choice to add a reference to the Outlook object model library on your computer (for example, the Microsoft Outlook 10.0 Object Library if you have Office 2002 installed).

Armed with a reference to an Outlook object model, my sample code assigns values to three variables:

• The first variable (myOlApp) represents an Outlook application.
• The second variable (myNameSpace) denotes the MAPI namespace in Outlook. This variable exposes many Outlook object model elements, such as the items in the Contacts folder.
• The third variable (myContacts) is for the collection of items in the Contacts folder.

The following code from the ListFirstTenContacts procedure declares the three variables and assigns values to them (the procedure resides in Module1 of the sample database in the accompanying Download file):

Dim myOlApp As Outlook.Application

Dim myNameSpace As Outlook.NameSpace

Dim myContacts As Outlook.Items

Set myOlApp = New Outlook.Application

Set myNameSpace = myOlApp.GetNamespace("MAPI")

Set myContacts = myNameSpace. _

    GetDefaultFolder(olFolderContacts).Items

The Outlook object model has different types of items, such as contact items vs. mail items, each of which exposes different properties, methods, and events. Figure 1 shows the Object Browser for the Outlook ContactItem with its FirstName property selected. Email1Address is at the top of the Members of ‘ContactItem’ pane in the Object Browser. You can use the Object Browser to familiarize yourself with the Outlook object model. Don’t forget that highlighting a member and clicking the Help tool (?) opens a Help window that may include useful code samples illustrating how to process the selected member.

Figure 1

You can use a For…Each…Next loop to iterate through items in the Contacts folder. The Object Browser can show you the names of properties that you may care to process for each item in the folder. As an example, the following excerpt from the ListFirstTenContacts procedure prints the FirstName, LastName, and Email1Address property values for each of the first 10 items in the Contacts folder. After printing these property values to the Immediate window, the code stops iterating through items. Halting iteration as soon as you’re done can be critical to performance when you have more than a few items in the Contacts folder:

int1 = 1

For Each myItem In myContacts

    Debug.Print myItem.FirstName & " " & _

        myItem.LastName & " -- " & _

        myItem.Email1Address

    int1 = int1 + 1

    If int1 > 10 Then Exit For

Next myItem

Dealing with the Outlook Security Guard

If you press the F5 function key with ListFirstTenContacts selected, you’ll see the Outlook Security Guard feature in operation. Figure 2 shows the dialog box that pops up. There’s no way to turn this feature off from the Outlook UI or by programming the Outlook object model with VBA. The feature guards against both accessing the Outlook e-mail addresses and sending mail via Outlook. Microsoft introduced the Outlook Security Guard dialog box as a response to the threat from viruses that can send messages to everyone in your Contacts folder.

Figure 2

Users can reply to an Outlook Security Guard prompt in any of several ways. For example, they can just click Yes once for each contact item they want to process. Another option is to click the “Allow access for” check box and select a value of 1, 2, 5, or 10 minutes. This will allow a VBA program to run for the selected duration without the display of the Outlook Security Guard dialog box.

There are several workarounds to the Outlook Security Guard for legitimate VBA programs that need to manipulate guarded Outlook items. One of these is Express ClickYes, a free program that you can download from the Internet (www.express-soft.com/mailmate/clickyes.html). The program automatically clicks the Yes button for you when the Outlook Security Guard dialog box pops up. After downloading the program, run a simple setup program.

I like to drag or copy the ClickYes application program to the Startup folder of the current computer user. This causes the program to start whenever the user starts the computer. There are three elements to getting ClickYes to work automatically for your application.

Once Express ClickYes is running, you can manage it from your code. First, you must declare some Windows API functions at the top of a VBA module. Next, you need two procedures that invoke theses API functions to either resume or suspend Express ClickYes. Finally, you need to call the procedure for resuming Express ClickYes before manipulating guarded Outlook objects and suspend Express ClickYes after processing guarded objects.

The following code excerpt shows sample code for each of the three elements for automatically resuming and suspending Express ClickYes after it’s installed and started. The API declarations appear at the top of the listing. ResumeClickYes and SuspendClickYes are two VBA procedures that respectively resume and suspend Express ClickYes by invoking the API declarations. The concluding CallListFirstTenContacts procedure demonstrates the syntax for invoking ResumeClickYes and SuspendClickYes before and after running some code that manipulates guarded objects. By calling ResumeClickYes before invoking ListFirstTenContacts, the user no longer has to manually respond to the Outlook Security Guard dialog box. Therefore, your legitimate VBA programs for Outlook can run without the need for manual intervention from a user.

'Declare Windows' API functions

Private Declare Function RegisterWindowMessage _

        Lib "user32" Alias "RegisterWindowMessageA" _

        (ByVal lpString As String) As Long

Private Declare Function FindWindow Lib "user32" _

      Alias "FindWindowA" (ByVal lpClassName As Any, _

      ByVal lpWindowName As Any) As Long

Private Declare Function SendMessage Lib "user32" _

        Alias "SendMessageA" (ByVal hwnd As Long, _

        ByVal wMsg As Long, ByVal wParam As Long, _

        lParam As Any) As Long

Public Sub ResumeClickYes()

Dim wnd As Long

Dim uClickYes As Long

Dim Res As Long

uClickYes = RegisterWindowMessage _

    ("CLICKYES_SUSPEND_RESUME")

wnd = FindWindow("EXCLICKYES_WND", 0&)

Res = SendMessage(wnd, uClickYes, 1, 0)

End Sub

Public Sub SuspendClickYes()

Dim wnd As Long

Dim uClickYes As Long

Dim Res As Long

uClickYes = RegisterWindowMessage _

    ("CLICKYES_SUSPEND_RESUME")

wnd = FindWindow("EXCLICKYES_WND", 0&)

Res = SendMessage(wnd, uClickYes, 0, 0)

End Sub

Sub CallListFirstTenContacts()

'Turn ClickYes on

ResumeClickYes

ListFirstTenContacts

'Suspend ClickYes

SuspendClickYes

End Sub

Copy contacts to Access

When processing Outlook contact data from Access, it may be convenient to copy the items from the Contacts folder to an Access table. This allows you to process contact data directly from Access without having to learn the Outlook object model or incur the performance hit associated with invoking an automation session for Outlook. In addition, you gain the flexibility and power of performing queries for contact data with the Access feature set. Because Access is a database tool, its querying tools are richer than those in Outlook.

There are three main steps to copying Outlook contact data to Access. First, you need to create an Access table design to hold the data that you want to copy. Second, you need to open a recordset object that points at the table. You can use the recordset to add rows to the table. Third, you need to iterate through the Outlook Contacts folder items and add the property values for each item as column values for rows in the Access table.

One easy way to ensure that a table design has the columns and settings that you want is to program its design. While it’s often feasible to create tables manually with the Access Table Designer, writing a script for a table guarantees a precise table design and makes it easy to edit the design. You can always exactly re-create the table just by re-running the script.

The following excerpt from the CreateContactsTable procedure in Module1 of the sample database performs two tasks. First, it drops any previous version of the Contacts table. Second, it uses Jet SQL to specify a table, starting with a column that has an Autonumber data type and four subsequent columns for first name, last name, e-mail address, and the Categories property values for contact items. The Outlook Category property holds string values denoting membership in zero or more categories. If a category name appears, the contact item belongs to a category. The last example in this article demonstrates how to process Categories property values.

Set cnn1 = CurrentProject.Connection

cnn1.Execute ("DROP TABLE Contacts")

strSQL = "CREATE TABLE Contacts " & vbCr

strSQL = strSQL & _

    "(ContactID INTEGER IDENTITY(1,1) PRIMARY KEY, "

strSQL = strSQL & "Fname TEXT(50), "

strSQL = strSQL & "Lname TEXT(50), "

strSQL = strSQL & "Emailaddress TEXT(255), "

strSQL = strSQL & "Categories TEXT(255));"

cnn1.Execute (strSQL)

The following code excerpt from the CopyFromContactsFolder procedure in the sample database performs the first two steps for copying the items from the Contacts folder in Outlook to the Contacts table in an Access database. First, it calls the CreateContactsTable procedure to create a new empty table designed to hold selected property values from items in the Contacts folder. Then, it points a recordset object (rst1) at the table and assigns properties to the recordset that permit the insertion of new rows:

CreateContactsTable

Set rst1 = New ADODB.Recordset

With rst1

    .ActiveConnection = CurrentProject.Connection

    .CursorType = adOpenKeyset

    .LockType = adLockOptimistic

    .Open "Contacts"

End With

Copying the property values for contact items to the table is a simple process. Just iterate through the items in the Contacts folder, starting each iteration by invoking the AddNew method for the recordset object. Within the iteration for each item, assign the property values to recordset fields. Then, close an item’s iteration by invoking the Update method. Wrap the whole process within calls to ResumeClickYes and to SuspendClickYes to eliminate the need to reply manually to Outlook Security Guard dialog boxes:

ResumeClickYes

For Each myItem In myContacts

    With rst1

        .AddNew

            .Fields(1) = myItem.FirstName

            .Fields(2) = myItem.LastName

            .Fields(3) = myItem.Email1Address

            .Fields(4) = myItem.Categories

        .Update

    End With

Next

SuspendClickYes

Searching for exact matches

Once you can connect with Outlook contact items either in Outlook or in a copied version in an Access table, you’ll probably want to search the contacts for matches against some criteria. For example, you can search for all contacts that have the same last name, such as Smith.

There are at least three approaches to this task:

• First, you can iterate through the items in the Contacts folder. This technique is slow because you process all of the contact items even though you require only a subset of those items.
• Second, you can use the built-in Outlook Find and FindNext methods to extract just those contact items with property values, such as a LastName of Smith, that exactly match a criterion value. When you iterate through all the items, you can specify partial matches for property values instead of requiring an exact match for a whole property value.
• Third, you can use an Access query against a table to which you copied property values for contact items. This is the most flexible and powerful approach because you gain full power of the Access Query Designer for graphically specifying queries. Plus, as an Access developer, you’re familiar with techniques for designing queries against partial matches for column values. The final sample in this article describes the design and syntax for this type of query.

The IterateToSearch procedure in Module1 of the sample database demonstrates that all this solution takes is an If statement within a For…Each…Next loop to search for a match against a criterion value. In this case, my criterion is a string value (strLastName) passed to the procedure. The critical loop appears in the following excerpt from the procedure. Of course, this simple loop requires a reference to Outlook and to have the myContacts variable pointing at the items in the Outlook Contacts folder. In addition, you need to handle the Outlook Security Guard dialog box. While the condition for the If statement specifies an exact match of the LastName property value, you can modify the sample to check for a match against any part of any property value.

For Each myItem In myContacts

    If myItem.LastName = strLastName Then

        Debug.Print myItem.FirstName & _

            " " & myItem.LastName & _

            " -- " & myItem.Email1Address

    End If

Next myItem

The FindToSearch procedure in Module1 of the article’s sample database illustrates the syntax for using the Find and FindNext methods to return contact items whose property values exactly match a criterion. With the Find method, you designate a criterion for the first Outlook item to match the criterion. Then, you can use the FindNext method repeatedly to return additional Outlook items that match the criterion since the last invocation of the Find method or the FindNext method. When the FindNext method fails to return an item, no additional items match the criterion.

The following excerpt from FindToSearch shows the key code for searching with the Find and FindNext methods. As with IterateToSearch, strLastName is a passed argument to the procedure. Notice that both the Find and FindNext methods can return an Outlook item. The code within the Do loop prints selected property values for the returned item so long as the methods return an item. When the TypeName function returns Nothing for the returned item, looping ceases:

strFilter = "[LastName] = """ & strLastName & """"

Set myItem = myContacts.Find(strFilter)

Do Until TypeName(myItem) = "Nothing"

    Debug.Print myItem.FirstName & " " _

        & myItem.LastName & " -- " & _

        myItem.Email1Address

    Set myItem = myContacts.FindNext

Loop

The last approach this article demonstrates for searching Outlook contact items uses the Contacts table and a parameter query. The qryLastName parameter query returns all rows from the table that match the [strLastName] criterion value. The following code excerpt from the QueryToSearch procedure starts by pointing an ADO Command object at the qryLastName parameter query. Next, the code instantiates a parameter object for the query and assigns the strLastName value to the parameter. As with the preceding two procedures, strLastName is a passed argument to the procedure. The code excerpt concludes by populating the rst1 Recordset with the row set from executing the cmd1 Command and printing column values for returned rows:

Set cmd1 = New ADODB.Command

With cmd1

    .ActiveConnection = CurrentProject.Connection

    .CommandText = "qryLastName"

    .CommandType = adCmdStoredProc

End With

Set prm1 = cmd1.CreateParameter()

With prm1

    .Name = "[strLastName]"

    .Type = adVarChar

    .Size = 50

End With

cmd1.Parameters.Append prm1

prm1.Value = strLastName

Set rst1 = cmd1.Execute

Do Until rst1.EOF

    Debug.Print rst1(0) & ", " & _

        rst1("FName") & " " & _

        rst1("LName") & " -- " & _

        rst1("Emailaddress")

    rst1.MoveNext

Loop

All three of the preceding procedures perform an identical task. They search for contact items that match a last name value represented by strLastName. The next excerpt, from the CallIterateToSearch procedure, invokes the IterateToSearch procedure to search for all contact items with a LastName property value of Smith and times how long it takes to return all of the items matching the criterion. Because FindToSearch and QueryToSearch have the exact same format for calling them as does IterateToSearch, you can readily adapt the following procedure to call the other two search procedures.

dat1 = Now

str1 = "Smith"

IterateToSearch str1

dat2 = Now

Debug.Print "IterateToSearch took " & _

    DateDiff("s", dat1, dat2) & _

    " second(s)." & vbCr

Table 1 shows the average run times for each of the three procedures for an Outlook Contacts folder with slightly more than 2,600 contact items and 11 contact items with a last name of Smith. As you can see, there’s a decided performance advantage to performing the search with an Access table. While the IterateToSearch procedure yielded the slowest performance by a wide margin, it offers very tight integration with Outlook, substantial flexibility in the search criterion, and is easy to program. The best technique depends on the requirements of a specific development project, but now you know at least three approaches to this kind of task.

Table 1. Average run times for the three procedures.

Searching Categories

The Categories property for contact items is different from other properties. This property can contain one or more comma-delimited strings. Each string denotes membership in a single category, but any one Categories property value can contain multiple comma-delimited strings denoting membership in one or more groups. Of course, the property value can be empty if the item belongs to no groups. Figure 3 shows a fictitious contact item for a person named Nate Carney. He bought SPAMBlocker (a program to block spam), and I placed him in the SPAMBlocker category. In addition, Nate indicated that he prefers not to receive newsletters sent to site friends, such as guest book registrants. As a result, the Categories property value for Nate’s contact item is NoNewsletter, SPAMBlocker.

Figure 3

It’s easy to design an Access query for the Contacts table that returns rows for those who are willing to receive newsletter messages. Just specify a criterion based on an InStr function for the Categories column where the function returns the starting character for NoNewsletter. If the function returns a value of 0, the string NoNewsletter doesn’t appear in the Categories column value for a row, and the row is among those to receive a newsletter. The following Jet SQL statement shows the syntax for a query that returns just rows without NoNewsletter in their Categories column value:

SELECT ContactID, Fname, Lname, Emailaddress

FROM Contacts

WHERE ((InStr([Categories],"NoNewsletter"))=0)

Access is a powerful database manager that’s especially well suited for small and medium sized organizations as well as many non-profit organizations. Managing contacts with Access instead of, or in addition to, Outlook offers several significant advantages in terms of querying and custom application development. One reason custom application development is easier in Access is that VBA developers don’t have to account for the Outlook Security Guard when working with data in Access tables. Access also offers queries that are easier to specify and faster to run. In addition, Access developers are creative at teasing the maximum amount of functionality out of Access in many different development contexts. This substantial experience in the Access development community means that Access developers can cost-effectively implement the custom features that will allow organizations to gain the maximum advantage from one of their most critical assets: contact data.