Link to home
Start Free TrialLog in
Avatar of retspanguy
retspanguy

asked on

How to export total number of mailboxes to Excel from Active Directory 2003?

Hello,

I am having a hard time finding a script to export the total number of users attached to mailboxes.  Ideally, I would like to export this list to an Excel spreadsheet.  I found the following script that allows me to export users and their email addresses, but I am not sure how to tweak it so that it will export user mailboxes only (or if it can be even used for such a task).

Dim objGroup, objExcel, iRow, strUser
Set objGroup = GetObject("LDAP://CN=,OU=,DC=,DC=,DC=")

On Error Resume Next
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
   irow=irow + 1
 Next
 .Columns(2).entirecolumn.autofit
End With

Set objExcel = Nothing
Set objGroup = Nothing

Any help will be greatly appreciated!

-R
Avatar of Paka
Paka

What do you mean by "export user mailboxes only"?
Avatar of retspanguy

ASKER

Sorry for the confusion.  I meant export a list of users in excel that are attached to mailboxes...no distribution lists, etc...just the users that have a mailbox.
ASKER CERTIFIED SOLUTION
Avatar of abhaigh
abhaigh
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
oh yeah - it will give you all sorts of records - just delete the collumns you don't need
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
abhaigh: When I try to run this, I get the following:

C:\>csvde -f mailboxusers.txt -r (&(object=user)(mail=*))
(mail was unexpected at this time.

Paka: I ran this script on one of our DCs and it yielded only 10 users total.  Does this script only query users that are currently logged into the DC in which this script is being run?

Thanks for the help so far everyone :)
Actually, I was able to use csvde -f mailboxusers.txt -r (&(object=user)(mail=*)) but I enclosed everything before and after the parenthesis with quotes and it worked.
oh yeah - mea culpa - I forgot the quotes
Paka, is it possilbe to modify the dumpusers.vbs to include departmental data?