Return individual sender of archived mail for a shared mailbox

Question

I have written a macro which pulls the emails from the 'Sent Items' of a particular mailbox in Outlook. I am using .SenderName property to populate senders name in Excel.
The mailbox is shared by a team, and is labeled as 'ResearchHub'.

The macro is working when I pull out mails for recent days.

When I try to draw the archived mails, the .SenderName property pulls out the sender as 'ResearchHub', not the person who sent the mail.

MailItem.SentOnBehalfOfName property is pulling Research Hub as the sender and not the individual name.

I tried unarchiving the emails by opening them and copying them in a different folder, yet was unsuccessful in fetching the particular user as sender name.

Option Explicit
Sub test()

Dim Result As Object
Dim i As Integer
Dim dstart As Date
Dim dend As Date
Dim lower As String
Dim upper As String
Dim limit As String

dstart = InputBox("Enter Start Date in dd/mmm/yyyy format")
dend = InputBox("Enter End Date in dd/mmm/yyyy format")
lower = "[ReceivedTime] > '" & Format(dstart, "ddddd") & " 12:00 AM" & "'"
upper = "[ReceivedTime] > '" & Format(dend, "ddddd") & " 12:00 AM" & "'"

limit = lower & " AND " & upper

Dim objoutlook As outlook.Application
Dim oStore As outlook.Store
Dim onjNSpace As outlook.Namespace
Dim objFolder As outlook.Folder
Dim oAccount As Account

Set objoutlook = CreateObject("Outlook.Application")
Set objNSpace = objoutlook.GetNamespace("MAPI")

For Each objFolder In objNSpace.Folders

    If objFolder.Name = "Research Hub" Then

        Dim myfolder As outlook.Folder
        Set myfolder = objFolder.Folders("Sent Items")

        Dim objitem As Object
        Dim irow As Integer
        irow = 2
        Set Result = myfolder.Items.Restrict(limit)

        If Result.Couunt > 0 Then

            For i = 1 To Result.Count

                Cells(irow, 1) = objitem.SenderName
                Cells(irow, 2) = objitem.To
                Cells(irow, 3) = objitem.Subject

                irow = irow + 1

            Next i
        End If
    End If
Next

Set objoutlook = Nothing
Set objNSpace = Nothing
Set myfolder = Nothing

End Sub

@PEH I used MailItem.SentOnBehalfOfName property but I still is pulling Research Hub as the sender and not the individual name — prakhar gupta

Dmitry Streblechenko Dmitry Streblechenko · Accepted Answer · 2019-09-15T21:11:49

0

votes

Use the MailItem.SentOnBehalfOfName property instead.

Return individual sender of archived mail for a shared mailbox

1 Answers