Lookup Outlook Contacts

[akcramblet]

I made an excel function that will return employee names stored in MS Outlook by referencing their employee ID numbers. The function works ok, but it's too slow. I'm sort of blathering my way through VBA and could use some ideas to speed things up. Any help is appreciated.

Here is what I came up with:

Function lookupContact(eeNumber As String)
Dim olApp As Outlook.Application
Dim olNs As Namespace
Dim Fldr As MAPIFolder
Dim olCi As ContactItem

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.Folders("Mailbox - Doe, John").Folders("Special Contacts")


For Each olCi In Fldr.Items
If olCi.OrganizationalIDNumber = eeNumber Then
lookupContact = olCi.FileAs
End If
Next olCi

Set olCi = Nothing
Set Fldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
End Function

 

[Damon Ostrander]

Hi akcramblet,

One thing that immediately "jumps out" as causing slow execution is that your search loop doesn't quit when it finds the desired ID number--it continues to search exhaustively through the entire list. Your loop should at least look like this:

For Each olCi In Fldr.Items
   If olCi.OrganizationalIDNumber = eeNumber Then
      lookupContact = olCi.FileAs
      Exit For
   End If
Next olCi

 

[akcramblet]

Excelent. You've increased the speed of my function by 100%. However, it's still much too slow. I have to work with several hundred employee numbers, and may need to reference all sorts of information. Makes me wonder how excel does it's lookup functions so fast.

Thanks for the help, and sorry I'm so green.

 

[Damon Ostrander]

Hi akcramblet,

Just a note about your wondering why Excel's lookups can be so fast. The main reason is that these lookups require that the data be in sorted order. This enables the lookups to do a binary tree search which only requires on average one additional test each time the table doubles in size.

I assumed your OrganizationalIDNumber values are in random order. If for some reason they are in ascending order (with respect to contact index number) then you could use a binary tree search too and vastly reduce the time required.

Damon