Getting email addresses from the text of an email

I have a client who has some .PST files (Outlook) with a number of emails in them He is wondering if there is some way to extract an email address from the body of the text. Is there?

He would like to avoid going through them manually (there are many) and extracting them that way. If nothing else, perhaps there is a way to make it easier.

Who is Participating?
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
Hi again,

MS-Outlook Visual Basic for Applications is not something I develop regularly, but hopefully this is somewhere near to a solution you can use:

Public Sub Extract_Email_Address_from_PST_File()

' --------------------------------------------------------------------------------------------------------------
' [ ]
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
' ID:               Q_28248779
' Question Title:   Getting email addresses from the text of an email
' Question Asker:   WoodrowA                                  [ ]
' Question Dated:   2013-09-25 at 02:46:30
' Expert Comment:   fanpages                                   [ ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ ]
' --------------------------------------------------------------------------------------------------------------

  Dim blnErr_Ignore                                     As Boolean
  Dim lngErr_Number                                     As Long
  Dim lngLoop                                           As Long
  Dim objFolder                                         As Folder
  Dim objMailItem                                       As MailItem
  Dim objNameSpace                                      As NameSpace
  Dim objVBScript_RegExp                                As Object
  Dim objVBScript_RegExp_Execute                        As Object
  Dim strBody                                           As String
  Dim strErr_Description                                As String
  On Error GoTo Err_Extract_Email_Address_from_PST_File
  blnErr_Ignore = False
  Set objNameSpace = ThisOutlookSession.GetNamespace("MAPI")
  objNameSpace.AddStore ("C:\Outlook Data File.pst")

  Set objFolder = objNameSpace.Stores("Outlook Data File").GetDefaultFolder(olFolderInbox)
  Set objVBScript_RegExp = CreateObject("VBScript.RegExp")
  blnErr_Ignore = True
  For Each objMailItem In objFolder.Items
      objVBScript_RegExp.Pattern = "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b"
      objVBScript_RegExp.Global = True
      objVBScript_RegExp.IgnoreCase = True
      strBody = ""
      Select Case (objMailItem.BodyFormat)
          Case (olFormatHTML)
              strBody = objMailItem.HTMLBody
          Case (olFormatPlain), _
               (olFormatRichText), _
              strBody = objMailItem.Body

          Case Else
              strBody = ""
      End Select ' Select Case (objMailItem.BodyFormat)
      If Len(Trim$(strBody)) > 0 Then
         If objVBScript_RegExp.Test(strBody) Then
            Set objVBScript_RegExp_Execute = objVBScript_RegExp.Execute(strBody)
            For lngLoop = 0& To (objVBScript_RegExp_Execute.Count - 1&)
                Debug.Print objVBScript_RegExp_Execute(lngLoop)
            Next lngLoop
            Set objVBScript_RegExp_Execute = Nothing
         End If ' If objVBScript_RegExp.Test(strBody) Then
      End If ' If Len(Trim$(strBody)) > 0 Then
  Next objMailItem
  blnErr_Ignore = False

  On Error Resume Next
  Set objVBScript_RegExp_Execute = Nothing
  Set objVBScript_RegExp = Nothing
  Set objMailItem = Nothing
  If Not (objFolder Is Nothing) Then
     If Not (objNameSpace Is Nothing) Then
        objNameSpace.RemoveStore objFolder
     End If ' If Not (objNameSpace Is Nothing) Then
  End If ' If Not (objFolder Is Nothing) Then
  Set objFolder = Nothing
  Set objNameSpace = Nothing
  Debug.Print "Finished"
  Exit Sub

  lngErr_Number = Err.Number
  strErr_Description = Err.Description
  On Error Resume Next
  If (blnErr_Ignore) Then
     On Error GoTo Err_Extract_Email_Address_from_PST_File
     Resume Next
  End If ' If (blnErr_Ignore) Then
  MsgBox "Error #" & CStr(lngErr_Number) & _
         vbCrLf & vbLf & _
         strErr_Description, _
         vbExclamation Or vbOKOnly, _
  Resume Exit_Extract_Email_Address_from_PST_File
End Sub

Open in new window

Please note these two lines:

  objNameSpace.AddStore ("C:\Outlook Data File.pst")

  Set objFolder = objNameSpace.Stores("Outlook Data File").GetDefaultFolder(olFolderInbox)

The file reference, "C:\Outlook Data File.pst", will need to be changed to match the location of the .PST file you wish to use.

The Stores reference, "Outlook Data File", will also need to be changed to match the Display Name of the .PST file.

PS. Any e-mail addresses found within the body of e-mail messages in the .PST "Inbox" folder are simply displayed in the "Immediate" window of the MS-Outlook VBProject environment.


Jeffrey CoachmanMIS LiasonCommented:
Not directly, ...even if you link the Inbox to MS Access you may only see the "friendly Name"
You would have to scan the actual email header AFAICT.

But I am sure it can be done, ...lets see what the other Experts may post...
I could probably hack some VBA code together, but you would get the REAL experts if you could get the question reclassified for VBA.  It shouldn't be difficult at all.
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

WoodrowAAuthor Commented:
Thank you.

I have requested reclassification.  Bear in mind that the email addresses I am looking for are not in the To or from of the emails but in the body of the text of the emails.
[ fanpages ]IT Services ConsultantCommented:

Are you looking for exact (explicit, fixed list of) e-mail addresses, or are you just looking for all instances of text that could be e-mail addresses (based on their similarity to a recognised pattern match for such an address)?

Also, are the mailitems within the "Inbox" of the ".PST" file, or are they stored within sub-folders?

Thank you for your clarification.


Jeffrey CoachmanMIS LiasonCommented:
Oh, ...then you can:
-Create a link to the inbox in Access
-Then use a Public function to extract the email:
Then call that function in a query, something like this:
    SELECT Inbox.Contents, ExtractEmailAddress([Contents]) AS TheEmailAddress
    FROM Inbox;


WoodrowAAuthor Commented:

I have not had time to investigate your code due to other commitments but hope to soon.  Thank you for sharing.

Best Regards

WoodrowAAuthor Commented:
Thank you again
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.