Ecommerce software home
Shopping Cart Software Forum for Ecommerce Templates
 
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

Find us on Facebook Follow us on Twitter View our YouTube channel
Search our site
 All Forums
 Technical
 Advanced Tips and Tricks - NO QUESTIONS PLEASE
 Removing Mailing List Bounces with GMail
Author « Topic »  

Vince
Administrator

Spain
42674 Posts

Posted - 12/23/2020 :  02:58:57  
It's important to keep your mailing list clean and that includes removing bounces for invalid recipients, and of course doing this one by one can be a bit tedious. For those that use GMail as we do, here is a way to do it quickly. It's quite complicated and requires access to your database via phpMyAdmin or SQL Server Management Studio.

Firstly, make sure you have your bounces grouped together in one folder in GMail.

You need to view GMail in text mode and you can do that following the instructions here...
https://support.google.com/mail/answer/15049?hl=en

Now copy ALL your subject lines IN ONE GO on the whole page, which will be a list of something like...
Bounces Ecommerce Templates News August 2020 - Address not found Your message wasn't delivered to xxx@yyyy.com because the address couldn't be found, or is unable to receive mail. The response from the
Bounces Ecommerce Templates News August 2020 - Address not found Your message wasn't delivered to xxx@yyyy.com because the address couldn't be found, or is unable to receive mail. The response from the
Bounces Ecommerce Templates News August 2020 - Address not found Your message wasn't delivered to xxx@yyyy.com because the address couldn't be found, or is unable to receive mail. The response from the

Now we need to get rid of the extra text so we need a text editor that can handle regular expressions. I use Notepad++ which is available for free download here...
https://notepad-plus-plus.org/downloads/

Paste your subject lines into a new Notepad++ file and it will look like a complete mess. But we're now going to clean up the email addresses with a regular expression. To do that, first press <Ctrl>+h to open the search/replace dialog in Notepad++
Under Search Mode check for "Regular Expression" and also check ". matches newline"
For Find What: enter...
because the.*?delivered to
Please note there is a space before and after that text.
For Replace With enter:
\n
...which signifies a newline.
Now click on Replace All and you should see that most of the text is removed leaving just the email addresses.

Now we need to create an SQL Query from the email addresses. To do that use we can use Notepad++ Regular expressions again.
Search for "$" and replace that with "'," (the dollar sign being "end of line")
Search for "^" and replace that with "'" (the carat sign being "beginning of line")

Your email addresses should now look like..

'xxx@yyy.com',
'xxx@yyy.com',
'xxx@yyy.com',
'xxx@yyy.com',

Now we just need to "top and tail" that for the SQL query like this...

DELETE FROM mailinglist WHERE email IN ('xxx@yyy.com',
'xxx@yyy.com',
'xxx@yyy.com',
'xxx@yyy.com')

(Note the final comma is replace with a closing bracket too.)

Now just copy and paste that into an SQL Query window in phpMyAdmin.

If there is call for this I could add a feature to the mailing list admin page so that a list of bare emails could be entered for removal from the list and that would cut out the need for direct access to the database.

Vince


Click Here for Shopping Cart Software
Click Here to sign up for our newsletter
Click Here for the latest updater

dbdave
ECT Moderator

USA
10373 Posts

Posted - 12/23/2020 :  04:03:40  
Good one Vince.
I would love to be able to do this with the Thunderbird email client.
I have never been able to export anything from there though.

David

Vince
Administrator

42674 Posts

Posted - 12/23/2020 :  05:16:40  
Hi Dave
This is just copy and pasting from the GMail Web interface so maybe something similar is possible with Thunderbird?

Vince

Click Here for Shopping Cart Software
Click Here to sign up for our newsletter
Click Here for the latest updater

John M
Advanced Member

459 Posts

Pre-sales questions only
(More Details...)

Posted - 09/23/2021 :  09:55:28  
It is far far far simpler to use awk and grep on a linux terminal or on windows command line using gnu tools or powershell and output to a sql file then call mysql from the command line including the sql file you want to run... We do this all the time for every order to automatically update the tracking numbers which we get directly from the shipping couriers log file.

John

  « Topic »  
Jump To:
Shopping Cart Software Forum for Ecommerce Templates © 2002-2022 ecommercetemplates.com
This page was generated in 0.02 seconds. Snitz Forums 2000