Returning outlook sender address from VBA in Excel

0 votes

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'

enter image description here

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:

enter image description here

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' enter image description here

And as previously stated Sender and MAPIObject are both empty:

enter image description here

Apr 1 in Database by Edureka
• 9,320 points
24 views

1 answer to this question.

0 votes

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
answered Apr 4 by Edureka
• 8,820 points

Related Questions In Database

0 votes
0 answers

How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

 need the row/column combinations marked with an ...READ MORE

Feb 24 in Database by Edureka
• 9,320 points
27 views
0 votes
1 answer

Prevent cell numbers from incrementing in a formula in Excel

In Excel, you can use a feature ...READ MORE

answered Mar 15 in Database by Edureka
• 8,820 points
27 views
0 votes
1 answer

[Excel][VBA] How to draw a line in a graph?

Sub MakeChart() Dim x(20) ...READ MORE

answered Mar 24 in Database by Edureka
• 8,820 points
26 views
0 votes
1 answer

Convert numbers to words in Excel (VBA)

In the cell where you wish to ...READ MORE

answered Mar 25 in Database by Edureka
• 8,820 points
37 views
0 votes
1 answer

Remove time from date field in Excel formula

Use the Find And Replace function to ...READ MORE

answered Mar 30 in Database by Edureka
• 8,820 points
20 views
0 votes
1 answer

Generating username from first name and last name in excel

How to automatically merge first and last ...READ MORE

answered Mar 30 in Database by Edureka
• 8,820 points
38 views
0 votes
1 answer

How to download excel in response from api react.js

Create ReactJS project import React, { Component } ...READ MORE

answered Mar 31 in Database by Edureka
• 8,820 points
103 views
0 votes
0 answers

How to loop in excel without VBA or macros?

Is it possible to iterate (loop) a ...READ MORE

Mar 30 in Database by Edureka
• 9,320 points
62 views
0 votes
1 answer

How to sort dates from Oldest to Newest in Excel?

Drag down the column to select the ...READ MORE

answered Feb 23 in Database by Edureka
• 8,820 points
34 views
0 votes
1 answer

Remove special characters from the specified string in excel

To erase a specific character from a ...READ MORE

answered Mar 15 in Database by Edureka
• 8,820 points
51 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP