Solved

Getting Phone, Cost Center, Department from Outlook Address book

Posted on 2004-08-31
16
1,113 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

911 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

23 Experts available now in Live!

Get 1:1 Help Now