Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Outlook Distribution List to Excel (2003)

    I have an Excel file with names from various public distribution lists. The lists change periodically and I need to come up with a way to update the names in Excel.
    Does anyone have any good way to do so?

    Thanks,

    itconc

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

    Re: Outlook Distribution List to Excel (2003)

    See <!post=this thread,526120>this thread<!/post> - you can either import the text file produced as Legare explained, or you can expand on the code I posted there and write it directly to an Excel file. (I don't have time to do the Excel part right now, if you need help another Lounger can probably assist.)

    Edit - the name of the Distribution List is found in it's VBA '.Subject' Property.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Outlook Distribution List to Excel (2003)

    Here's an Outlook code approach using the default Contacts Folder. You have to set an Outlook VBE reference to Excel, as commented below, and it'll blow up if you have more than 256 Distribution Lists or more than 65,536 members in a Distribution List (until Excel 2007).

    Sub Distlist2Excel()
    ' set a Reference to Microsoft Excel 11.0 Object Library
    Dim appExcel As Excel.Application
    Dim wksTarget As Excel.Worksheet
    Dim itmsContacts As Outlook.Items
    Dim lngC As Long, lngDLMems As Long, lngCol As Long

    Set itmsContacts = Outlook.Session.GetDefaultFolder(olFolderContacts) .Items
    Set appExcel = New Excel.Application
    appExcel.Visible = True
    Set wksTarget = appExcel.Workbooks.Add.Worksheets(1)

    For lngC = 1 To itmsContacts.Count
    With itmsContacts(lngC)
    If .Class = olDistributionList Then
    lngCol = lngCol + 1
    wksTarget.Cells(1, lngCol).Value = .Subject
    For lngDLMems = 1 To .MemberCount
    wksTarget.Cells(lngDLMems + 2, lngCol).Value = .GetMember(lngDLMems).Address
    Next lngDLMems
    wksTarget.Columns(lngCol).AutoFit
    End If
    End With
    Next lngC
    wksTarget.Activate
    Set wksTarget = Nothing
    Set appExcel = Nothing
    Set itmsContacts = Nothing
    End Sub

    Routines to select an existing Excel File and save it can be added.
    -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
  •