Active Directory - Distribution List Script

Adjust as noted, assuming you want the smtp in Col2:

 For Each strUser in objGroup.Member      
    Set objUser =  GetObject("LDAP://" & strUser)
    .Cells(iRow,1) = objUser.CN

    for each email in objUser.proxyAddresses
         if lcase(left(email,4)) = "smtp" then .Cells(iRow,2) = mid(email,6)
   next

So it would look like this?

Dim objGroup, objExcel, iRow, strUser
Set objGroup = GetObject("LDAP://CN=myDistList,ou=Messaging,dc=myDomain,dc=com")

Set objExcel = CreateObject("Excel.Application")
With objExcel
  .SheetsInNewWorkbook = 1
  .Workbooks.Add
  .Visible = True
  .Worksheets.Item(1).Name = mid(objGroup.Name, instr(1,objGroup.Name,"=") + 1 ) 'set Worksheet name to that of the DL
  irow=1

 For Each strUser in objGroup.Member      
    Set objUser =  GetObject("LDAP://" & strUser)
    .Cells(iRow,1) = objUser.CN

    for each email in objUser.proxyAddresses
         if lcase(left(email,4)) = "smtp" then .Cells(iRow,2) = mid(email,6)
   next
 .Columns(1).entirecolumn.autofit
End With

Set objExcel = Nothing
Set objGroup = Nothing

I'm getting an error at line 20 -

line 20 = End With

Did I not modify the script right?

works like a charm ... Thanks!

One thing I noticed - it doesn't save the list ... it populates great, but what I want to do is setup monthly reporting and email the excel file. I have the script that emails the file but I'll need the script to save and close the excel doc ... is this easy to do?

Sorry to keep bothering you!
Brian

Oh, I saw you wanted it in Excel, so I went that route.  You can simply save the file - not sure off-hand how to automate that...
OR... you can change your output type to a text file and have it save with an XLS extension - which is how I usually do this....

So, you would have data in the format of

SmithT,TomSmith@myCompany.com

so that it would be CSV and convert easily when opened.

I can dig deeper for the auto-save of Excel, if that's what you'd prefer...

I made a couple of changes ... can't figure out how to add the spreadsheet headers ... I can handle the save manually ... the last change I want to make is to add "Display Name" and "Email Address" as the headers of the two columns. I bumped the irow down to 2 and changed CN to displayName ... can you tell me how to add the headers?


Dim objGroup, objExcel, iRow, strUser, strDL

strDL = inputBox("What is the name of the Distribution List?","Distribution List")

Set objGroup = GetObject("LDAP://CN=" & strDL & ",ou=Distribution Lists,ou=Accounts,dc=Corp,dc=Contoso,dc=Com")

Set objExcel = CreateObject("Excel.Application")
With objExcel
  .SheetsInNewWorkbook = 1
  .Workbooks.Add
  .Visible = True
  .Worksheets.Item(1).Name = mid(objGroup.Name, instr(1,objGroup.Name,"=") + 1 ) 'set Worksheet name to that of the DL
  irow=2
  For Each strUser in objGroup.Member      
     Set objUser =  GetObject("LDAP://" & strUser)
     .Cells(iRow,1) = objUser.displayName
    for each email in objUser.proxyAddresses
         if lcase(left(email,4)) = "smtp" then .Cells(iRow,2) = mid(email,6)
   next
   irow=irow + 1
 Next
 .Columns(2).entirecolumn.autofit
End With

Set objExcel = Nothing
Set objGroup = Nothing

just before your
irow=2 line...

Enter:
irow=2 'existing line
  .Cells(1,1) = "Display Name"    'new line
  .Cells(1,2) = "Email Address"   'new line
For Each strUser in objGroup.Member 'existing line

sweet ... I seriously appreciate you helping me with this task ...

Thanks again -

Glad to have helped. :)

hi,
I copied the code into notepad, edited the ldap string, saved as .vbs but I do not see a file.
the black screen flashed then disappears.  where does the output go or what am i doing wrong?
Thanks.