Processing bad email addresses in our customer list.

Back in 2014 I wrote a post about processing returned email and enough has changed that the advice is no longer relevant.

I use Apple’s email client to send and receive mail and this time around I created rules for dealing with the remove requests, auto replies, and bad addresses. I don’t get many remove requests so I handle them individually. Likewise, the automated replies usually are out of office or moved to a new location so they get manual treatment as well.

This time around I got 12,002 failures—although the actual number is much lower because servers will give 24, 48, and 72 hour notices as well when they can’t deliver the mail. Deleting the 7,747 warning messages yields 4,255. I used three rules to put the returned mail into a Failure mailbox.

Subject begins with Warning: message
Subject begins with Undeliverable
Subject begins with Mail delivery failed:

This caught most of them. I then selected all of the messages and printed them to a PDF file. Mail does not warn you that it will only print 300 pages so if you don’t have a lot of failures you can select groups of messages and make sure that there are fewer than 300 pages in the group. My computer is old so it also is slow to delete messages. So you need to be patient.

If you can get the messages into a PDF file you can open it in Preview, select all and then use the BBEdit feature Process Lines Containing to first extract lines with @ and then remove ones with your domain. From there it was just a matter of getting rid of things like retry timeout exceeded, Final-Recipient: rfc822;, and Status: 5.0.0 by doing a simple find and replace.

Getting rid of the last group took a bit of grepping since I wanted to get rid of everything before the “<” e.g.

To: "Alice Foxwood" <AliceFoxwood@yahoo.com>

but it’s just

*.<

If you have lots of failures, and I normally get a bunch of I haven’t sent out an email in a while, then you need a different method. First you need to find where Mail put the Failure folder. MacOS won’t let you into the Mail directory so you need to copy it to your Desktop and then use the terminal to find one of the failed messages.

cd /Users/jscarry/Desktop/Mail
grep -R 'AliceFoxwood@yahoo.com' .

In my case the message was in

cd ./V7/06676897-0872-4618-9225-222A5281A236/Failure.mbox/69AC0518-E9C8-4B54-ADD0-2A0B676267B4/Data/

Then I copied every line with an @ to a file:

grep -R "@" . > ~/Desktop/failure.txt

This method gives you all of the header info so you get lots more junk than in the first method. I spent maybe 20 minutes using BBEdit to get rid of things before and after the address. Multiple sorts and deleting duplicates helped. I also used the Process Lines Containing feature to find all the lines with @ and copy them to a new file since some of my edits resulted in lines without an email address.

There are lots of duplicates in the list since so I sorted the lines and then removed duplicates. That left me with 4255 bad addresses which I then imported to my bad address table. That’s more than the 4,116 failure emails because some of the failure messages had an internal email address e.g. AliceFoxwood@baylor0.mail.onmicrosoft.com.