Fetch exchangeusermanager name and email error

Qadah

New Member
Joined
Sep 5, 2013
Messages
24
Hi,

I modified a code i found on this thread Modify Existing VBA Code

original thread use alias to fetch other contact info (email, name.. ) from outlook/GAL.

my name is to the same, but get name, title, department, manager and managers email form user email (didn't have to change much)

the code worked fine, till i added the lines to get manager's name and email, then I'm getting an error on those two lines (I am using office 2016 if that's important)

arrUsers(lngUser, 4) = .GetExchangeUserManager.Name
arrUsers(lngUser, 5) = .GetExchangeUserManager.PrimarySmtpAddress

VBA Code:
Sub GetExchangeUserDetailsFromAlias()

    Dim str As String
    Dim olApp As Object 'Outlook.Application
    Dim olNameSpace As Object 'Outlook.Namespace
    Dim olRecipient As Object 'Outlook.Recipient
    Dim oEU As Object 'Outlook.ExchangeUser
    Dim arrUsers() As String
    Dim lngUser As Long
    Dim rngAlias As Range, rngAliasList As Range
    Dim lngLastRow As Long

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0

    Set olNameSpace = olApp.GetNamespace("MAPI")

    With Worksheets("Unique missing from 2020")
        lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set rngAliasList = .Range("A2:A" & lngLastRow)
    End With
    ReDim arrUsers(1 To lngLastRow - 1, 1 To 5)

    With Range("B1:F1")
        .Value = Array("Name", "Department", "Job Title", "Manager", "Manager Email")
    End With
    
 
    For Each rngAlias In rngAliasList
        lngUser = lngUser + 1
        If Len(rngAlias.Value) > 0 Then
            str = rngAlias.Value
            Set olRecipient = olNameSpace.CreateRecipient(str)
            olRecipient.Resolve
            If olRecipient.Resolved Then
                    Set oEU = olRecipient.AddressEntry.GetExchangeUser
                    If Not (oEU Is Nothing) Then
                        With oEU
                            arrUsers(lngUser, 1) = .Name
                            arrUsers(lngUser, 2) = .department
                            arrUsers(lngUser, 3) = .JobTitle
                            arrUsers(lngUser, 4) = .GetExchangeUserManager.Name
                            arrUsers(lngUser, 5) = .GetExchangeUserManager.PrimarySmtpAddress
                        End With
                    End If
            End If
        End If
    Next rngAlias
    rngAliasList.Offset(, 1).Resize(, 5).Value = arrUsers
        
    Worksheets("Unique missing from 2020").Columns("A:F").EntireColumn.AutoFit

    Set olApp = Nothing 'Outlook.Application
    Set olNameSpace = Nothing 'Outlook.Namespace
    Set olRecipient = Nothing 'Outlook.Recipient
    Set oEU = Nothing 'Outlook.ExchangeUser
    If lngUser Then Erase arrUsers
    Set rngAlias = Nothing
    Set rngAliasList = Nothing

End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi Qadah

I have a few questions - When you say "I'm getting an error on those two lines" - what do you mean? It would help if you could let us know what error number you're getting and the corresponding error description. Also, I don't see how you'd be getting an error on both lines - the code will only ever break on one line and then stop. That is, unless you've manually 'pushed' VBA to ignore it and proceed to the next line (where it breaks again). Of do you mean by 'error' that it simply isn't producing the results you expected it to?

I might be wrong but I expect that this code only works on an exchange systems that uses Outlook as a 'client'. It will not, for example, work on my personal laptop because although I use Outlook, I am not an exchange user and so my contacts don't have a 'Manager' field/property. Can you confirm that you're trying to execute this code on an exchange system and that there are (for example) contacts in the Global Address List with Manager data assigned to it.
Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top