Extracting Email Addresses from Outlook Export

[kreestar]

I save bounced messages in an Outlook folder and export these as CSV/Excel sheet. I then need to split out the email addresses so that they can be marked dud in my mailing lists.

The cells with the email addresses are laid out thus (cut and pasted from the cell):

"Delivery has failed to these recipients or distribution lists:

abc@xyz.com
The recipient's e-mail address was not found in the recipient's e-mail system. Microsoft Exchange will not try to redeliver this message for you. Please check the e-mai"

I've tried Text to Columns with space delimiters, but - maybe because of the hard-returns - the data disappears. It splits out "delivery" and "has" and "failed" etc. until the ":" at which point the column cells to the right are blank.

I've tried to replace "Delivery has failed...lists:" with "|" and then convert to columns (the | works, but the second column when converting is empty).

I've turned off text wrapping, I've forayed into the world of VB suggestions on similar questions on the board, but have come a cropper...

I don't know how to find/replace the hard-returns or line-feeds in the body.

All I need is to extract the text surrounding the @ and I'm going batty :D

 

[BrianB]

Hopefully this will do what you want.

'=============================================================================
'- REGULAR EXPRESSION TO EXTRACT EMAIL ADDRESS FROM A STRING
'- This will match addresses like 'abc@xyz.com' and 'abc@xyz.co.uk'
'- LOOP WORKSHEET ROWS
'- Column A contains text. Extract to column C
'- Brian Baulsom August 2010
'=============================================================================
Sub EXTRACT_EMAIL()
    Dim ws As Worksheet
    Dim MyRow As Long
    Dim LastRow As Long
    Dim MyRegExp As Object
    Dim MyText As String       ' Full text string
    Dim ExtractText As String
    Dim MyPattern As String     ' string search pattern
    Dim MyMatches As Variant
    '-------------------------------------------------------------------------
    '- INITIALISE VARIABLES
    Application.Calculation = xlCalculationManual
    Set ws = ActiveSheet
    LastRow = ws.Range("A65536").End(xlUp).Row
    Set MyRegExp = CreateObject("VbScript.RegExp")
    MyPattern = "\b\w*@\w*\.\w*\.\w*\b|\b\w*@\w*\.\w*\b"
    '-------------------------------------------------------------------------
    '- LOOP
    For MyRow = 1 To LastRow
        MyText = ws.Cells(MyRow, 1).Value
        '---------------------------------------------------------------------
        '- EXECUTE REGULAR EXPRESSION
        With MyRegExp
            .Global = False      ' replace first instance only
            .pattern = MyPattern
            .ignorecase = True
            Set MyMatches = .Execute(MyText)        ' zero based array
        End With
        '---------------------------------------------------------------------
        '- RESULT TO SHEET
        If MyMatches.Count = 0 Then
            ExtractText = "?"
        Else
            ExtractText = MyMatches(0)
        End If
        '---------------------------------------------------------------------
        ws.Cells(MyRow, 3).Value = ExtractText
    Next
    '-------------------------------------------------------------------------
    Set MyRegExp = Nothing
    MsgBox ("Done")
End Sub
'-----------------------------------------------------------------------------

 

[kreestar]

Now that is just awesome! Marvelous! Thank you very much.

 

[merrydesuza27]

Softaken Outlook pst extractor is a simple application .This software is a multifunctional programme .This is very simple interface and easy to operate.MS Outlook Attachment Extractor Software to Extract Adobe PDF, Word DOC/DOCX, Excel XLS/XLSX and other documents quickly.This software is capable to extract pst data file attachments.This tool is highly compatible with all the latest version.

 

[Madisonarbon]

I found a tool and now want to share this tool because this is very useful for outlook users, sometimes we need to extract all attachments or email addresses or contacts from outlook pst then the manual method will take lots of time so I used this DataVare Outlook Attachment Extractor Software to extract my attachments. You can also extract all attachments. You can also extract contacts, calendars, and email addresses.