Returning outlook sender address from VBA in Excel

Question

The following code I have copied from this answer here. It triggers the myItems_ItemAdd when a new email is received and is run from within excel. (Note I am not able to call New Mail events directly from outlook)

 Private WithEvents myItems As Outlook.Items

 Private Sub Class_Initialize()
 Dim oNS As Namespace
 Dim myOL As Outlook.Application
 Set myOL = New Outlook.Application
 Set oNS = myOL.GetNamespace("MAPI")
 Set myItems = oNS.GetDefaultFolder(olFolderInbox).Items

 End Sub

Private Sub myItems_ItemAdd(ByVal Item As Object)
  Debug.Print "Got_EMAIL!!!"
End Sub

I then wanted to access various other properties of the email like the subject. This for example works:

Private Sub myItems_ItemAdd(ByVal Item As Object)
  Debug.Print "Got_EMAIL!!!"
  Debug.Print Item.Subject     
End Sub

I would have thought as per the docs that a MailItem (which Item is?) should be accessible with :

Debug.Print Item.Sender

But that results in a Run-time error '287'

I also tried lines:

 Debug.Print Item.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10130102")
 Debug.Print Item.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0C1F001E")
 Debug.Print Item.PropertyAccessor.GetProperty(PR_SMTP_ADDRESS)

Which has the error:

When I inspect the Item object I notice that Sender and MAPIObject are both empty. I have been unable to find an answer to why Sender properties would be accessed through Property Accessor.

How can I access the sender's email address?

*** UPDATE **

Item.Sender.Address results in Run-time error '287' 

And as previously stated Sender and MAPIObject are both empty:

 

Answer 1

Returns a String that represents the sender of the Outlook item's email address. Read-only.

SenderEmailAddress is a syntax expression.

expression A MailItem object is represented by this variable.

The following Microsoft Visual Basic for Applications (VBA) example loops over all items in the Test folder in the Inbox and flags anything sent by'someone@example.com' with a yellow flag. Make sure the Test folder exists in the default Inbox folder and replace'someone@example.com' with a valid sender email address in the Test folder to run this example without issues.

Sub SetFlagIcon() 
 
 Dim mpfInbox As Outlook.Folder 
 
 Dim obj As Outlook.MailItem 
 
 Dim i As Integer 
 
 
 
 Set mpfInbox = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Folders("Test") 
 
 ' Loop all items in the Inbox\Test Folder 
 
 For i = 1 To mpfInbox.Items.Count 
 
 If mpfInbox.Items(i).Class = olMail Then 
 
 Set obj = mpfInbox.Items.Item(i) 
 
 If obj.SenderEmailAddress = "someone@example.com" Then 
 
 'Set the yellow flag icon 
 
 obj.FlagIcon = olYellowFlagIcon 
 
 obj.Save 
 
 End If 
 
 End If 
 
 Next 
 
End Sub