Export Recipient Mail Address instead of Name in Outlook VBA

Question

I try to export the Mail Addresses from my SentObjekts to Excel - this works fine. But the Outlook VBA Variable for the recipients Mail Address "To" exports the Name of the Recipient and not the Mail address. I will see the Mail Address in my Excel.

Did anybody knows how to export only the Mail address?

 Dim RecipientEmailAddress As String
     RecipientEmailAddress = .To

Does this answer your question? [How do you extract email addresses from the 'To' field in outlook?](https://stackoverflow.com/questions/12641704/how-do-you-extract-email-addresses-from-the-to-field-in-outlook) — niton, Mar 04 '21 at 12:43
When i use the second last Code from the answers i get only the sender mail adress but i have to get the receiver mail adress. — Alexander Metzger, Mar 04 '21 at 13:34
[Edit](https://stackoverflow.com/posts/66471876/edit) the question to put in code that runs. [mcve]. — niton, Mar 04 '21 at 15:41

score 1 · Answer 1 · answered Mar 04 '21 at 23:07

The following functions should be adaptable for what you require. A listener for new e-mail will be needed (which I assume you have), but you can then pass the MailItem into the EmailAddressInfo function which returns a 3 dimension array for the sender, To and CC addresses. I've included a test function which can be used to check this

The code should be expandable in the case of unknown e-mail types through the use of the CASE declarations

Private Const olOriginator As Long = 0, olTo As Long = 1, olCC As Long = 2, olBCC As Long = 3

Function PrintEmailAddresses(olItem As MailItem)
    If olItem.Class <> olMail Then Exit Function
    
    Dim Arr As Variant: Arr = EmailAddressInfo(olItem)
    Debug.Print "Sender: " & Arr(0)
    Debug.Print "To Address: " & Arr(1)
    Debug.Print "CC Address: " & Arr(2)
End Function

Private Function EmailAddressInfo(olItem As MailItem) As Variant
    If olItem.Class <> olMail Then Exit Function
    
    Dim olRecipient As Outlook.Recipient
    Dim ToAddress, CCAddress, Originator, email As String
            
    With olItem
        Select Case UCase(.SenderEmailType)
            Case "SMTP": Originator = .SenderEmailAddress
            Case Else: Originator = .Sender.GetExchangeUser.PrimarySmtpAddress
        End Select
    End With
    
    For Each olRecipient In olItem.Recipients
        With olRecipient
            Select Case UCase(.AddressEntry.Type)
                Case "SMTP": email = .Address
                Case Else: email = .AddressEntry.GetExchangeUser.PrimarySmtpAddress
            End Select
            Select Case .Type
                Case olTo: ToAddress = ToAddress & email & ";"
                Case olCC: CCAddress = CCAddress & email & ";"
            End Select
        End With
    Next
    EmailAddressInfo = Array(Originator, ToAddress, CCAddress)
End Function

Export Recipient Mail Address instead of Name in Outlook VBA

1 Answers1