Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select from Address Book in EXCEL (Outlook 2000 / EXCEL)

    Don't know if this is the right approach but I'm looking for VBA code which allows me to display the Outlook Address book and allow the user to select a user or distribution list from it.
    I need the selection returned to EXCEL.

    Erik Jan

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select from Address Book in EXCEL (Outlook 2000 / EXCEL)

    Perhaps the code in this Technet article will help? <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q213/7/12.ASP?>http://support.microsoft.com/support/kb/ar...s/Q213/7/12.ASP?</A>

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Select from Address Book in EXCEL (Outlook 2000 / EXCEL)

    I certainly don't know the easiest way to do this. If you use Insert Address... in Word (which was not on my menu by default, I had to use Tools Customize... to add it), there is a relatively simple but somewhat inflexible way to do this into a document. While you could hijack this feature from Excel, I'm not sure that the insertion process would work. In Word, there is a hidden AutoText entry that defines the address fields to be inserted into the document, and I don't think this concept can transfer over to Excel.

    Here is a shell of a procedure to display names from the Outlook address book(s). The hard part is yet to come, but this is how I think you could access the data programmatically.

    <pre>Sub GetAddress()
    'instantiate Outlook application
    Dim olApp As New Outlook.Application
    'retrieve AddressList collection from MAPI NameSpace
    Dim olNameSpace As NameSpace
    Set olNameSpace = olApp.GetNamespace("MAPI")
    Select Case olNameSpace.AddressLists.Count
    Case 0
    MsgBox "No Address Lists Found.", vbCritical
    GoTo olClose
    Case 1
    'good, easy
    Case Else
    MsgBox "Multiple Address Lists Found. Using the first one.", vbExclamation
    End Select
    'set object reference to the first AddressList
    Dim olAddList As AddressList
    Set olAddList = olNameSpace.AddressLists(1)
    If olAddList.Name = "Personal Address Book" Then
    'old style field names
    'retrieve names into an array? pop up a form?
    MsgBox "Able to access " & olAddList.AddressEntries.Count & " address entries!"
    Else
    'assume it is a contacts folder
    'retrieve names into an array? pop up a form?
    MsgBox "Able to access " & olAddList.AddressEntries.Count & " address entries!"
    End If
    'insert the desired information to the Sheet
    olClose:
    Set olAddList = Nothing
    Set olNameSpace = Nothing
    olApp.Quit
    Set olApp = Nothing
    End Sub</pre>

    I've noticed the quitting part is quite slow, so I hope someone else can suggest a faster or more direct route to the address lists.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •