Retrieve Outlook details based on alias name Excel VBA

0
votes

I have a list Employee IDs of all the employees in my organization. I want Excel VBA code to get details like first name, last name, designation contact# and department.

The alias name is the Employee ID. So the code should take the Employee ID as alias and search Outlook for the respective details as mentioned above.

I found a macro online and modified it to my requirement:

Sub tgr()

Dim appOL As Object
Dim oGAL As Object
Dim oContact As Object
Dim oUser As Object
Dim UserIndex As Long
Dim i As Long
Dim j As Integer

Set appOL = CreateObject("Outlook.Application")
Set oGAL = appOL.GetNamespace("MAPI").AddressLists("/Name of the Distribution List/").AddressEntries

On Error Resume Next

For j = 2 To Application.WorksheetFunction.CountA(Columns(1))

    For i = 1 To oGAL.Count

        Set oContact = oGAL.Item(i)

        If oContact.AddressEntryUserType = 0 Then

            Set oUser = oContact.GetExchangeUser

            If UCase(oUser.FirstName) = UCase(Range("A" & j).Value) And UCase(oUser.LastName) = UCase(Range("B" & j).Value) Then

                Range("c" & j).Value = oUser.Alias

                Range("D" & j).Value = oUser.JobTitle

                Range("E" & j).Value = oUser.Department

                Range("F" & j).Value = oUser.ManagerName

                i = oGAL.Count
            End If
        End If       
    Next i
Next j

Set oGAL = Nothing
Set oContact = Nothing
Set oUser = Nothing

End Sub

The code works but the issue is that it checks all the items in the addresslist everytime to search for each item. This is taking more time.

Is there a way to simplify it by searching broadly instead of looking at each item in the addresslist and comparing. Something like Addresslist.find. Well the propety find only works if searching within a contact folder for addresslist there is no FIND property.

2
excelvbaoutlook
Extract details from Global Address List or Contacts folder accessible from Outlook or AD? You need to specify how Employee ID is related in the Contacts/AD. We would be thankful if you have tried some coding to achieve. - PatricK
It is not clear where you need to search the contacts... - Eugene Astafiev
Thank you for your quick responses. Unfortunately i want the code to work at my work place where there is no external email access. So i cant get the code. All i want is a simple code to search the GAL using the alias name and when a result is found retrieve the first name, last name, Job Title, Department and Manager. I know is too much to ask for but i've been trying to find a way to do this. I had found few codes online but all of them search using all the other field apart from alias name. - Surya Tester
Going down the Outlook and GAL route as sugguested by PatricK then, it looks like there is a full sample code for your scenario over at vbaexpress.com/kb/getarticle.php?kb_id=222 - Alex Read

2 Answers

0
votes

Alias as in Windows login alias? Try Namespace.ResolveName - GAL provider will resolve based on the login alias.

0
votes

In the past, I have used both ADSI VBScripts or ADO+VBA within Excel to look these details up from the domain, rather than Outlook. An example being:

Dim adoConnection As ADODB.Connection
Set adoConnection = New ADODB.Connection
With adoConnection
    .Provider = "ADsDSOObject"
    .CursorLocation = adUseClient
    .Open "Active Directory Provider"
End With

Dim adoCommandText As String
adoCommandText = "<LDAP://DC=company, DC=co, DC=uk>" & _
"; (& (objectCategory=person) (mail=" & EmailAddress & ")); " & _
"sAMAccountName, cn, givenName; subtree")

Dim adoCommand As ADODB.Command
Dim adoReturnRecordset As ADODB.Recordset

Set adoCommand = New ADODB.Command
With adoCommand
    .ActiveConnection = adoConnection
    .CommandType = adCmdText
    .CommandText = adoCommandText

    Set adoReturnRecordset = .Execute
End With

' read the data returned by using ADQueryReturnRecordset.Fields(0) etc.

The https://msdn.microsoft.com/en-us/library/ms810638.aspx page can help to get you started with the ADO route if you're absolutely sure this needs doing from within VBA.

However, now we're in 2015, I would advise possibly looking towards powershell which can lookup details from Active Directory (& Exchange) as an alternative to using VBA. Is there any reason (1) you need to use VBA for this and (2) why you want to lookup these details from Outlook rather than AD/Exchange?