Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Getting Phone, Cost Center, Department from Outlook Address book

Posted on 2004-08-31
16
Medium Priority
?
1,226 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 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 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

916 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