Querying Active Directory for a specific user

[Seth Schrock]

I have found the following thread which ADezii solved very nicely: ...Grab information from AD.... However, this just pulls for the current user. I want to be able to specify the user so that I can get their email address. From research that I have done online (ADSI Active Directory), you must use LDAP and not WinNT to be able to pull the EmailAddress property of the user. Again from looking online, it seems that I need to know the OU where the user is located to be able to do this. The problem is that our users are arranged through many different OUs and I can't predict where via code.

What I'm trying to do is get the email addresses for everyone in a specific security group. I had managed to get several pieces of information using the following code.

Public Sub UsersInGroup(GroupName As String)
Dim adGroup As ActiveDs.IADsGroup
Dim adMembers As IADsMembers
Dim adMember As IADs
Dim adNTUser As IADsUser

Set adGroup = GetObject("WinNT://MyDomain/" & GroupName)
Set adMembers = adGroup.Members

For Each adMember In adMembers

    Set adNTUser = GetObject("WinNT://MyDomain/" & adMember.Name & ",user")
    Debug.Print adMember.Name, adNTUser.FullName, adNTUser.IsAccountLocked, adNTUser.PasswordExpirationDate, adNTUser.EmailAddress
    
Next

End Sub

Everything in this works except for the adNTUser.EmailA ddress at the end of the Debug.Print line. So I just need to replace the WinNT string with an LDAP string that allows me to search for the user by username. I had tried

"LDAP://CN=" & adNTUser.FullName & ",CN=Users,DC=MyDomain,DC=com"

but I get an error saying "There is no such object on the server". I don't know much about LDAP and I have no clue what else to try.

[iam_clint]

I do believe you are looking for username and not FullName for the LDAP query.

[Seth Schrock]

Well, based on the solution ADezii posted in the thread that I referenced, the string that sysInfo.UserNam e uses the user's full name along with the other elements I listed. So while you are accurate that the property used is called UserName, it refers to the user's name and not the computer username that I'm looking for.

[Rabbit]

This is a VBScript that I use at work to grab AD info quickly given their first and last name. The part you'll be interested in is the filter that is used to find the correct AD object regardless of which OU they are in.

Option Explicit

Dim objRoot, strDomain, objConn, objComm, objRecordset
Dim sFilter, sAttribs, sDepth, sBase, sQuery

Set objRoot = GetObject("LDAP://RootDSE")
strDomain = objRoot.Get("DefaultNamingContext")
Set objConn = CreateObject("ADODB.Connection")
Set objComm = CreateObject("ADODB.Command")

sFilter = "(&(objectClass=person)(sn=" & InputBox("Enter Last Name") & ")(givenName=" & InputBox("Enter First Name") & "))"
sAttribs = "adsPath,objectCategory,physicalDeliveryOfficeName,telephoneNumber,streetAddress,manager,mail,sAMAccountName"
sDepth = "SubTree"
sBase = "<LDAP://" & strDomain & ">"
sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth

objConn.Open "Data Source=Active Directory Provider;Provider=ADsDSOObject"
Set objComm.ActiveConnection = objConn
objComm.Properties("Page Size") = 10000
objComm.CommandText = sQuery
Set objRecordset = objComm.Execute

Do Until objRecordset.EOF
	MsgBox objRecordset("adsPath") & vbCrLf & vbCrLf & "Account Name: " & Nz(objRecordset("sAMAccountName"), "") & vbCrLf & "Street Address: " & Nz(objRecordset("streetAddress"), "") & vbCrLf & "Office: " & Nz(objRecordset("physicalDeliveryOfficeName"), "") & vbCrLf & "Telephone Number: " & Nz(objRecordset("telephoneNumber"), "") & vbCrLf & "E-Mail: " & Nz(objRecordset("mail"), "") & vbCrLf & "Manager: " & Nz(objRecordset("manager"), "")
	objRecordset.MoveNext
Loop

Function Nz(strItem, strReturn)
	If IsNull(strItem) Then
		Nz = strReturn
	Else
		Nz = strItem
	End If
End Function

[Seth Schrock]

Can I filter based on the username? For some strange reason, I have access to the FullName property through WinNT, but not the FirstName and LastName properties. I get the username through the Member object.

[Rabbit]

You can filter by any field available in AD. I believe the username field is called sAMAccountName. Which may or may not include the domain as part of it. I can't remember of the top off my head.

[Seth Schrock]

It worked. Thanks Rabbit. Here is my completed code that allows me to specify the security group and then outputs the username and email address associated with the user:

Public Sub UsersInGroup(GroupName As String)
Dim adGroup As ActiveDs.IADsGroup
Dim adMembers As IADsMembers
Dim adMember As IADs
Dim adUser As IADsUser
Dim objConn As Object
Dim objComm As Object
Dim objRecordset As Object
Dim sFilter As String
Dim sAttribs As String
Dim sDepth As String
Dim sBase As String
Dim sQuery As String

Set objConn = CreateObject("ADODB.Connection")
Set objComm = CreateObject("ADODB.Command")
Set adGroup = GetObject("WinNT://ftc/" & GroupName)
Set adMembers = adGroup.Members

objConn.Open "Data Source=Active Directory Provider;Provider=ADsDSOObject"
Set objComm.ActiveConnection = objConn
objComm.Properties("Page Size") = 10000

sAttribs = "adsPath,objectCategory,physicalDeliveryOfficeName,telephoneNumber,streetAddress,manager,mail,sAMAccountName"
sDepth = "SubTree"
sBase = "<LDAP://DC=FTC,DC=com>"

For Each adMember In adMembers
    sFilter = "(&(objectClass=person)(sAMAccountName=" & adMember.Name & "))"
    sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth
    objComm.CommandText = sQuery
    Set objRecordset = objComm.Execute
     
    Debug.Print "Account Name: " & Nz(objRecordset("sAMAccountName"), ""), "E-Mail: " & Nz(objRecordset("mail"), "")
               
Next

Set objComm = Nothing
Set objConn = Nothing
Set adGroup = Nothing
Set adMembers = Nothing
Set objRecordset = Nothing

End Sub

[Rabbit]

No problem Seth. By the way, the sAttribs is a listing of fields to return. You don't need to return all the fields that I returned in my script if all you need is the email.

[Seth Schrock]

I had noticed that, but then forgot to look into it. Thanks again.

[Nauticalgent]

A little late, but from within the VBE, which Reference must be enabled to use this?

[Rabbit]

If you are referring to Seth's code, I'm not sure which reference he's using to get access to the ActiveDs objects. However, if you go to my original VBS code, with minor modifications that should work in VBA without needing an additional reference.