outlook email from active directory

Hello All,

Is it possible via vba if a excel sheet has a list of outlook users in a column, then for each of those contacts, i can get their names and positions from active directory or outlook server?

Thanks
RayneAsked:
Who is Participating?
 
David LeeConnect With a Mentor Commented:
Assuming that by "outlook users" you mean a user name, then the answer is yes.  You can get the info from AD with something like this

Sub UpdateWorkbook()
    'On the next line, edit the path to your domain
    Const MY_DOMAIN = "LDAP://company.com"
    Dim excWks As Excel.Worksheet, adoCon As Object, adoRec As Object, lngRow As Long
    Set excWks = Application.ActiveSheet
    
    Set adoCon = CreateObject("ADODB.Connection")
    With adoCon
        .Provider = "ADsDSOObject"
        .CursorLocation = 3
        .Open "ADSI"
    End With
    
    For lngRow = 1 To excWks.UsedRange.Rows.Count
        Set adoRec = adoCon.Execute("SELECT sn,givenName,title FROM '" & MY_DOMAIN & "' Where objectClass='user' AND objectCategory='Person' AND samAccountName='" & excWks.Cells(lngRow, 1).Value & "'")
        If (Not adoRec.bof) And (Not adoRec.EOF) Then
            excWks.Cells(lngRow, 2) = adoRec.Fields("sn").Value
            excWks.Cells(lngRow, 3) = adoRec.Fields("givenName").Value
            excWks.Cells(lngRow, 4) = adoRec.Fields("title").Value
        End If
    Next

    adoRec.Close
    adoCon.Close
    Set adoRec = Nothing
    Set adoCon = Nothing
    Set excWks = Nothing
End Sub

Open in new window

0
 
RayneAuthor Commented:
Hello BlueDevilFan,

I am getting an error when its executes
error5.bmp
0
 
RayneAuthor Commented:
i gave  a sample existing user id like "ssamy"
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
David LeeCommented:
Did you edit the MY_DOMAIN constant at the top of the script?
0
 
RayneAuthor Commented:
yes I did to change it to my company..
0
 
David LeeCommented:
Then the domain you entered must not be correct.  I tested the code using my domain and it worked correctly.
0
 
RayneAuthor Commented:
thank you bluedevilfan :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.