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.EmailAddress 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.
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
10 12749
I do believe you are looking for username and not FullName for the LDAP query.
Well, based on the solution ADezii posted in the thread that I referenced, the string that sysInfo.UserName 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.
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
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.
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.
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
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.
I had noticed that, but then forgot to look into it. Thanks again.
A little late, but from within the VBE, which Reference must be enabled to use this?
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Trey Mitchell |
last post by:
I have built a single class in vb.Net to handle all active directory
authentication needed in my web app. People can sign into the website with
no problem. There is also a second form of...
|
by: Brian Mitchell |
last post by:
Is there any way to return active directory objects from the object's guid?
My basic problem is that I don't know the appropriate search filters. Does
anyone know of any sites that list them? (like...
|
by: Gary |
last post by:
I'm creating an Infopath form and I need to query Active Directory to obtain
some data. Here is the code I'm using (obtained from sample code from MSDN)
Private Shared domainADsPath As String =...
|
by: hellosibba |
last post by:
i am trying to enumerate and query the organization Domain controller to get all the domains and its corresponding users in it.
i also want to query the user's information like full name,...
|
by: cpajoe2001 |
last post by:
I am not sure if this is the proper place to put this thread but I have run into a road block. I am writing a web app that will create groups in Active Directory then add users to those groups. I...
|
by: Brian McCullough |
last post by:
Hello,
I am trying to query ADAM using the ActiveDirectoryMembershipProvider in my
ASP.NET 2.0 application, but have been unsuccessful. I have followed the
steps in these blog posts, but still...
|
by: gdltec |
last post by:
I have an ASP page that needs to be populated with employee data from Active Directory (i.e. Name, email, manager, etc), can anyone help me out on how to acomplish this? I just need to know how to...
|
by: justintaitt |
last post by:
Hi,
I am new to active directory and am trying to query it for the user name and ID of members of a group to populate a drop down list in the form: firstname lastname (userID)
When I run the page...
|
by: jllg2000 |
last post by:
I got this query:
CREATE VIEW viewADContacts
AS
SELECT , SN , ST State
FROM OPENQUERY( ADSI2,
'SELECT Name, SN, ST
FROM ''LDAP://presidencia.local/CN=Users,...
|
by: VBDevo |
last post by:
Hello,
Can anyone help me in this please
I'm trying to restore an Active Directory deleted user, I found the code only in c++ in the MSDN, and it uses the DLL : wldap32.dll , which is under...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |