Solved

Getting Phone, Cost Center, Department from Outlook Address book

Posted on 2004-08-31
16
1,183 Views
Last Modified: 2012-06-27
I need to get the phone, cost center, and department from my company's Outlook Address book.  I recently got code from rockmuncher that gets the name and e-mail address but I don't know how to modify it to also give me the phone, cost center and department?

Here is the code:

Public Sub Get_OutlookUsers()
On Error GoTo Err_Get_OutlookUsers
  Dim myOlApp As Object, MyNameSpace As Object
  Dim myAddressList As Object, myAddressEntries As Object, myAddressEntry As Object
  Dim i As Long

  Set myOlApp = CreateObject("Outlook.Application")
  Set MyNameSpace = myOlApp.GetNamespace("MAPI")
  Set myAddressList = MyNameSpace.AddressLists("Global Address List")
  Set myAddressEntries = myAddressList.AddressEntries

  Application.SetOption "Confirm Action Queries", 0   'stops Access's warning message
  On Error Resume Next
  DoCmd.RunSQL "DELETE * FROM Commitment_Outlook"
  For i = 1 To myAddressEntries.Count
    Set myAddressEntry = myAddressList.AddressEntries(i)
    If myAddressEntry.DisplayType = olUser Then
      DoCmd.RunSQL "INSERT INTO Commitment_Outlook(olkName, olkAddress) SELECT '" & myAddressEntry.Name & _
                   "' AS olkName, '" & myAddressEntry.Address & "' as olkAddress"
    End If
  Next
  On Error GoTo Err_Get_OutlookUsers
  Application.SetOption "Confirm Action Queries", -1
Exit_Get_OutlookUsers:
    Exit Sub
Err_Get_OutlookUsers:
  LogEvt "Error Number " & Err.Number & vbNewLine & Err.Description, vbCritical, "System Error (COM-1)"  'for debug
  Resume Exit_Get_OutlookUsers
End Sub

0
Comment
Question by:schmir1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 7

Expert Comment

by:rockmuncher
ID: 11948498
Unfortunately you now need an LDAP query, but I'm pretty sure that the cn provided by the outlook addressentry object will not be sufficient to carry out the LDAP query!  Typically Microsoft, wouldn't you say?

Perhaps other entries on EE might help (as I don't even know how to spell LDAP!), although I would be inclined to repost this Q in Office or NT rather than Access

eg.
    http://www.experts-exchange.com/Operating_Systems/WinNT/Q_10064208.html?query=LDAP+query+outlook&clearTAFilter=true

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11949053
Hmm, you ought to be able to create a linked table to your global address list and append records from there into your table.

File->Get External Data->Link Tables, you should see an option in the "Files of type" selector to choose Exchange(). You'll then see the tables available. Linking to the global address list ought to give you all the fields you are looking for - easier than cycling through the collection :-)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11949073
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:rockmuncher
ID: 11949076
Well, wouldn't that tear the crotch out of your nighty!  Shane's post werks.  

 I would strongly recommend importing overnight rather than linking as the performance is (as always) abysmal.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11949088
Agreed - linking to Exchange.....is......S.....L..........O...................W................
:-)
0
 
LVL 7

Expert Comment

by:rockmuncher
ID: 11949097
Any idea which version of Access was first to support link to Exchange Shane?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11949123
Not sure which the first version was, but I did use A97 to link to Exchange 5.5 way back. It might have been supported in A95, but A95 is best forgotten :-)
0
 
LVL 7

Expert Comment

by:rockmuncher
ID: 11949128
All that long ago! How ould I have missed it in all those hours of searching for an elegant solution.  Sometimes you just have to hate Billy and his mates
0
 

Author Comment

by:schmir1
ID: 11969629
I copied the code below from the Microsoft web site.  It give me my personal contact list and I need my companies global address list (need name, phone, department, and e-mail address).  See the <--- line below:

Sub ImportContactsFromOutlook()
   Dim rst As DAO.Recordset
   Dim ol As New Outlook.Application
   Dim olns As Outlook.NameSpace
   Dim cf As Outlook.MAPIFolder
   Dim c As Outlook.ContactItem
   Dim objItems As Outlook.Items
   Dim Prop As Outlook.UserProperty
   Dim iNumContacts As Long
   Dim i As Long

   Set rst = CurrentDb.OpenRecordset("Commitment_Outlook")
   Set olns = ol.GetNamespace("MAPI")
   Set cf = olns.GetDefaultFolder(olFolderContacts)  '<---------------need the global address list???
   Set objItems = cf.Items
   iNumContacts = objItems.Count
   If iNumContacts <> 0 Then
      For i = 1 To iNumContacts
         If TypeName(objItems(i)) = "ContactItem" Then
            Set c = objItems(i)
            If Not IsNull(c.LastName) Or c.LastName <> "" Then
              rst.AddNew
              rst!FirstName = c.FirstName
              rst!LastName = c.LastName
              rst.Update
            End If
         End If
      Next i
      rst.Close
      MsgBox "Finished."
   Else
      MsgBox "No contacts to export."
   End If
End Sub

0
 

Author Comment

by:schmir1
ID: 12010204
Is there any way to make the code from my initial entry work?  Is the phone and department available from the "Global Address List"?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12010231
I have no idea if those fields are available from the global address list. It wouldn't be difficult to write a short code snippet to work with a link table though.
0
 

Author Comment

by:schmir1
ID: 12011365
Can you give me an example?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12011435
Sure - if you have a linked global address list, and you wanted to insert the fields you're using above plus phonenumber and department, you could replace all of your above code with just this line:

CurrentDb.Execute "INSERT INTO Commitment_Outlook (FirstName, LastName, Phonenumber, Department) SELECT [First], [Last], Phone, Department FROM [Global Address List] WHERE [Last] Is Not Null"
0
 

Author Comment

by:schmir1
ID: 12022746
Sound good now I've got a really stupid question.  How do I link to the global address list? I don't know what the file name is or where it is located.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 12022756
You don't need to - click File->Get External Data->Link Tables. One of the options in the drop-down should be "Exchange()". Select that and you'll be prompted for a profile, which will then show you all the folders and address lists available to that profile.
0
 

Author Comment

by:schmir1
ID: 12023108
That was so unbelievably easy.  Thanks.  Tomorrow I'm going to work on the query you gave me and then I should be done.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question