Query AD for Phone Number and return E-mail address

[mac7attack]

Hello all,

Is there a way to query W2K Active Directory for a telephone number and
return the user's e-mail address in VBA? I generated a list of calls
daily into an Excel spreadsheet and I would like to send the user an
automated e-mail from that spreadsheet.

Any help will be appericated,

Thanks
Matt

 

[Jerold Schulman]

Hello all,

Is there a way to query W2K Active Directory for a telephone number and
return the user's e-mail address in VBA? I generated a list of calls
daily into an Excel spreadsheet and I would like to send the user an
automated e-mail from that spreadsheet.

Any help will be appericated,

Thanks
Matt

Yes.

I ran:
cscript //nologo c:\temp\temp.vbs "1 (770)475-3820"
and received:
1 (770)475-3820 (e-mail address removed)

c:\temp\temp.vbs contains:

On Error Resume Next
Dim objConnection, objCommand, objRootDSE, strDNSDomain
Dim strFilter, strQuery, objRecordSet, oArgs
Set oArgs = WScript.Arguments
Phone = oArgs(0)
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOOBject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"
strFilter = "(&(objectCategory=person)(objectClass=user)(telephoneNumber=" & Phone & "))"
strAttributes = "mail"
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 1
objCommand.Properties("Timeout") = 300
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
mail = objRecordSet.Fields("mail")
Wscript.Echo Phone & " " & mail
objRecordSet.MoveNext
Loop
objConnection.Close
Set objConnection = Nothing
Set objCommand = Nothing
Set objRootDSE = Nothing
Set objRecordSet = Nothing


See the example at tip 9843 » How can I use VBScript to return all the users in my domain?
in the 'Tips & Tricks' at http://www.jsifaq.com

Jerold Schulman
Windows Server MVP
JSI, Inc.
http://www.jsiinc.com
http://www.jsifaq.com

 

[mac7attack]

Works like a charm Jerold.
Is there a site that lists all the Active Directory variables for VB?
I would be nice to expand the script to give the user's name (first and
last) or display name too.

Thanks
Matt

 

[Richard Mueller]

Works like a charm Jerold.
Is there a site that lists all the Active Directory variables for VB?
I would be nice to expand the script to give the user's name (first and
last) or display name too.

Hi,

I document default W2k Server attributes in this link:

http://www.rlmueller.net/UserAttributes.htm

The first spreadsheet documents the attributes corresponding to fields in
the user properties dialog of ADUC. The second documents all attributes in
the schema. You can see which apply to objects of class user. First name is
givenName, last name is sn (for surname), display name is displayName.

 

[mac7attack]

Nice spreadsheets.
I have tried to convert the above code into Excel VBA but the mail
lookup sorta works. I am inputing a phone number that is present in two
accounts: a regular and a Admin account. The admin account does not
have a e-mail address. But when the script runs it reports the email
addres for the regular as the email for the admin account. My thought
is either something wrong with the variable declaration or assignment
of strmail = objRecordSet.fields("mail")

Thanks in advanced
Matt

Sub ADLookup()
On Error Resume Next
Dim objConnection, objCommand, objRootDSE, strDNSDomain
Dim strFilter, strQuery, objRecordSet, strBase, strAttributes
Dim strmail As String
Dim strCN As String
Dim strname As String
Dim Phone As String

Phone = InputBox(Prompt:="Enter Telephone Number", Title:="Telephone
Number")
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOOBject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"
strFilter =
"(&(objectCategory=person)(objectClass=user)(telephoneNumber=" & Phone
& "))"
strAttributes = "cn,givenname,sn,mail"
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 1
objCommand.Properties("Timeout") = 300
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF = True
'If objRecordSet.fields("mail") = Null Then
strmail = objRecordSet.fields("mail")
'End If
strCN = objRecordSet.fields("cn")
strname = objRecordSet.fields("sn") & " " &
objRecordSet.fields("givenname")
MsgBox Len(strmail) & "!"
If (Len(strmail) = Null) Or (Len(strmail) < 1) Then
MsgBox "mail is null"
Exit Do
End If
If strmail = Null Then
MsgBox "mail is null 2"
Exit Do
End If
MsgBox strname & " * " & strCN & " * " & strmail & " * " & Phone
objRecordSet.MoveNext
strmail = Null
MsgBox "?? " & strmail
Loop
objConnection.Close
Set objConnection = Nothing
Set objCommand = Nothing
Set objRootDSE = Nothing
Set objRecordSet = Nothing
End Sub

 

[Richard Mueller]

Hi,

I wouldn't use "On Error Resume Next" as it probably masks some problems. In
particular, if any attributes do not have values (like sn or givenName), an
error is raised when you attempt to concatenate a Null value with other
strings. I like to retrieve values with separate variables. Somehow, even if
the value returned is Null, the variable is a blank string, not a Null. For
example:

Dim strCN, strFirstName, strLastName, strMail

Do Until objRecordset.EOF = True
strCN = objRecordset.Fields("cn").Value
strFirstName = objRecordset.Fields("givenName").Value
strLastName = objRecordset.Fields("sn").Value
strMail = objRecordset.Fields("mail").Value
strName = strFirstName & " " & strLastName
Wscript.Echo strCN & ";" & strName & ";" & strMail
objRecordsetMoveNext
Loop

In some cases I have had to force the variable to be a blank when the value
is missing with something like:

strFirstName = objRecordset.Fields("givenName").Value & ""

but I have not found this necessary in VBScript. Also, as you noted, your
filter can return more than one record in the recordset, so you must be able
to handle this.