Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Getting Phone, Cost Center, Department from Outlook Address book

Posted on 2004-08-31
16
Medium Priority
?
1,211 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

721 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