Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Distribution List to XL (97:SR2)

    I would like to export my global distribution list in Outlook98 to XL via code and create some personal distribution lists from the export.

    Any suggestions to get me started would be appreciated.

    Thanks,
    John

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Distribution List to XL (97:SR2)

    John,

    I hesitate to post the following, but nobody has answered you and you wanted any suggestions so...

    The following is sample code from Microsoft that they provide with Office XP developer.
    A lot of their sample code is incomplete or even wrong.
    I do not have Outlook installed, so I cannot test the code.
    You should be able in run this code from Excel__which would open Outlook, extract your data to an array and then allow you to do something with it.
    I have tried to clean up the code a little and have added some notes in caps.
    '-------------------------------------------------------------------------------
    Sub GetOutlook()
    ' This procedure retrieves all contacts from the Outlook Contacts folder where there
    ' is a Business Address and places those items in an array called strContactsArray.


    Dim olApp As Outlook.Application
    Dim nspNameSpace As Outlook.NameSpace
    Dim fldContacts As Outlook.MAPIFolder
    Dim objContacts As Object
    Dim objContact As Object
    Dim intCntr As Integer
    Dim strZLS As String
    Dim strContactsArray() As String

    On Error GoTo GetAll_Err
    ' Initialize zero-length string variable used in the Restrict method argument.
    strZLS = ""
    ' Get reference to the Outlook Contacts folder.
    ' CREATES A NEW INSTANCE OF OUTLOOK
    Set olApp = New Outlook.Application
    Set nspNameSpace = olApp.GetNamespace("MAPI")
    Set fldContacts = nspNameSpace.GetDefaultFolder(olFolderContacts)
    Set objContacts = fldContacts.Items.Restrict("[BusinessAddress] <> '" & strZLS & "'")

    ' olApp.Visible = True 'THE DEFAULT IS USUALLY FALSE

    ' Resize the array to the number of Outlook Contacts.
    ReDim strContactsArray(objContacts.Count - 1)

    For Each objContact In objContacts
    'Add only entries that include a business address.
    strContactsArray(intCntr) = IIf(Len(objContact.FullName) > 0, _
    objContact.FullName & vbCrLf, "") & IIf(Len(objContact.CompanyName) > 0, _
    objContact.CompanyName & vbCrLf, "") & objContact.BusinessAddress
    intCntr = intCntr + 1
    Next 'objContact

    ' ADD ADDITIONAL CODE HERE TO PASTE THE ARRAY INTO EXCEL

    GetAll_Bye:
    Set objContact = Nothing
    Set objContacts = Nothing
    Set nspNameSpace = Nothing
    Set fldContacts = Nothing
    ' olApp.Quit 'YOUR CHOICE
    Set olApp = Nothing
    Exit Sub
    GetAll_Err:
    MsgBox Err.Description, vbOKOnly, "Error = " & Err.Number
    Resume GetAll_Bye
    End Sub
    -------------------------------------------------------------------------------

    Regards,

    Jim Cone
    San Francisco, CA
    jim.coneXXX@rcn.comXXX

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribution List to XL (97:SR2)

    After tweaking Jim's code I've narrowed it down to:

    Sub GetGlobalAddressList()
    Set ol = New Outlook.Application
    Set olns = ol.GetNamespace("MAPI")
    ' Set MyFolder to the default contacts folder.
    Set MyFolder = olns.GetDefaultFolder(olFolderContacts) This line returns the Contacts folder but should return the Global Address List.
    ' Get the number of items in the folder.
    NumItems = MyFolder.Items.Count
    ' Set MyItem to the collection of items in the folder.
    Set MyItems = MyFolder.Items
    ' Loop through all of the items in the folder.
    For I = 1 To NumItems
    MsgBox MyItems(I).FullName
    Next
    End Sub


    Any ideas as to what the correct code would be to obtain the address' off the global address list?

    Thanks,
    John

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Distribution List to XL (97:SR2)

    John,
    If you're using Exchange/Windows 2000, then the GAL is an Active Directory object and can be accessed via LDAP - something along the lines of:
    <pre>Set AddressListCont = GetObject("LDAP://Myserver/CN=All Global Address Lists,_
    CN=Address Lists Container,CN=MyOrg,CN=Microsoft Exchange,_
    CN=Services,CN=Configuration,DC=MyDomain,DC=com")
    </pre>

    As this is an Excel 97 question, I'll assume the Domain is not an AD one, so I think you have to use MAPI/CDO - something like:
    <pre>MAPI.Session.AddressLists("Global Address List")
    </pre>

    I would guess.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Distribution List to XL (97:SR2)

    [Edit - original post content deleted by poster. I hate being wrong.]
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Distribution List to XL (97:SR2)

    [Edited]
    John, I had one of those "Oops, I gave some bad advice" epiphanies last night even before reading Rory's post. The following code in OL 2000 gets to my own corporate GAL content into a spreadsheet. Remember to set a reference to the Outlook Object Library in the Excel VBE Tools, References.

    Sub GAL2Sheet()
    Dim objOLApp As Outlook.Application
    Dim nsNS As Outlook.NameSpace
    Dim objAddrLists As AddressLists
    Dim objGAL As AddressList
    Dim objAddEntry As AddressEntry
    Dim lngC As Long
    Application.ScreenUpdating = False
    Set objOLApp = CreateObject("Outlook.Application")
    Set nsNS = objOLApp.GetNamespace("MAPI")
    Set objAddrLists = nsNS.AddressLists
    ' see if we can find the GAL and if there set it - not necessary if it's for sure there
    For lngC = 1 To objAddrLists.Count
    If objAddrLists.Item(lngC).Name = "Global Address List" Then
    Set objGAL = objAddrLists.Item(lngC)
    End If
    Next lngC
    If objGAL Is Not Nothing Then
    ' list all the GAl entries; triggers the OL 2000 security warning
    For lngC = 1 To objGAL.AddressEntries.Count
    ThisWorkbook.Worksheets(1).Cells(lngC Mod Application.Rows.Count, _
    1 + Int(lngC / Application.Rows.Count)).Value = objGAL.AddressEntries(lngC).Address
    Next lngC
    End If
    Set objGAL = Nothing
    Set objAddrLists = Nothing
    Set nsNS = Nothing
    Set objOLApp = Nothing
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distribution List to XL (97:SR2)

    John,

    It worked great. Although I had to make one minor tweak to the 2nd For Statement:
    For lngCE = 1 To objGAL.AddressEntries.Count
    ThisWorkbook.Worksheets(1).Cells(lngC Mod Application.Rows.Count, _
    1 + Int(lngC / Application.Rows.Count)).Value = objGAL.AddressEntries(lngC).Address
    Next lngCE

    I was getting a warning of a memory issue most likely related to the 1st & 2nd For Statements.

    One other thing...within the GAL I have a number of distribution lists containing members. I've tried modifying the code to produce a member list of that group but have not made any headway.

    Thanks for you help.
    John

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Distribution List to XL (97:SR2)

    John, since it's getting a bit long I have attached the Excel Version in a text file. It runs fine, but I have not had time to test it carefully for completeness or accuracy of the listing, I leave that to you. (My bosses' boss is already on my case for getting some stuff out late in the day so he doesn't have enough time to review.) Also, read the in-code comments, especially about reading the contents of a Distribution List.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Distribution List to XL (97:SR2)

    John, I just learned something. Access has wizards to do this kind of importing; writing code isn't necessary. See 262918. From an Access table to an Excel table is a snap.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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