How to get Outlook Mail ID Properties to Excel - Challenging Problem

[raghuram.star]

Hi,

I wish to extract outlook mail id properties to excel.

My problem is to find out the Name of the Manager with the Given Employee ID of an Subordinate
And this info is available in Outlook Email ID Properties.

For Example...
In a company, Each employee got an Employee ID as well an Email ID (both are linked in outlook)
&
Each employee got an Manager

Now I'm doing this manually, which is taking whole lot of time...

How I get the required info:
First I enter Employee ID in "To" address bar, then I get his email ID, then I double click on the email ID of employee in outlook, then I see his Employee ID in First Tab and his concern manager Name will be in second Tab.

Kindly let me know if you need any more information....

I have searched many websites but no where i could not find any help on this.... Thought it as almost impossible, but still have small hope, there will be some one who can crack this problem and help me.....

Please... Please help me....

Thanks a million in advance...

Attaching sample file with screen shots for your reference

Sample_File.xls

 

[Kyle123]

With regards to the speed issue, I would have thought that it would be more efficient to dispense with outlook altogether. The address book you are pulling data from is in turn pulled from the Active Directory - you can query this directly therefore cutting out Outlook and any potential performance overhead of the Outlook object.

Likewise we don't use managers/reports in our org so I can't test this but something like:

Sub testLookup()

PrintMemberOf Environ("USERNAME")


End Sub


Public Sub PrintMemberOf(samAccountName As String)


Dim sDomain     As String
Dim user        As Collection
Dim manager     As Collection
Dim x           As Long


'Get the Domain from the Current logged on user
With CreateObject("ADSystemInfo")
    sDomain = .DomainShortName
End With




'Get Info from username
Set user = GetIDName(GetDN(samAccountName, sDomain))


'Get Manager's username and Name
Set manager = GetIDName(user("ManagerDN"))


'Print The results
Debug.Print "User ID - " & user("UserID")
Debug.Print "User Name - " & user("Name")
Debug.Print "Manager ID - " & manager("UserID")
Debug.Print "Manager Name - " & manager("Name")




End Sub


Public Function GetIDName(strDN As String) As Collection
'Function to return the memberof property
Dim info As New Collection


    With GetObject("LDAP://" & strDN)
        info.Add .samAccountName, "UserID"
        info.Add .cn, "Name"
        info.Add .manager, "ManagerDN"
    End With
    
    Set GetIDName = info
End Function


Function GetDN(ByVal samAccountName, ByVal sDomain)
'Function to return the DN from a given samAccountName and Domain
    With CreateObject("NameTranslate")
        .Init 1, sDomain
        .set 3, sDomain & "\" & samAccountName
        GetDN = .GET(1)
    End With


End Function

 

[Madhusudhanan S]

It WORKED!!!! Thank you Simon, you literally made a part of my work life easy.... thank you so much...

With your help I have this code to get all required fields...

Sub test()
   Dim outApp As Object 'Application
   Dim outTI As Object 'TaskItem
   Dim outRec As Object 'Recipient
   Dim outAL As Object 'AddressList


    Set outApp = GetObject(, "Outlook.Application")
    Set outAL = outApp.Session.AddressLists.Item("Global Address List")
    Set outTI = outApp.CreateItem(3)
   
    outTI.Assign
   
    Set outRec = outTI.Recipients.Add("EMP20010")
    outRec.Resolve
    If outRec.Resolved Then
        MsgBox outRec.AddressEntry.Name
        MsgBox outRec.AddressEntry.Manager.Name
        MsgBox outAL.AddressEntries(outRec.AddressEntry.Manager.Name).GetExchangeUser.Alias
    Else
        MsgBox "Couldn't find Employee"
    End If
End Sub

Once Again THANK YOU SO MUCH - SIMON!!!!!!

Thanks a lot for this code . Require a small help on this code. I have email ids of 3000 users in column "A" and i would need to traverse through the columns and would provide the Outlook office Location against the user email id's . Please find attached the image for more clarity. Please help.