Extracting Outlook recipient addresses-CodePudding

I have a 2 columns named as To and Cc header in Excel. Actually, i need to EXTRACT To and Cc email IDs, not name into the Excel File from Outlook by using VBA code.

Sub ExportOutlookInfo()
Dim o As Outlook.Application
Set o = New Outlook.Application
Dim ons As Outlook.Namespace
Set ons = o.GetNamespace("MAPI")
Dim omail As Outlook.MailItem
Set omail = o.CreateItem(olMailItem)
Dim i As Long
Dim olRecip As Outlook.Recipient
Dim olRecipAddress As String
Dim SENT_FLDR As MAPIFolder
Dim Items As Outlook.Items
Set SENT_FLDR = ons.GetDefaultFolder(olFolderSentMail)
Set Items = SENT_FLDR.Items
Dim recp As Outlook.Application

For i = Items.Count To 1 Step -1
DoEvents
For Each olRecip In Items(i).Recipients
Debug.Print olRecip.Address
Next olRecip
Next i
End Sub

Please help me on it.

Thank you in advance!

CodePudding user response：

Use the Recipients property of the MailItem class to get recipient email addresses. The Using Outlook Recipient and Recipients collection – guide for developers article explains how to deal with recipients. If you need to get the recipient's email address you may use the following sequence of calls:

Recipient.AddressEntry.Address

The Address property of the AddressEntry class returns a string representing the e-mail address of the AddressEntry.

Also you may find the GetExchangeUser method of the AddressEntry class which returns an ExchangeUser object that represents the AddressEntry if the AddressEntry belongs to an Exchange AddressList object such as the Global Address List (GAL) and corresponds to an Exchange user. The ExchangeUser class provides the PrimarySmtpAddress property which I suppose you are looking for. Read more about converting email address in the HowTo: Convert Exchange-based email address into SMTP email address article.

There is another way of getting SMTP addresses of the recipients. The following GetSMTPAddressForRecipients method takes a MailItem as an input argument and then displays the SMTP address of each recipient for that mail item. The method first retrieves the Recipients collection that represents the set of recipients specified for the mail item. For each Recipient in that Recipients collection, the method then obtains the PropertyAccessor object that corresponds to that Recipient object, and uses the PropertyAccessor to get the value of the MAPI property https://schemas.microsoft.com/mapi/proptag/0x39FE001E, that maps to the SMTP address of the recipient.

Sub GetSMTPAddressForRecipients(mail As Outlook.MailItem) 
    Dim recips As Outlook.Recipients 
    Dim recip As Outlook.Recipient 
    Dim pa As Outlook.PropertyAccessor 
    Const PR_SMTP_ADDRESS As String = _ 
        "http://schemas.microsoft.com/mapi/proptag/0x39FE001E" 
    Set recips = mail.Recipients 
    For Each recip In recips 
        Set pa = recip.PropertyAccessor 
        Debug.Print recip.name & " SMTP=" _ 
           & pa.GetProperty(PR_SMTP_ADDRESS) 
    Next 
End Sub

CodePudding user response：

Assuming you are accessing the To and CC properties (which contain ; separated lists of display names which may or may not contain email addresses), you need to extract the addresses one at a time - loop through the MailItem.Recipients collection, then for each Recipient object, check the Recipient.Type property (can be olTo, olCC, olBCC). You can then read the Recipient.Address property.

Note that for Exchange recipients (address type of "EX" instead of "SMTP"), you will end up with EX addresses. If you always want an SMTP address, check that you have the right address type by reading the PR_ADDRTYPE (DASL name "http://schemas.microsoft.com/mapi/proptag/0x3002001F") MAPI property using Recipient.PropertyAccessor.GetProperty. If it is "SMTP", Recipient.Address property is all you need. If it is "EX", try to read the PR_SMTP_ADDRESS MAPI property (DASL name "http://schemas.microsoft.com/mapi/proptag/0x39FE001F"). If it is missing, your last resort will be Recipient.AddressEntry.GetExchangeUser().PrimarySmtpAddress (expensive and can raise an error).