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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.