We help IT Professionals succeed at work.

VBA/CDO - Access querying the Outlook Address Book

2,112 Views
Last Modified: 2013-12-20
Hey guys,

This is the first time I've been stuck enough to post for help. Momentus! I could really use some advice; I've been searching the internet for a long time with no progress.

SCENARIO: I'm working on a personnel managing database for a very large corporation. One part of it is, I need a script that -- given a location and job title -- updates the personnel table by adding new hires and deleting people no longer with the company. As a Microsoft shop, all systems involved are Office 2007; the database itself is entirely Access on a shared folder at the moment (I know, long story, don't ask). My solution was for Access to tap into the Outlook GAL (Global Address List) and query it using CDO.

PROBLEM: I was able to connect to the GAL just fine. I can pull down a list of all the names, or given my name, I can pull down most of my info. My first inclination from there was to use Filter.Fields.Add CdoPR_TITLE, "job title", which for all I know might work, but with over 200,000 entries in the GAL it froze out with MAPI TOO BIG. After some trial and error, I discovered that Field 18 is where the job title is located. Using my name as a filter, when I ask it for Field 18, it returns my accurate job title.

PROBLEM IN ONE SENTENCE: I can't figure out how to get the CDO filter to return a subset of the GAL based on the filter criteria of "Title" and "Location".


Public Sub updatePeople()
 
Dim CdoSession As MAPI.Session
Dim CdoAddressLists As MAPI.AddressLists
Dim CdoAddressList As MAPI.AddressList
Dim CdoAddressEntries As MAPI.AddressEntries
Dim CdoAddressEntry As MAPI.AddressEntry
Dim CdoAddressEntriesMembers As MAPI.AddressEntries
Dim CdoFilter As AddressEntryFilter
  
Set CdoSession = New MAPI.Session
  
CdoSession.Logon (fOSUserName) 'fOSUserName - Windows logon ID
  
Set CdoAddressLists = CdoSession.AddressLists
Set CdoAddressList = CdoAddressLists("Global Address List")
Set CdoAddressEntries = CdoAddressList.AddressEntries
Set CdoFilter = CdoAddressEntries.Filter
CdoFilter.Fields(18) = "Claim Processor" '<-- CANT GET THIS TO WORK
 
'CdoFilter.Name = "Robert" '<-- But this works
'CdoFilter.Fields.Add CdoPR_TITLE, "Claim Processor"'<-Freezes,MAPI too big
 
For i = 1 To CdoAddressEntries.Count 
 
     Set CdoAddressEntry = CdoAddressEntries.Item(i)
 
     Debug.Print (CdoAddressEntry.Name)
 
     Set CdoAddressEntry = Nothing
 
Next
 
CdoSession.Logoff
Set CdoAddressLists = Nothing
Set CdoAddressList = Nothing
Set CdoAddressEntries = Nothing
Set CdoFilter = Nothing
 
End Sub

Open in new window

Comment
Watch Question

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
If the GAL does not get updated all that often, then why not just pull it down into Access as a temp table on a daily basis?

Something roughly like this:
SELECT YourGAL.*
INTO tblGalTemp
FROM YourGal;

...Then do your filtering in Access.


Access should have no problem with 200,000 records.

JeffCoachman

Author

Commented:
Sorry, I haven't been able to get that to work. :-\

Author

Commented:
Ok, so pulling the entire GAL into ANYTHING doesn't work. Populating a simple listview with a couple fields, even starting from record 100,000 to the max 217,927 took about 4 hours (run overnight).

I need to restrict the number pulled; by job title, and by location. From the debug, here's the fields I need:

I: 18; Field ID: 974585886; Value: Claim Processor (<-- job title; always 18th, always 974585886)
I: 21; Field ID: 974716958; Value: GREAT WESTERN (<-- location; always 21st, always 974716958)

If anyone can tell me how to turn the hex field ID into something I can filter by, even a clue about translating the hex into something useful like a &H39FE001E format, I'll award the points.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Sorry, I am not sure how this works with the specific code you are using...

But In MS Access you can link to your Contacts in your personal folders, and this will become a linked table in Access, fully readable and searchable.

Again, you can import this data (Static) or link to it (dynamic).

You may have better luck if you added the MS Exchange Zone to this question...
Please click the "Request Attention" link/button for help with this.

JeffCoachman
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Spade86 ,

Congratulations!
;-)

You also have the option of selecting your own post as the Solution.
This way other members searching here for the same issue will benefit.
;-)

If you delete the Q the the entire Development community and the World at large would be deprived of this useful information.
(You don't really want to be responsible for downfall of civilization do you?)
:-(

To save the universe from this unspeakable fate, Please click the "Request Attention" link/button and make sure that your solution is Accepted, and the Q is PAQ'd (not deleted)


JeffCoachman

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.