Resolve Exchange Details with Alias in Excel

Dear Experts,

I have a list in Excel with some of our company user's aliases. e.g. I log into COMPANY\user in windows, I have the user alias in excel in a column, userA, userB, userC, etc...

In column B, I'd like to resolve the full name from Outlook/Exchange, from the "Display Field" (double-click a name in Outlook and the full name of that person is in this field).

In column C, I'd like the field "Title"
In column D, I'd like the field "Office"

I have no idea where to start, I am hoping it would be possible through macros?

Many, Many thanks - This will save me a massive amount of work (I have 700 users in the list!)

Hendrik
hendrkleAsked:
Who is Participating?
 
Rory ArchibaldCommented:
It may be that you have an incorrect user name or headers in row 1? Try this version:
Sub GetADDetails()

   'On Error Resume Next
   Dim rngCell As Range
   
   Dim objConnection, objCommand, objRecordset, objRoot, strDomain
   Const ADS_SCOPE_SUBTREE = 2
   Set objRoot = GetObject("LDAP://rootDSE")
   'Work in the default domain
   strDomain = objRoot.Get("defaultNamingContext")
   
   Set objConnection = CreateObject("ADODB.Connection")
   Set objCommand = CreateObject("ADODB.Command")
   objConnection.Provider = "ADsDSOObject"
   objConnection.Open "Active Directory Provider"
   Set objCommand.ActiveConnection = objConnection
   
   objCommand.Properties("Page Size") = 1000
   objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
    For Each rngCell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        objCommand.CommandText = _
           "SELECT DisplayName, Title, physicalDeliveryOfficeName FROM 'LDAP://" & strDomain & _
               "' WHERE Name='" & rngCell.Value & "'"
        Set objRecordset = objCommand.Execute
    If not objrecrodset.BOF then
        objRecordset.MoveFirst
        With rngCell
            .Offset(0, 1).Value = objRecordset("DisplayName")
            .Offset(0, 2).Value = objRecordset("Title")
            .Offset(0, 3).Value = objRecordset("physicalDeliveryOfficeName")
        End With
        objRecordset.Close
   End If
    Next rngCell
   objConnection.Close
End Sub

Open in new window

0
 
Rory ArchibaldCommented:
Assuming you have Active Directory, it would be something like this:
Sub GetADDetails()

   'On Error Resume Next
   Dim rngCell As Range
   
   Dim objConnection, objCommand, objRecordset, objRoot, strDomain
   Const ADS_SCOPE_SUBTREE = 2
   Set objRoot = GetObject("LDAP://rootDSE")
   'Work in the default domain
   strDomain = objRoot.Get("defaultNamingContext")
   
   Set objConnection = CreateObject("ADODB.Connection")
   Set objCommand = CreateObject("ADODB.Command")
   objConnection.Provider = "ADsDSOObject"
   objConnection.Open "Active Directory Provider"
   Set objCommand.ActiveConnection = objConnection
   
   objCommand.Properties("Page Size") = 1000
   objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
    For Each rngCell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        objCommand.CommandText = _
           "SELECT DisplayName, Title, physicalDeliveryOfficeName FROM 'LDAP://" & strDomain & _
               "' WHERE Name='" & rngCell.Value & "'"
        Set objRecordset = objCommand.Execute
        objRecordset.MoveFirst
        With rngCell
            .Offset(0, 1).Value = objRecordset("DisplayName")
            .Offset(0, 2).Value = objRecordset("Title")
            .Offset(0, 3).Value = objRecordset("physicalDeliveryOfficeName")
        End With
        objRecordset.Close
    Next rngCell
   objConnection.Close
End Sub

Open in new window

0
 
hendrkleAuthor Commented:
Thanks Rorya,

When running your code, I get the following error:

"Run-time error 3021 - Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

My company does have active directory but I don't have any special permissions or anything, just the average user..

Any ideas?

Thanks!
Hendrik
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
hendrkleAuthor Commented:
Thanks,

I now get error:

"Run-time error 424: Object Required". When I click Debug, this part of the code is marked: "If Not objrecrodset.BOF Then"

For testing purposes I have reduced the list to just two users, for which I know for a fact that the alias is correct.

Your help is much appreciated Rorya.
Hendrik
0
 
Rory ArchibaldCommented:
Sorry typo:
If Not objrecordset.BOF Then
0
 
hendrkleAuthor Commented:
Thanks - getting closer,

Now the result returned is only a value in column B (my users are in column A). For example, my username "hkleine" in column A, returns "HKLEINE$" in column B, where there should be my full name, followed by title and office in columns C and D.

Also, many of the users are left blank in column B, I've done a check and they do exist in the outlook directory.

Any further suggestions to make this work?

Thanks Again Rorya!
0
 
Rory ArchibaldCommented:
I'm afraid AD is not really my area but it would appear that your Outlook contacts are not stored in AD or you have a more complicated AD setup than a single domain, and I can't assist you with either of those.
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.