Solved

How to compile a list of bad or returned email addresses

Posted on 2010-11-08
43
512 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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
 
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 53

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 143

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
When you have clients or friends from around the world, it becomes a challenge to arrange a meeting or effectively manage your time. This is where Outlook's capability to show 2 time zones in one calendar comes in handy.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

860 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