VBS Script to find email address from list of user ID's and export to xls

AKSO
AKSO used Ask the Experts™
on
Hi All,

I'm trying to find a VBS script to search AD for primary email addresses from a list of user ID's I have in an Excel spreadsheet and update the spreadsheet in another column or export to a seperate file. I thought that this would be an easy one to find but I have had no luck. Can anyone point me in the right direction, or maybe provide me with a smilar script I could modify?

Thanks,

AKSO
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
try this

from a dos command prompt, type

ldifde -l mail -f output.txt

This will export all the accounts and their email addresses in the file output.txt.

Manipulate that file.

There are a bunch of options for ldifde that you can get from here - http://support.microsoft.com/kb/237677

Ss

Author

Commented:
Hi sshah254,

Many thanks for your response but I was looking more for something in VBS. If all else fails I'll have a play with this thugh. We have over 50k accounts on our domain so I need to limit the results to the accounts that I need.

Thanks,

AKSO
Copy the script "as is" to a .vbs file. Create a file Users.txt containing the usernames in the same directory as the script. Run the script. Output file name will be OutPut.csv and will be created in the same folder as the script.
'Create a file Users.txt in the same folder as the script. Output filename is OutPut.csv and can be located in the same folder as the script.

On Error Resume Next
ReportLog = "OutPut.csv"
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objOut : Set objOut = objFSO.CreateTextFile(ReportLog)
objOut.WriteLine "UserName" & ";" & "Email Address" & ";" & "OU Path"

strUserList = "Users.txt"

Set objRootDSE = GetObject("LDAP://rootDSE")
strADsPath = "<LDAP://" & objRootDSE.Get("defaultNamingContext") & ">"
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOOBject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

arrSam = Split(objFSO.OpenTextFile(strUserList).ReadAll, vbNewLine)
For Each sam In arrSam

strFilter = "(&(objectCategory=person)(objectClass=user)(sAMAccountName=" & sam & "))"
strAttributes = "distinguishedName,sAMAccountName,displayName,mail"

strQuery = strADsPath & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 99999
objCommand.Properties("Timeout") = 300
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst

Do Until objRecordSet.EOF
	strDN = objRecordSet.Fields("distinguishedName")
	strSA = objRecordSet.Fields("sAMAccountName")
	strMail = objRecordSet.Fields("mail")
	objOut.WriteLine StrSA & ";" & strMail & ";" & strDN
	objRecordSet.MoveNext
Loop	
Next

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

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

Open the Output.csv file in MS Excel, Use Text to Column and choose delimited and in delimiters choose semicolon and then click on Finish. All data will be sorted column wise.

Author

Commented:
Hi Ashutoshsapre,

Thank you very much for providing the script and instructions, everything worked exactly as described, I now have my list of email addresses.

Sshah254,

Thanks again for your input, it's much appreciated.

Thanks,

AKSO
Anytime... thanks for the rating :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial