Solved

Getting Phone, Cost Center, Department from Outlook Address book

Posted on 2004-08-31
16
1,096 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
  • 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 119

Expert Comment

by:Rey Obrero
ID: 11949073
0
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now