CODEWITHSUNDEEP
vbaoutlook
air
air

Reputation: 6264

extract email address from outlook

I am trying to extract email addresses of all emails in my Outlook inbox. I found this code on the Internet.

Sub GetALLEmailAddresses()

Dim objFolder As MAPIFolder
Dim strEmail As String
Dim strEmails As String
''' Requires reference to Microsoft Scripting Runtime
Dim dic As New Dictionary
Dim objItem As Object

''Set objFolder = Application.ActiveExplorer.Selection
Set objFolder = Application.GetNamespace("Mapi").PickFolder

For Each objItem In objFolder.Items

   If objItem.Class = olMail Then

       strEmail = objItem.SenderEmailAddress

       If Not dic.Exists(strEmail) Then

           strEmails = strEmails + strEmail + vbCrLf

           dic.Add strEmail, ""

       End If

I am using outlook 2007. When I run this code from the Outlook Visual Basic Editor with F5 I get an error on the following line.

Dim dic As New Dictionary

"user defined type not defined"

Upvotes: 0

Views: 19193

Answers (4)

Bob Mortimer
Bob Mortimer

Reputation: 459

Here's an updated version for those using Exchange. It converts Exchange format addresses to normal email addresses (with the @ symbol). 

' requires reference to Microsoft Scripting Runtime 
Option Explicit

Sub Write_Out_Email_Addresses()
    ' dictionary for storing email addresses
    Dim email_list As New Scripting.Dictionary

    ' file for output
    Dim fso As New Scripting.FileSystemObject
    Dim out_file As Scripting.TextStream
    Set out_file = fso.CreateTextFile("C:\emails.csv", True)

    ' open the inbox
    Dim ns As Outlook.NameSpace
    Set ns = Application.GetNamespace("MAPI")
    Dim inbox As MAPIFolder
    Set inbox = ns.GetDefaultFolder(olFolderInbox)

    ' loop through all items (some of which are not emails)
    Dim outlook_item As Object
    For Each outlook_item In inbox.Items
        ' only look at emails
        If outlook_item.Class = olMail Then

            ' extract the email address
            Dim email_address As String
            email_address = GetSmtpAddress(outlook_item, ns)

            ' add new email addresses to the dictionary and write out
            If Not email_list.Exists(email_address) Then
                out_file.WriteLine email_address
                email_list.Add email_address, ""
            End If
        End If
    Next
    out_file.Close
End Sub

' get email address form a Mailoutlook_item
' this entails converting exchange format addresses
' (like " /O=ROOT/OU=ADMIN GROUP/CN=RECIPIENTS/CN=FIRST.LAST")
' to proper email addresses
Function GetSmtpAddress(outlook_item As Outlook.MailItem, ns As Outlook.NameSpace) As String

    Dim success As Boolean
    success = False

    ' errors can happen if a user has subsequently been removed from Exchange
    On Error GoTo err_handler

    Dim email_address As String
    email_address = outlook_item.SenderEmailAddress

    ' if it's an Exchange format address
    If UCase(outlook_item.SenderEmailType) = "EX" Then
        ' create a recipient
        Dim recip As Outlook.Recipient
        Set recip = ns.CreateRecipient(outlook_item.SenderEmailAddress)

        ' extract the email address
        Dim user As Outlook.ExchangeUser
        Set user = recip.AddressEntry.GetExchangeUser()
        email_address = user.PrimarySmtpAddress
        email_address = user.Name + " <" + user.PrimarySmtpAddress + ">"
        success = True
    End If

err_handler:
    GetSmtpAddress = email_address
End Function

Kudos to http://forums.codeguru.com/showthread.php?441008-Extract-sender-s-email-address-from-an-Exchange-email and Brettdj

Upvotes: 0

Dennis
Dennis

Reputation: 19

export the file to C:\Users\Tony\Documents\sent file.CSV

Then use ruby

email_array = []
r = Regexp.new(/\b[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}\b/) 
CSV.open('C:\Users\Tony\Documents\sent file.CSV', 'r') do |row|
    email_array << row.to_s.scan(r)                           
end
puts email_array.flatten.uniq.inspect

Upvotes: 1

brettdj
brettdj

Reputation: 55702

I have provided updated code below

  1. to dump the Inbox email addresses to a CSV file "c:\emails.csv" (the current code provides no "outlook" for the collected addresses
  2. the code above works on a selected folder rather than Inbox as per your request

[Update: For clarity this is your old code that uses "early binding", setting this reference is unnecessary for my updated code below which uses "late binding"]

Part A: Your existing code (early binding)

In terms of the error you received:

The code sample aboves uses early binding, this comment "Requires reference to Microsoft Scripting Runtime" indciates that you need to set the reference

  • Goto the Tools menu
  • Select 'References'
  • check "Microdoft Scripting Runtime"

enter image description here Part B: My new code (late binding - setting the reference is unnecessary)

Working Code 

Sub GetALLEmailAddresses() 
Dim objFolder As MAPIFolder
Dim strEmail As String
Dim strEmails As String
Dim objDic As Object
Dim objItem As Object
Dim objFSO As Object
Dim objTF As Object

Set objDic = CreateObject("scripting.dictionary")
Set objFSO = CreateObject("scripting.filesystemobject")
Set objTF = objFSO.createtextfile("C:\emails.csv", 2)
Set objFolder = Application.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)
For Each objItem In objFolder.Items
    If objItem.Class = olMail Then
        strEmail = objItem.SenderEmailAddress
        If Not objDic.Exists(strEmail) Then
            objTF.writeline strEmail
            objDic.Add strEmail, ""
        End If
    End If
Next
objTF.Close
End Sub

Upvotes: 4

Blake K Peterson
Blake K Peterson

Reputation: 481

In outlook, export a folder to a csv file, then open in Excel. A simple MID function should be able to extract the email address if it's not been placed in a "from" column already.

Upvotes: -1

Related Questions