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?

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

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

Open in new window

RayneAuthor Commented:
Hello BlueDevilFan,

I am getting an error when its executes
RayneAuthor Commented:
i gave  a sample existing user id like "ssamy"
Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

David LeeCommented:
Did you edit the MY_DOMAIN constant at the top of the script?
RayneAuthor Commented:
yes I did to change it to my company..
David LeeCommented:
Then the domain you entered must not be correct.  I tested the code using my domain and it worked correctly.
RayneAuthor Commented:
thank you bluedevilfan :)
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.