Email Address List

Have you ever wondered what all the email addresses in your organization are?  No, me neither, that’s what the Outlook Address Book is for, right?  But what if you have some Unix guys who want a text file with all the email addresses for some app?  So here’s my solution:
‘ ***************************************************************************************
‘ **
‘ **  Email_Addr.vbs – Version 1.0.0
‘ **    Written By   : Scott Heath
‘ **    Created On   : July 22, 2008
‘ **    Last Updated : July 22, 2008
‘ ** 
‘ **  Description
‘ **    This script retrieves all the user objects from AD
‘ **    with email addresses assigned and then writes them
‘ **    to a file called ’email_addr.dat"
‘ **
‘ **
‘ ***************************************************************************************
On Error Resume Next
‘ Set Constants
‘ ***************************************************************************************
Const ForWriting = 2
‘ Query AD for all user objects with email address and write them to a file
‘ ***************************************************************************************
Set ADConnection = CreateObject("ADODB.Connection")
Set LDAPQuery    = CreateObject("ADODB.Command")
Set UserList     = CreateObject("System.Collections.ArrayList")
Set FSO          = CreateObject("Scripting.FileSystemObject")
ADConnection.Provider = "ADsDSOObject"   
ADConnection.Open "Active Directory Provider"
Set  LDAPQuery.ActiveConnection = ADConnection
LDAPQuery.Properties("Page Size") = 10000
LDAPQuery.CommandText = "<
LDAP://DC=yourdomain,DC=com>;(&(objectCategory=User)(mail=*@*)" & _
        "(!userAccountControl:1.2.840.113556.1.4.803:=2));Name, Mail;Subtree" 
Set QueryResults = LDAPQuery.Execute
If err.Number = 0 Then
End If
Do Until QueryResults.EOF
  UserInfo = QueryResults("Name") & "|" & QueryResults("Mail") & "|"
  UserList.Add UserInfo
Set OutputFile = FSO.OpenTextFile("email_addr.dat", ForWriting, True)
For Each User in UserList
  OutputFile.WriteLine User
So this is a pretty simple script that uses the ActiveX Data Object (ADODB) to query Active Directory.  I use this in a lot of my scripts because Active Directory is typically your source of record for email addresses and user names (samAccountName) unless your use Groupwise or some such.  The request was to get output like this:
and to sort it alphabetically.  After I looked at the first run I noticed there were many peope who no longer worked for the company and even more objects without email addresses.  If we look at the query:
LDAPQuery.CommandText = "<LDAP://DC=yourdomain,DC=com>;(&(objectCategory=User)(mail=*@*)" & _
        "(!userAccountControl:1.2.840.113556.1.4.803:=2));Name, Mail;Subtree" 
 We are including only objects whose class is User and whose mail property matches the pattern *@*.  That gets rid of computer objects which reduces our data set.  Then it gets rid of user objects without email address or with invalid email addresses.  The second line starts with:
What the heck is that!  Well, props to the MS Scripting Guys for their excellent article on how to find disabled accounts (click here).  In a nutshell that crazy userAccountControl:1.2.840.113556.1.4.803:=2 is a bitwise operation just like:
If object.userAccountControl AND 2
This operation checks for the value 2 which indicates that the account has been disabled.  But wait!  Scott said he needed account that WEREN’T disabled.  That were the ! at the beginning of the line comes in.  Using the ! is the same as using NOT.  Then finally we tell the query to return the Name and Mail properties and to search the entire subtree.
The other cool feature is the use of the Systems.Collections.ArrayList.  This great object allows you to do fun stuff like Sort() and Reverese() your list with ease.
Then finally we write the data to the text file then we’re done!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s