Capturing on behalf of email id from Outlook mail

Question

I receive emails in Outlook with no reply email id on behalf of some person for which I would like to create a database in Excel.

The challenge is fetching the email id.

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

OutlookMail.SenderName gets 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

Answer 1

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.