[SOLVED] Capturing actual on behalf of email id from outlook mail instead of the sender id

Capturing actual on behalf of email id from outlook mail instead of the sender id

On a daily basis, i receive hundreds of emails in outlook with no reply email id on behalf of some person for which i would like to create a database in excel using macro.

I have successfully created my database however the only challenge is fecthcing the actual email id.

Email comes from "noreply@xxx.com; on behalf of; Ayush Varshney "< varshneyayush@gmail.com >"

weherien using OutlookMail.SenderName, i gets details as noreply@xxx.com instead of varshneyayush@gmail.com.

Is there any way i can capture on behalf email id which is varshneyayush@gmail.com in this case.

Sub GetFromOutlook()

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Net Sales Report").Folders("Sales")

i = 1

For Each OutlookMail In Folder.Items
    If OutlookMail.ReceivedTime >= Range("From_date").Value Then
        Range("eMail_subject").Offset(i, 0).Value = OutlookMail.Subject
        Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
        Range("eMail_text").Offset(i, 0).Value = OutlookMail.Body

        i = i + 1
    End If
Next OutlookMail

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing

End Sub

Solution

There are two methods to get the Email of the Sender:

OutlookMail.Sender.Address - Will give the OnBehalfOf Email ID
OutlookMail.SenderEmailAddress - Will Give you the Sender Email Id

 OutlookMail.SentOnBehalfOfName

I think you are looking for. This will give you the required Name.