Grab sender email address instead of Name

Question

I have code that opens a specific email, grabs the reply to email address, closes that email, then creates a new email with the To as the sender email address of the previous email.

Sometimes the address comes over as the name and not the full xxxx@xxx.com.

Filename below is the email that is on my shared drive. Address is the variable where I want to store all of the email addresses, the 'Reply All'.

Set OutApp = GetObject(, "Outlook.Application")
Set outEmail = OutApp.Session.OpenSharedItem(Filename)
With outEmail.ReplyAll
    .display
    Address = .to
    .Close olDiscard
    Set outEmail = Nothing
End With

The code by Sue Mosher in my answer to [Creating a "Check Names" button in Excel](https://stackoverflow.com/questions/31161726/creating-a-check-names-button-in-excel/31161938#31161938) might work for you. — Darren Bartrup-Cook, Jan 31 '23 at 08:26
You may find the following useful: https://stackoverflow.com/a/66484483/3688861 — Tragamor, Feb 01 '23 at 17:00

Eugene Astafiev · Answer 1 · 2023-01-31T20:42:21.293

Use the Recipients property instead of the string To field which may contain the name of the sender, not the actual email address. Use Recipients(index), where index is the name or index number, to return a single Recipient object. The name can be a string representing the display name, the alias, or the full SMTP email address of the recipient.

The Recipient.Type returns or sets a long representing the type of recipient. For the MailItem a recipient can have one of the following OlMailRecipientType constants: olBCC, olCC, olOriginator, or olTo. So, iteraring over all recipients and checking the Type property value you can find the To recipients.

 For d = 1 To myItem.Recipients.count 
   Debug.Print myItem.Recipients.item(d).name 
   Debug.Print myItem.Recipients.item(d).Type 
 Next

Finally, you can use all properties of the Recipient object to get the email address such as Address or just set up the new email with the same object by using the Recipients.Add method which creates a new recipient in the Recipients collection. It accepts a string which represents the name of the recipient representing the display name, the alias, or the full SMTP email address of the recipient.

Note, you can get the Sender related properties from the original item without creating a reply-all draft item.

Thank you for the explanation. Can you please provide an example as I am unfamiliar with the syntax to do the above. I understand the concept, just dont know how to format the code. — David R, Jan 31 '23 at 15:33

Dmitry Streblechenko · Answer 2 · 2023-01-31T21:17:41.840

There is no reason to create / display / close a reply just to figure out who the message sender is. Use MailItem.SenderEmailAddress or MailItem.Sender.GetExchangeUser() (in case of an Exchange address):

Set outEmail = OutApp.Session.OpenSharedItem(Filename)
if outEmail.SenderEmailType = "EX" Then
  Address = outEmail.Sender.GetExchangeUser.PrimarySMTPAddress
Else
  Address = outEmail.SenderEmailAddress 
End If

If you need all recipients in an email, loop through the MailItem.Recipients collection and extract addresses from the Recipient.Address property or use Recipient.AddressEntry.GetExchangeUser.PrimarySMTPAddress. Make sure to exclude the current user:

Off the top of my head:

for each recip in outEmail.Recipients
  set addEntry = recip.AddressEntry
  if addEntry.Type = "EX" Then
    Addresses = Addresses & ";" & addEntry.GetExchangeUser.PrimarySMTPAddress
  Else
    Addresses = Addresses & ";" & addEntry.Address
  End If
next

Thank you for the above. This works but only gives me the sender, not any additional emails (i want to do a reply all basically) — David R, Jan 31 '23 at 15:34

Grab sender email address instead of Name

2 Answers2