If a recipient using AOL, Yahoo! Or Hotmail (see here for a full list) deems your email to be spam may click on the “This is Spam” button. This generally logs a black mark against the sender’s domain/IP and also sends a detailed notification to the sender’s abuse@ email account (assuming the sender’s signed up to the FBL). The accepted practise is that then the sender unsubscribes the recipient. If the sender then sends further emails to that recipient then bigger black marks are logged against them and their reputation falls. It’s a good system and if you’re handling your own email broadcasts in-house then you’ll want to sign up and configure them. Word to the Wise has a comprehensive list of all available email FBLs with their respective signup pages.
However, a problem arises when you send 150,000+ emails per week. You can then expect to receive around 200+ FBL emails per week, a frustrating unsubscribe routine. This is made worse by the completely informal FBL email structure. Yahoo! send their FBL email with a text file attachment that lists the recipient. Hotmail however just attach the original email in .msg format. AOL though are the most annoying – they attach the original .msg like Hotmail but strip out the recipient information and replace it with “<redacted>”. In the FBL body they state the original messageid, hence you need to query your SMTP server for the recipient’s email address. The inconsistency between FBL implementations just means it all takes up more precious time and it needs to be automated…but can it? It can! And I’ll show you how I did it.
Firstly, a brief outline of what I do. I send emails using Arialsoft’s Campaign Enterprise mated to SQL Server 2008 with Exchange as the SMTP server. I use Outlook as my general mail client. All run on Server 2008/Windows 7.
The FBLs are sent from Hotmail etc to abuse@ and processed by an open Outlook session to append their attachments .dat/.msg/.txt {etc} to their message body. They’re then read by a terrific application called Email2DB by Parker Software which reads the email body and extracts recipient information. Email2DB then runs a query against the SQL Server to unsubscribe the recipient. Email2DB is not free, however once you get to grips with its interface you’ll realise that you can perform some pretty funky things with it and well worth the price.
So where do you start? Assuming you’ve signed up to (and configured/confirmed functional) all the email FBLs…
Firstly, to get around AOL’s annoying <redacted> feature, alter your email templates to include the recipient’s email address somewhere in the body. For example, amend the footer to read something like ‘This email has been sent to {email_address} because…’
Now download and install Outlook Redemption – this allows us to run VB scripts within Outlook that can access the data within .msg format attachments. To get things up and working get the developer edition.
Next setup a rule in Outlook that moves emails received from the FBL addresses to a specific folder – “FBL Queue”. Now setup a further rule, beneath the previous rule, that performs a script. Use the script below, you will need to set Outlook’s macro security to low to run the code.
AppendEmail.vb (v.0.1)
The code, if you don’t know VBS, pulls out the attachments and stores them in a temporary folder. It then reads them and appends them as plain text to the original email body. Inside the message body’s are of course the recipient’s email addresses.
Now download, install and start-up Email2DB.Set it up to store the messages however you wish, it doesn’t really matter how, I chose to use the internal database. Setup an account called “FBLs” and point it, using MAPI to read your FBL Queue folder. Set it to move read emails to another folder called “Processed FBLs”. Now we need to setup the triggers.
Setup a new trigger called “Hotmail” and set the “From address” to “staff@hotmail.com”. Click on the tab called ‘Field Extraction’, click ‘Add Field’ and paste a copy of your newsletter/triggered email HTML in the textbox on the right. Now, in the textbox on the left titled ‘Look For’ type in the text preceding the recipient’s email address (red in the example below). Then in the ‘Extract Data’ tab, ‘Until These Characters’ box, type in the text following the user’s email address (left in the example below).
“This email has been sent to example@emailaddress.com because…”
If you now click on the General tab the email address should be highlighted, indicating that it will extract that data. Click OK to go back to the Hotmail trigger and OK again to go back to the trigger icons list. In exactly the same way setup further triggers to extract data from AOL and Yahoo! FBLs only remembering to change the from address for filtering to scomp@aol.net etc.
Once they’re setup, go back into each trigger and ensure the ‘After this trigger has processed a message’ is set to the following trigger. So for example, once the ‘Hotmail’ trigger has been executed, it follows on to process the ‘AOL’ trigger etc.
Now we have a system that executes a script on each FBL entering the inbox, appends its .msg attachment to the message body and extracts the recipients’ email address. All that’s left is to setup Email2DB to do something to unsubscribes people!
In my SQL database I simply insert the records into a table called fbl_unsubscribes with fields email_address, fbl_domain and dt_created. I then use a stored procedure within the database to change the subscribe list later. This means I can create historical reports to view abuse notifications by domain over time. Email2DB supports a wide range of database protocols, for details on how to deal with SQL inserts see here.
So there you have it – a 100% automated feedback loop unsubscribe system.
Limitations? My Outlook script is rather weak because it stores msg attachments as draft emails (required for reading). It could do with deleting them afterwards. It also requires an open Outlook session. Improvement suggestions always welcome!
If a recipient using AOL, Yahoo! Or Hotmail (see here for a full list) deems your email to be spam may click on the ‘This is Spam’ button. This generally logs a black mark against the sender’s domain/IP and also sends a detailed notification to the sender’s abuse@company.com email account (assuming the sender’s signed up to the FBL). The accepted practise is that then the sender unsubscribes the recipient. If the sender then sends further emails to that recipient then bigger black marks are logged against them and their reputation falls. It’s a good system and if you’re handling your own email broadcasts in-house then you’ll want to sign up and configure them. Word to the Wise has a comprehensive list of all available email FBLs with their respective signup pages.
However, a problem arises when you send 150,000+ emails per week. You can then expect to receive around 200+ FBL emails per week, a frustrating unsubscribe routine. This is made worse by the completely informal FBL email structure. Yahoo! send their FBL email with a text file attachment that lists the recipient. Hotmail however just attach the original email in .msg format. AOL though are the most annoying – they attach the original .msg like Hotmail but strip out the recipient information and replace it with “<redacted>”. In the FBL body they state the original messageid, hence you need to query your SMTP server for the recipient’s email address. The inconsistency between FBL implementations just means it all takes up more precious time and it needs to be automated…but can it? It can! And I’ll show you how I did it.
Firstly, a brief outline of what I do. I send emails using Arialsoft’s Campaign Enterprise mated to SQL Server 2008 with Exchange as the SMTP server. I use Outlook as my general mail client. All run on Server 2008/Windows 7.
The FBLs are sent from Hotmail etc to abuse@ and processed by an open Outlook session to append their attachments .dat/.msg/.txt {etc} to their message body. They’re then read by a terrific application called Email2DB by Parker Software which reads the email body and extracts recipient information. Email2DB then runs a query against my SQL Server to unsubscribe the recipient. Email2DB is not free, however once you get to grips with its interface you’ll realise that you can perform some pretty funky things with it and well worth the price.
So where do you start? Assuming you’ve signed up to (and configured/confirmed functional) all the email FBLs…
Firstly, to get around AOL’s annoying <redacted> feature, alter your email templates to include the recipient’s email address somewhere in the body. For example, amend the footer to read something like ‘This email has been sent to {email_address} because…’
Now download and install Outlook Redemption – this allows us to run VB scripts within Outlook that can access the data within .msg format attachments. To get things up and working get the developer edition.
Next setup a rule in Outlook that moves emails received from the FBL addresses to a specific folder – “FBL Queue”. Now setup a further rule, beneath the previous rule, that performs a script. Copy and paste the below, you will need to set Outlook’s macro security to low to run the code.
<code>
The code, if you don’t know VBS and don’t enjoy reading comments, pulls out the attachments and stores them in a temporary folder. It then reads them and appends them as plain text to the original email body. Inside the message body’s are of course the recipient’s email addresses.
Now download, install and start-up Email2DB.Set it up to store the messages however you wish, it doesn’t really matter how, I chose to use the internal database. Setup an account called “FBLs” and point it, using MAPI to read your FBL Queue folder. Set it to move read emails to another folder called “Processed FBLs”. Now we need to setup the triggers.
Setup a new trigger called “Hotmail” and set the “From address” to “staff@hotmail.com”. Click on the tab called ‘Field Extraction’, click ‘Add Field’ and paste a copy of your newsletter/triggered email HTML in the textbox on the right. Now, in the textbox on the left titled ‘Look For’ type in the text preceding the recipient’s email address (red in the example below). Then in the ‘Extract Data’ tab, ‘Until These Characters’ box, type in the text following the user’s email address (left in the example below).
“This email has been sent to example@emailaddress.com because…”
If you now click on the General tab the email address should be highlighted, indicating that it will extract that data. Click OK to go back to the Hotmail trigger and OK again to go back to the trigger icons list. In exactly the same way setup further triggers to extract data from AOL and Yahoo! FBLs only remembering to change the from address for filtering to scomp@aol.net etc.
Once they’re setup, go back into each trigger and ensure the ‘After this trigger has processed a message’ is set to the following trigger. So for example, once the ‘Hotmail’ trigger has been executed, it follows on to process the ‘AOL’ trigger etc.
Now we have a system that executes a script on each FBL entering the inbox, appends its .msg attachment to the message body and extracts the recipients’ email address. All that’s left is to setup Email2DB to do something to unsubscribes people!
In my SQL database I simply insert the records into a table called fbl_unsubscribes with fields email_address, fbl_domain and dt_created. I then use a stored procedure within the database to change the subscribe list later. This means I can create historical reports to view abuse notifications by domain over time. Email2DB supports a wide range of database protocols and more can be read here.
So there you have it – a 100% automated feedback loop unsubscribe system.
Limitations? My Outlook script is rather weak because it stores msg attachments as draft emails (required for reading). It could do with deleting them afterwards. It also requires an open Outlook session. Improvement suggestions always welcome!
