vbaemailoutlooktext-files

How do you extract email addresses from the 'To' field in outlook?


I have been using VBA to some degree, using this code:

Sub ExtractEmail()
Dim OlApp As Outlook.Application
Dim Mailobject As Object
Dim Email As String
Dim NS As NameSpace
Dim Folder As MAPIFolder
Set OlApp = CreateObject("Outlook.Application")
' Setup Namespace
Set NS = ThisOutlookSession.Session
' Display select folder dialog
Set Folder = NS.PickFolder
' Create Text File
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\mydocuments\emailss.txt", True)
' loop to read email address from mail items.
For Each Mailobject In Folder.Items
   Email = Mailobject.To
   a.WriteLine (Email)
Next
Set OlApp = Nothing
Set Mailobject = Nothing
a.Close
End Sub

However this gives output as the names of the email addresses and not the actual email address with the "something@this.domain".

Is there an attributte of the mailobject that will allow the email addresses and not the names to be written from the 'To' Textbox.

Thanks


Solution

  • Check out the Recipients collection object for your mail item, which should allow you to get the address: http://msdn.microsoft.com/en-us/library/office/ff868695.aspx


    Update 8/10/2017

    Looking back on this answer, I realized I did a bad thing by only linking somewhere and not providing a bit more info.

    Here's a code snippet from that MSDN link above, showing how the Recipients object can be used to get an email address (snippet is in VBA):

    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