How to create list of email addresses from Outlook 2003 distribution list?

I have a distribution list. I want to open it up in contacts so I can see the email addresses for all the people.
If I open it up in a TO: field, I get the names in the Address Book; Smith, John for example rather than John.Smith@company.com.  
LVL 1
Alex CampbellAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
VBA procedure to retrieve email addresses for each member in the distribution group name passed.
OM Gang


Public Sub GetDistGroupAddys()
On Error GoTo Err_GetDistGroupAddys

    Dim olNS As NameSpace
    Dim olContactFolder As Folder
    Dim olDistListItem As DistListItem
    Dim olContactItem As ContactItem
    Dim olItems As Outlook.Items
    Dim i As Integer
    Dim strListName As String
   
        'prompt for distribution list name
    strListName = InputBox("Enter distribution list name")
    If strListName = "" Then GoTo Exit_GetDistGroupAddys
   
    Set olNS = Application.GetNamespace("MAPI")
    Set olContactFolder = olNS.GetDefaultFolder(olFolderContacts)
    Set olItems = olContactFolder.Items.Restrict("[MessageClass]='IPM.DistList'")
   
    For Each olDistListItem In olItems
        'Debug.Print olDistListItem.DLName
        If olDistListItem.DLName = strListName Then
            For i = 1 To olDistListItem.MemberCount
                Debug.Print olDistListItem.GetMember(i).Address
            Next i
        End If
    Next

Exit_GetDistGroupAddys:
    Set olDistListItem = Nothing
    Set olItems = Nothing
    Set olContactFolder = Nothing
    Set olNS = Nothing
    Exit Sub

Err_GetDistGroupAddys:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub GetDistGroupAddys of VBA Document ThisOutlookSession"
    Resume Exit_GetDistGroupAddys
   
End Sub
0
Alex CampbellAuthor Commented:
I got an error when I ran the macro.
Did I forget something?
I am not familiar with macros and just pasted it into the macro editor.

Thanks,
Alex
 Get this error.
0
omgangIT ManagerCommented:
Let me give it a shot in 2003.  I was using 2007 when I wrote it.
OM Gang
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

omgangIT ManagerCommented:
For Outlook 2003 change to

Dim olContactFolder As MAPIFolder

OM Gang
0
Alex CampbellAuthor Commented:
I made the change.

Where should I be when I run the macro?
I tried it while in Contacts, ran the macro. Didn't get any results.
I then tried it while in an Email, ran the macro. Same thing.
0
omgangIT ManagerCommented:
The results are being printed to the Immediate Window in the VBE.  While in the VBE, select Immediate Window from the View menu.
The next step is to determine how/where you want the results.  Then you can modify the procedure to direct the output accordingly.
OM Gang
0
Alex CampbellAuthor Commented:
I am not familiar with macros so I don't know how to modify the procedure.
I just need a list of the emails that are in the distribution list.

I looked at the Immediate Window and I just see the name of the distribution list not the names of the people in the list.
ImmediateWindow.gif
0
omgangIT ManagerCommented:
Alex972, is your distribution list populated with individual recipients or made up of other distribution lists?
OM Gang
0
Alex CampbellAuthor Commented:
individual recipients
0
omgangIT ManagerCommented:
Is there a scroll bar in the Immdiate Window?  Are you seeing the full output?  How many addresses are you expecting?  When I run the procedure in Outlook 2003 I get a full list of email addresses for each person in the distribution list.
OM Gang
0
omgangIT ManagerCommented:
How are you calling/running the procedure?  In Outlook 2003 you can select Customize from the Tools menu and then click the New... button on the Toolbars tab to create a new toolbar.  Once the new toolbar is displayed next to your dialogue box you can drag it up to your menu bar area.  Next, in the Customize dialogue box, select the Commands tab then select Macros from the Categories pane on the left.  In the right pane you should see the new procedure displayed as something like Project1.GetDistGroupAddys.  Drag that macro and drop it on your new toolbar.  Now you can run it at any time.  You should be prompted for the name of the distribution group.
OM Gang
0
Alex CampbellAuthor Commented:
Is there a scroll bar in the Immediate Window?  
No

Are you seeing the full output?  
No, just getting the distribution list name.

How many addresses are you expecting?
about 20

 When I run the procedure in Outlook 2003 I get a full list of email addresses for each person in the distribution list.
Could there be a restriction on running the macro?
I haven't received any error messages.

0
omgangIT ManagerCommented:
<Could there be a restriction on running the macro?>
No, not if you're getting any output to the Immediate Window.  Macros are enabled.  Please verify that your procedure looks exactly like what I have below.  Note that the first Debug statement is commented out and that statement DOES print the distribution list name to the Immediate Window IF the statement is uncommented.

Also, try adding
Debug.Print olDistListItem.MemberCount
immediately after this conditional statement
If olDistListItem.DLName = strListName Then

OM Gang


Public Sub GetDistGroupAddys()
On Error GoTo Err_GetDistGroupAddys

    Dim olNS As NameSpace
    Dim olContactFolder As MAPIFolder
    Dim olDistListItem As DistListItem
    Dim olContactItem As ContactItem
    Dim olItems As Outlook.Items
    Dim i As Integer
    Dim strListName As String
   
        'prompt for distribution list name
    strListName = InputBox("Enter distribution list name")
    If strListName = "" Then GoTo Exit_GetDistGroupAddys
   
    Set olNS = Application.GetNamespace("MAPI")
    Set olContactFolder = olNS.GetDefaultFolder(olFolderContacts)
    Set olItems = olContactFolder.Items.Restrict("[MessageClass]='IPM.DistList'")
   
    For Each olDistListItem In olItems
        'Debug.Print olDistListItem.DLName  
        If olDistListItem.DLName = strListName Then
            For i = 1 To olDistListItem.MemberCount
                Debug.Print olDistListItem.GetMember(i).Address
            Next i
        End If
    Next

Exit_GetDistGroupAddys:
    Set olDistListItem = Nothing
    Set olItems = Nothing
    Set olContactFolder = Nothing
    Set olNS = Nothing
    Exit Sub

Err_GetDistGroupAddys:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub GetDistGroupAddys of VBA Document ThisOutlookSession"
    Resume Exit_GetDistGroupAddys
   
End Sub
0
Alex CampbellAuthor Commented:
I put the Debug.print into the place and I got:
1
and the distribution list name.

I have take care of some other stuff this afternoon, but I should be back Friday AM.
It is now Thursday afternoon 2:30.

Thanks for all your work.

I will be happy to give you the points.
0
omgangIT ManagerCommented:
Alex972, is the distribution group in your Contacts folder or is it in the Global Address Book?
We'll get this figured out tomorrow.
OM Gang
0
Alex CampbellAuthor Commented:
I copied it from the Global Address Book into my contacts.

Thanks!
0
Alex CampbellAuthor Commented:
I'm back. Let's assume it is in the Global Address Book.
The one I am currently working with is in my contacts, but it is also in the global list.
In the future, I would only be working with ones from the Global Address Book.

Thanks for your patience.
0
omgangIT ManagerCommented:
The procedure I provided is for a local distribution group in your Contacts folder.  It will not work for distribution groups in the GAL.  A little curious as to why you are not getting results if the distribution group is in your Contacts but we need to change focus to something that will work for what you want.
I'll be back.  (did I sound like the Terminator?)
OM Gang
0
Alex CampbellAuthor Commented:
I was stunned by the similarity. Will be gone for a couple of hours.
0
omgangIT ManagerCommented:
OK, can't recall where I got this (I'm a slacker) but I'm thinking I found it here in EE quite some time ago.  It's a vbscript that will allow you to query the specified AD group members.  This includes AD distribution groups so it does what you want.  I've modified it to output the group members email address.  The output file is GroupMembers.txt and is output to the same directory the script is executed from.

You'll need to change the Set objGroup statement to point to the full AD path of the distribution group you want to query, so something like
Set objGroup = GetObject("LDAP://CN=ORAHQ-MIC,OU=Groups,OU=FDA,OU=HHS,DC=Fed,DC=ADS")
or something

Give it a shot.
OM Gang


' VBScript source code
Option Explicit

Dim arrNames()
Dim intSize, objGroup, strUser, objUser, strHolder, strName, i, j, strID, strResults
Dim objFSO, objOutputFile
intSize = 0
 
Set objGroup = GetObject("LDAP://CN=TheDistGroupName,OU=Groups,OU=Company,DC=Tree,DC=Domain,DC=Com")
 
If TypeName(objGroup.Member) = "Empty" Then
      WScript.Echo "There are no members in " & objGroup.Name
ElseIf TypeName(objGroup.Member) = "String" Then
      WScript.Echo objGroup.Member
Else
      For Each strUser in objGroup.Member
          Set objUser =  GetObject("LDAP://" & strUser)
        
          'ReDim Preserve arrNames(intSize)
          'arrNames(intSize) = objUser.CN
          'intSize = intSize + 1
         'strID = objUser.SamAccountName
         strID = objUser.Mail
         strResults = strResults & strID & ";"      

      Next
      
      'For i = (UBound(arrNames) - 1) to 0 Step -1
      '    For j= 0 to i
      '        If UCase(arrNames(j)) > UCase(arrNames(j+1)) Then
      '            strHolder = arrNames(j+1)
      '            arrNames(j+1) = arrNames(j)
      '            arrNames(j) = strHolder
      '        End If
      '    Next
      'Next
      
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objOutputFile = objFSO.CreateTextFile("GroupMembers.txt")
    objOutputFile.Write strResults
      
      'For Each strName in arrNames
      '    'Wscript.Echo strName
      '    objOutputFile.WriteLine strName
      'Next
      
    objOutputFile.Close
    Set objOutputFile = Nothing
    Set objFSO = Nothing
    Set objUser = Nothing
    Set objGroup = Nothing
   
End If
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alex CampbellAuthor Commented:
Sorry, but this is getting way too technical for what I need. I will give you the points, but I will keep looking for something simpler.
0
Alex CampbellAuthor Commented:
I am really looking for a much simpler method since I am not familiar with macros and VBA and I am only going to do this for one distribution list.
0
omgangIT ManagerCommented:
Alex972, the last code I posted is a vbs script not a VBA routine or Outlook macro.  Copy the script into notepad.  Save the file to your c:\temp directory and name is something like GetListMembers.vbs (not .txt).  All you need to do is change the AD path to the distribution group.  Simply double-click on the .vbs file and it will create a semicolon delimited text file of the email addresses for each member and save the list as GroupMembers.txt in the C:\temp directory.
OM Gang
0
Alex CampbellAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.