Solved

How to compile a list of bad or returned email addresses

Posted on 2010-11-08
43
505 Views
Last Modified: 2012-05-10
Hi,

I would like to keep track of bad email addresses when they are sent via outlook and are returned.

Is it possible to have a macro so that the follwing happens:

When a sent email has a bad email address and is returned because it can't go through, the bad email address is listed in a column in excel.  The excel spreadsheet would have two columns: one for the person's ID and one for bad email address.

The person's ID is in the subject of the email and will always have NCXXXXXXX, in which X is any digit number such as NC1214567.

Thank you for your assistance.

Amreska
0
Comment
Question by:Amreska
  • 22
  • 14
  • 4
  • +2
43 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 34088146
Hi, Amreska.

That might be possible.  Do you have a sample returned mail that I can use as an example?
0
 

Author Comment

by:Amreska
ID: 34093151

From: Mail Delivery Service [mailto:postmaster@ncmail.net]
Sent: Wednesday, September 15, 2010 3:24 PM
To: Ali, Amro
Subject: Undeliverable: Work Group (NC0123567)
 
 
Delivery has failed to these recipients or distribution lists:
 
H20SUPER@BIZEC.RR.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-mail address and try resending this message, or provide the following diagnostic text to your system administrator.
 
The following organization rejected your message: mx1.biz.rr.com.
 
0
 

Author Comment

by:Amreska
ID: 34093620
Hi BlueDevilFan:

Thanks for taking over this question.

Amreska
0
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 34093749
In theory, sure.  You can access the folders in outlook and look for failure messages, however if you are sending email to different people, you will get back LOTS of different bounce messages, and sometimes none at all.  You also get response from system like Earthlink that respond with a SPAM response, e.g. "fill out this form and we will send your message".  That not withstanding, if you have a outlook client, I'll post some code shortly you can adapt.
0
 
LVL 16

Assisted Solution

by:JohnBPrice
JohnBPrice earned 250 total points
ID: 34093916
This is some code I wrote to do the same thing, it tries to find emails that are bounces in the various formats I get.  I also have to manually review the incoming and unkown folders to see if there are other formats I can identify.  Note that some systems will not send you a bounce message at all.  Some won't include your subject or sent to email address.

I left out the code where I was handling the bounces, as you will have to do something different.  I also left out some other routines like ManualFindEMail, but it should be pretty clear what they do.

Option Explicit

Option Compare Text



Private Sub cmdBounces_Click()

    Dim o As New Outlook.Application

    Dim Incoming As Outlook.MAPIFolder

    Dim Processed As Outlook.MAPIFolder

    Dim Unknown As Outlook.MAPIFolder

    Dim n As Outlook.NameSpace

    Dim i As Integer

    Set n = o.GetNamespace("MAPI")

    Dim msg As Outlook.MailItem

    Dim counter As Integer

    Dim FolderName As String

    Dim body As String

    Dim email As String

    Dim rsQual As New ADODB.Recordset

    

    Dim rs As New ADODB.Recordset

    Dim iStart As Long

    Dim istop As Long

    



    Screen.MousePointer = vbHourglass

    FolderName = "\\Public Folders\All Public Folders\XXYYZZ\Support\ToProcess"

    Set Incoming = FindFolder(n.Folders, FolderName)

    If Incoming Is Nothing Then

        MsgBox "can't find folder " & FolderName

        Exit Sub

    End If



    Set Processed = FindFolder(n.Folders, "\\Public Folders\All Public Folders\XXYYZZ\Support\Bounced")

    Set Unknown = FindFolder(n.Folders, "\\Public Folders\All Public Folders\XXYYZZ\Support\BouncedUnknown")

    If Processed Is Nothing Then

        MsgBox "can't find folder " & FolderName & "\Bounced"

        Exit Sub

    End If

    

    For i = Incoming.Items.Count To 1 Step -1

        Set msg = Incoming.Items(i)

        email = ""

        

'    If msg.Subject = "failure notice" Or msg.Subject = "Mail Delivery failed" Or msg.Subject = "Undelivered Mail Returned to Sender" _

'        Or msg.Subject = "Delivery Failure" Then

        'using bounced folder, they are all bounces

        body = msg.body

        email = GetEMail(body, "I was not able to deliver your message to the following addresses.", ">:")

        If email = "" Then

            email = GetEMail(body, "The following address failed:", ">:")

        End If

        

        If email = "" Then

            email = GetEMail(body, "Failed Recipient: ", vbCrLf)

        End If

        If email = "" Then

            email = GetEMail(body, "Failed Recipient: ", vbLf)

        End If

        If email = "" Then

            email = GetEMail(body, "Failed Recipient: ", vbCr)

        End If

        If email = "" And body Like "*failed recipient*" Then Stop

        If email = "" Then

            email = GetEMail(body, "550 5.1.1 <", ">")

        End If

        If email = "" Then

            email = GetEMail(body, "<", ">:")

        End If

        If email = "" Then

            email = GetEMail(body, "User mailbox exceeds allowed size:", vbCrLf)

        End If

        If email = "" Then

            email = GetEMail(body, "User mailbox exceeds allowed size:", vbLf)

        End If





        'find anything with a <blahblah@blahblah> in it

        If email = "" Then

            email = GetEMail(body, "<", ">")

        End If

        

        If email = "" And chkInteractive.Value = vbChecked Then

            email = frmCheckEMail.ManualFindEMail(msg)

        

        

            'Do this last

        ElseIf email = "" Then

            email = msg.SenderEmailAddress

            

        End If

        



        If  email <> "" Then

            email = Replace(email, "'", "")



DO WHATEVER HERE WITH YOUR BOUNCED EMAIL ADDRESS



            msg.Move Processed

        Else 'couldn't find address

              

            msg.Move Unknown

        

        End If

        DoEvents

    Next i

    Screen.MousePointer = vbDefault

    MsgBox "Done"

End Sub





Function FindFolder(TopFolder As Outlook.Folders, TargetPath As String) As Outlook.MAPIFolder

    Dim TempFolder As Outlook.MAPIFolder

    Dim TempFolder2 As Outlook.MAPIFolder

    For Each TempFolder In TopFolder

        If TempFolder.FolderPath = TargetPath Then

            Set FindFolder = TempFolder

            Exit Function

        Else 'walk through this folder too

            Set TempFolder2 = FindFolder(TempFolder.Folders, TargetPath)

            If Not TempFolder2 Is Nothing Then

                Set FindFolder = TempFolder2

                Exit Function

            End If

        End If

    Next

    

End Function



Function GetEMail(body As String, prefix As String, suffix As String) As String

    Dim iStart As Long

    Dim istop As Long

    Dim email As String

    email = ""

    iStart = InStr(1, body, prefix)

    If iStart > 0 Then

        istop = InStr(iStart, body, suffix)

        If istop > 0 Then

            email = Mid(body, iStart + Len(prefix), istop - iStart - Len(prefix))

            If InStr(1, email, vbCrLf) = 0 Then 'we found it on one line

                'email = Replace(email, vbCrLf, "")

                email = Replace(email, "<", "")

                email = Replace(email, ">", "")

                email = Trim(email)

            Else

                email = ""

            End If

        Else

            'Stop

        End If

    Else

        'Stop

    End If

    GetEMail = email

End Function

Open in new window

0
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 34093959
oops, you must uncomment lines 43 and 44 and add an "End If" twixt lines 103/104.  (After I do a manual review, I'll put the collected emails into a special bounced folder and run this again without lines 43 and 44 to see if I can pick up more mail addresses.)
0
 

Author Comment

by:Amreska
ID: 34094914
I wouldl like to compile the returned email addresses into a spreadsheet just like the question description above.

Thanks
0
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 34095366
That's the easy part once you get the email and find the subject line (note my example does not find the original subject line, you will have to modify it.  Once you do get the email and Person's ID, all you have to do is stick it in excel, such as....


'put this at the beginning

Dim xl As New Excel.Application

Dim wb As Excel.Workbook

Dim ws As Excel.Worksheet

Set wb = xl.Workbooks.Open("C:\YourSpreadsheetNAmeHere.xls")

Set ws = wb.Worksheets(1)



'then call this everytime you figure out the email and ID

Sub AddEmail(Email As String, PersonID As String)

    Dim NextRow As Integer

    NextRow = ws.UsedRange.Rows.count + 1

    ws.Cells(NextRow, 1) = Email

    ws.Cells(NextRow, 2) = PersonID

End Sub



'Then when you are done, save it

wb.Save

wb.Close

xl.Quit

Open in new window

0
 
LVL 76

Accepted Solution

by:
David Lee earned 250 total points
ID: 34100530
Amreska,

Here's my solution.  Follow these instructions to use it.

1.  Save the attached spreadsheet to a folder.
2.  Start Outlook
3.  Click Tools > Macro > Visual Basic Editor
4.  If not already expanded, expand Microsoft Office Outlook Objects
5.  If not already expanded, expand Modules
6.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
7.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
8.  Edit the code by changing the path to the spreadsheet to match the folder you saved it to in step #1
9.  Click the diskette icon on the toolbar to save the changes
10. Close the VB Editor
11. Create a rule that fires for these messages
12. Set the rule's action to "run a script" and select ProcessNDR as the script to run

You're ready to go.  One note.  This will only work if the messages are in the format you gave.  If they differ at all, then this won't do what you want it to do.
Sub ProcessNDR(Item As Outlook.MailItem)
    Dim strID As String, strAddress As String, intP1 As Integer, intP2 As Integer, adoCon As Object
    intP1 = InStr(1, Item.Subject, "(") + 1
    intP2 = InStr(1, Item.Subject, ")") - 1
    strID = Mid(Item.Subject, intP1, intP2 - intP1)
    strAddress = GetEmailAddress(Item.Body)
    Set adoCon = CreateObject("ADODB.Connection")
    With adoCon
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        'Change the file name and path on the following line
        .ConnectionString = "Data Source=C:\eeTesting\NDR.xls;Extended Properties=Excel 8.0;"
        .Open
        .Execute "INSERT INTO [Sheet1$] (ID,Email) VALUES('" & strID & "','" & strAddress & "')"
    End With
    Set adoCon = Nothing
End Sub

Function GetEmailAddress(strBody As String) As String
    Dim objRegEx As Object, colMatches As Object
    Set objRegEx = CreateObject("VBscript.RegExp")
    With objRegEx
        .IgnoreCase = True
        .Global = True
        .Pattern = "([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})"
        Set colMatches = .Execute(strBody)
    End With
    If colMatches.count > 0 Then
        GetEmailAddress = colMatches.Item(0)
    Else
        GetEmailAddress = ""
    End If
    Set objRegEx = Nothing
    Set colMatches = Nothing
End Function

Open in new window

NDR.xls
0
 

Author Comment

by:Amreska
ID: 34102353
Thank you BlueDevilFan

I tried your macro above and the only thing is that nothing is saved in the excel spreadsheet when an email with bad email address is received.  I setup a rule so the following that I run a script when an email is received.  Thats it.

Thanks
0
 

Author Comment

by:Amreska
ID: 34102510
Please note that I have excel 2007.
0
 

Author Comment

by:Amreska
ID: 34102635
Hi BlueDevilFan:

You mentioned format should be the same as I gave you.  Does that mean that the subject has to the same as I told you?.  Because I may want to change the subject but will still have the ID

Thanks,
Amreska
0
 

Author Comment

by:Amreska
ID: 34136449
Hi BlueDevilFan:

I hope you are doing well.  I would like to ask if there are any updates.

Thanks,
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34137245
Hi, Amreska.

Apologies, I though I'd replied.  No, the subject does not have to be an exact match, at least not for the script.  From the script's perspective the subject just has to contain the ID in parenthesis.  It's up to you to construct a rule that catches the right messages.  That may require a near exact match on the subject, but that has nothing to do with the script.  The script extracts the ID from between the parenthesis then extracts the SMTP email address from the body. So long as the subject has an ID in parenthesis, there is only one set of parenthesis in the subject line, and the body contains just one SMTP email address, then the script is good to go.

So long as you use a spreadsheet that's been saved in 2003 format, then the script should work without regard to the actual version of Excel you use.  I tested the code using Outlook and Excel 2007.  It worked perfectly.  
0
 

Author Comment

by:Amreska
ID: 34139878
Hi BlueDevilFan:

The returned email has at the bottom of the page "Diagnostic Information for administrators:"

At the bottom also several email addresses.  Does this affect thse script?.  Becasue it is still not working.

Thanks,
Amreska
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34139966
The returned email has at the bottom of the page "Diagnostic Information for administrators:"

This should not have any affect on the script.

-----

At the bottom also several email addresses.  Does this affect thse script?.

Yes.  As I noted in my last post "... and the body contains just one SMTP email address, then the script is good to go."
If the body contains multiple addresses, then it won't work properly.  How can it tell which address is the rejected address?

If nothing is being inserted into the spreadsheet, then I can only assume that the subject line does not have the ID in parenthesis and that the multiple SMTP addresses in the body are causing a problem.  I tested using the message that you gave as a sample and it worked perfectly.  If the message(s) you're running it against don't match the conditions I specified, then it's no wonder it's not working.  The only other possibility is that the script isn't running at all.  How are you testing it?
0
 

Author Comment

by:Amreska
ID: 34140349
Yes there arre multiple emails in the body.  But the first one is the right one to choose.  Can the script be modified?

Thanks,
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34140469
Possibly, but I have to have some sample to work from.  I can't do it without knowing what the messages look like.
0
 

Author Comment

by:Amreska
ID: 34146758


_____________________________________________
From: Mail Delivery Service [mailto:postmaster@ncmail.net]
Sent: Wednesday, November 10, 2010 10:13 AM
To: Michael, James
Subject: Undeliverable: Group (NC1234567)


Delivery has failed to these recipients or distribution lists:

CCLEETWOOD@NC.RR.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-mail address and try resending this message, or provide the following diagnostic text to your system administrator.

The following organization rejected your message: cdptpa-smtpin01.mail.rr.com.







Diagnostic information for administrators:

Generating server: relay3.ncmail.net

CCLEETWOOD@NC.RR.COM
cdptpa-smtpin01.mail.rr.com #<cdptpa-smtpin01.mail.rr.com #5.1.1 smtp; 550 5.1.1 - Invalid mailbox: CCLEETWOOD@nc.rr.com> #SMTP#

Original message headers:

Return-Path: <michael.james@gmail.com>
Received: from relay3.ncmail.net (145.00.001) by relay3.ncmail.net (5.6.789)
        id 364 for CCLEETWOOD@NC.RR.COM; Wed, 10 Nov 2010
 10:13:24 -0500
Received: from smtp.mail.gs.com (10.12.5.151) by relay3.ncmail.net (7.3.118)
        id 4C6C91FA0037EBF3 for CCLEETWOOD@NC.RR.COM; Wed, 10 Nov 2010 10:13:24 -0500
Received: from sfsf.ncmal. ([45.5698.58]) by
 ncwitmxhtep32.ad.ncmail ([45.655.9698]) with mapi; Wed, 10 Nov 2010 10:13:24
 -0500
From: "Michael, James" <michael.james@gmail.com>
To: "CCLEETWOOD@NC.RR.COM" <CCLEETWOOD@NC.RR.COM>
Date: Wed, 10 Nov 2010 10:13:23 -0500
Subject: Group (NC1234567)
Thread-Topic: Group NC1234567
Thread-Index: AcuA6dFNZSS/jD9vS4yMfFBv/lW8IA==
Message-ID: <0763A1040C09E84F890606F2759FDADD55716200@NCWITMXMBEV36.ad.ncmail>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach:
X-MS-TNEF-Correlator:
acceptlanguage: en-US
Content-Type: multipart/alternative;
      boundary="_000_0763A1040C09E84F890606F2759FDADD55716200NCWITMXMBEV36ad_"
MIME-Version: 1.0

0
 

Author Comment

by:Amreska
ID: 34156426
Hi BlueDevilFan:

I attached a sample above.

Thanks
0
 

Author Comment

by:Amreska
ID: 34165473
Hi BlueDevilFan:

I would like to get an update whether it is possible.

Thanks,
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 76

Expert Comment

by:David Lee
ID: 34169980
Having so many addresses in the body of the message makes getting the correct address more challenging.  Do all of the messages have the line

Delivery has failed to these recipients or distribution lists:

followed by the failed address?
0
 

Author Comment

by:Amreska
ID: 34173694
Hi BlueDevilFan:

Yes all follow the same format.  Only the first email address after "Delivery has failed to these recipients or distribution lists" is what I am looking for.

Thanks,
Amreska
0
 

Author Comment

by:Amreska
ID: 34184843
Hi,

Any updates.  Please let me know or I will try to pursue other options.

Thanks,
0
 

Author Comment

by:Amreska
ID: 34189822
Hello
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34190329
I haven't forgotten the question.  Please hang on a bit longer.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34190525
I just tested with the message body that you provided in post 34146758 and the code works perfectly as is.  Have you tried stepping though it in the debugger to see what's happening?
0
 

Author Comment

by:Amreska
ID: 34197283
No I haven't tested it using debugger
0
 

Author Comment

by:Amreska
ID: 34199069
Hi,

The debugging is not working because there is no call of event to the script.  Can you assist me with how to debug this script.

thank you
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34202445
Do you have a rule that calls the script?  That's what triggers it.
0
 

Author Comment

by:Amreska
ID: 34206235
Hi,

Can you guide me through how to debug the script.  I am clueless.

Thank you
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34216615
Sure.

1.  Open the VB editor in Outlook.
2.  Click on line #3 of the code then press F9.  The line should turn red.
3.  Trigger the code.  When execution gets to line #3 processing will stop and the editor will appear with line #3 in yellow.
4.  Press F8 to execute the current line of code and move to the next line.  Mouse over any variable name to see what it contains.

There are two goals.  First, we need to see if strID has the ID from the subject line.  Check that after executing line #5 of the code.  Second, to see if strAddress contains the address from the message body.  Check that after executing line #6 of the code.
0
 

Author Comment

by:Amreska
ID: 34241005
Hi,

It seems that the code is not running because when the code is trigerred nothing happens and no yellow line appears.

Thanks
0
 

Author Comment

by:Amreska
ID: 34254549
why ignoring me
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34254644
I'm not ignoring you.  I have about 200 open questions.  I also have a full-time job, family, etc.  I work on questions as time permits.  Sometimes it takes longer for me to respond.

If the code isn't running, then

1.  Are macros enabled?
2.  Is the rule set to run the code?
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 34254677
And I was feeling ambitious juggling a couple of dozen :-).  But I hear you on "life gets in the way of EE...:.

~bp
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34256131
LOL.  As of right now I have 203 open questions.  If EE was a paying gig, then I'd be on here all the time.
0
 

Author Comment

by:Amreska
ID: 34257488
Yes the macro is enabled.  Here is how I set the rule:

Apply this rule after message arrives and run Project1.ProcessNDR

Is that correct?

Thanks
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34270776
That's correct.  Are macros enabled?  Not this macro, but macros in general?
0
 

Author Comment

by:Amreska
ID: 34361201
Yes they are enabled.

0
 
LVL 76

Expert Comment

by:David Lee
ID: 34419499
If macros are enabled then I don't see how the code can fail to run.  
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34941479
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now