How To Get Email from Active Directory

[bignate]

Okay so I know how to send an email using access but I wanted to know if anyone knows how to get the emails address that are in the active directory

Thanks

 

[pr2-eugin]

Explain what do you mean by...

....how to get the emails address that are in the active directory

 

[bignate]

In the active directory it has each user, the department each user works in and the email address of each user

 

[Galaxiom]

Active Directory information is returned using an LDAP query.

This page shows a function to return the user's name. It is easily adapted to returning other attributes.

 

[bignate]

Thanks I adapted it to show my email address, however I had trouble adapting it to show a departments email or even a list of all emails in the active directory

 

[bignate]

Even if there was a way to get email addresses from outlook that would be great, however the emails have to update as the outlook address book updates

By the way I tried changing the select statement to this to try and show the IT departments email or even every member of the IT Departments email. However ended up showing one person email in the department

CommandText = "SELECT mail FROM 'LDAP://" & DomainDN & "' WHERE department = 'Information Technology'"

 

[Galaxiom]

I tried changing the select statement to this to try and show the IT departments email or even every member of the IT Departments email. However ended up showing one person email in the department

CommandText = "SELECT mail FROM 'LDAP://" & DomainDN & "' WHERE department = 'Information Technology'"

Close. That should create a recordset with all the department members' emails. The way the function is structured only the first one would be returned.

What do you want to do with them?

If it is for a table then there are a couple of different ways. Open a recordset against the table and write them across with a loop. Or loop through the AD recordset and issue Insert commands to the database to put them in a table.

 

[bignate]

I want to fill out a combo box, I was hoping that it could directly fill out the combo box, im guessing I will need a loop to do this.

The design will be like this:
There is a combo box where users select a department. Once they have picked a department there will be another combobox that shows all the emails from that department and the user picks one. An email will then send to that person once they press a submit button.

The coding for displaying the emails from the correct department would be like this

If txtDepatment = "Information Technology" Then
txtEmails = CommandText = "SELECT mail FROM 'LDAP://" & DomainDN & "' WHERE department = 'Information Technology'"

Else If txtDepartment = "Art" Then
txtEmails = "SELECT mail FROM 'LDAP://" & DomainDN & "' WHERE department = 'Art'"

If I was to put the emails into the table first and then link the combobox to the table then I think I would have to query the table and use select statements and I don't really want to get into that.

 

[Galaxiom]

No need for a table. Populate the combo by setting the combo's Recordset property to the recordset returned by the LDAP query.

Change the function to return a recordset instead of a string. Disconnect the recordset in the function by setting its ActiveConnection property to Nothing.

Make the argument of the function the department name. (The declaration as Object is for LateBinding.)

Public Function GetEmails(ByVal DeptName As String) As Object

The command text becomes:

"SELECT mail FROM 'LDAP://" & DomainDN & "' WHERE department '=" & DeptName & "'"

Call the function from the form like this:

Set Me.comboname.Recordset = GetEmails(Me.txtDepartment)

Probably in the form's Current Event and/or AfterUpdate of the department textbox.

 

[Galaxiom]

BTW. Rather than calling the LDAP query each time, the recordsets for the various department emails could be stored in a Collection or (better still) a Dictionary object. The index would be the department name.

This would be set up to run the query and save the recordset to the Dictionary if it is not already saved, or return the saved one if it exists.

The advantage of the Dictionary over the collection is the ability to search the index.

 

[bignate]

Thank you for all your help. I am however doing one bit wrong and its making the function return a recordset. Could you help me with this bit

 

[Galaxiom]

The following function returns a disconnected ADODB Recordset holding the emails from the designated department.

Assuming the form has a textbox named Department populate the listbox like this

Set Me.listboxname.Recordset = DeptEmails(Me.Department)

If you want the name of the user too, just add it to the fields returned and adjust the listbox to display more fields.

Public Function DeptEmails(ByVal DeptName As String) As Object
 
Const adOpenStatic = 3
Const adUseClient = 3
 
Dim DomainDN As String
Dim ADConn As Object    'ADODB Connection
Dim rs As Object        'ADODB.Recordset
 
    DomainDN = CreateObject("[URL]ldap://rootDSE").Get("defaultNamingContext[/URL]")
 
    Set ADConn = CreateObject("ADODB.Connection")
        With ADConn
            .Provider = "ADsDSOObject"
            .Open "Active Directory Provider"
        End With
 
    Set rs = CreateObject("ADODB.Recordset")
 
        With rs
            .ActiveConnection = ADConn
            .Source = "SELECT mail FROM 'LDAP://" & DomainDN & "' WHERE objectCategory='User' AND department ='" & DeptName & "'"
            .CursorType = adOpenStatic
            .CursorLocation = adUseClient
            .Open
            .ActiveConnection = Nothing
        End With
 
    ADConn.Close
 
    Set DeptEmails = rs
 
    Set ADConn = Nothing
    Set rs = Nothing
 
End Function

 

[LadyDi]

I have a couple questions regarding the code provided here.

I just tried to add it to my database, but received an error message that it couldn't create the object. Is there a reference that needs to be added so that this will work?

Also, is it possible to use wild cards when the function is called? The reason I ask, is that I would like to pull all the e-mail addresses where the department starts with "US Field Service". In my company's address book, the service technicians all belong to a certain "service branch" which is included in the Department (i.e. US Field Service 1234). I want to pull e-mail addresses for all the service technicians regardless of branch. Is that possible?

 

[Galaxiom]

I I just tried to add it to my database, but received an error message that it couldn't create the object. Is there a reference that needs to be added so that this will work?

It uses Late Binding so references should not be needed. Which line throws the error?

Also, is it possible to use wild cards when the function is called?

LDAP has a non-standard syntax for wildcards. It uses equals where SQL would use Like.

whatever = "*foo*"

 

[LadyDi]

I'm getting an error that says "Active X can't create object". When I click on Debug, it highlights the line of code that reads:

DomainDN = CreateObject("ldap:\\rootDSE").Get("defaultNamingContext")

 

[pr2-eugin]

You need to declare/Dim DomainDN As Object, and you have to Set an object.

Dim DomainDN[B] [COLOR=Red]As Object[/COLOR][/B]
[COLOR=Red][B]Set[/B] [/COLOR]DomainDN = CreateObject("ldap:\\rootDSE").Get("defaultNamingContext")

 

[LadyDi]

I set the DomainDN as an Object instead of a string and I added the word Set in front of the "DomainDN =" line. However, I'm still getting the same error message and it is still highlighting the same line of code.

 

[pr2-eugin]

So lets try breaking it up.

Dim DomainDN As Object, someName As String

Set DomainDN = CreateObject("LDAP://RootDSE")
someName = DomainDN.Get("DefaultNamingContext")

 

[LadyDi]

I'm afraid, it still doesn't like the line that reads

Set DomainDN = CreateObject("ldap:\\rootDSE")

. I am still getting the same error message and it highlights this line of code.

 

[pr2-eugin]

Can you please use the code I have given you in Post#18? You are using Backward slashes. I have used Forward. Direction does matter !