Intelligent mailing list (password protected) - it sends the email message to the list of the email addresses.

email_export_from_Drupal_Simplenews.sql

------------ Email export from the Drupal Simplenews module

--- get Simplenews private key

select * from drupal_variable where name='simplenews_private_key';

-- +------------------------+------------------------------------------+
-- | name                   | value                                    |
-- +------------------------+------------------------------------------+
-- | simplenews_private_key | s:32:"00000000000000000000000000000000"; |
-- +------------------------+------------------------------------------+



-- tid - drupal_taxonomy_term_data.tid used as newsletter category = 99 

select s.mail,
concat(substr(md5(concat(s.mail,'00000000000000000000000000000000')),1,10),b.snid,'t99') as unsubscribe_token
from drupal_simplenews_subscription b,
drupal_simplenews_subscriber s
where b.tid=99 and status=1
and s.snid=b.snid; 

-- link for unsubscribe
-- http://example/en/newsletter/confirm/remove/[unsubscribe_token]t99

i_mailinglist.py

#!/usr/bin/python2.7
#          Copyright Martin Mevald 2011 - 2018.
# Distributed under the Boost Software License, Version 1.0.
#    (See accompanying file LICENSE_1_0.txt or copy at
#          http://www.boost.org/LICENSE_1_0.txt)

"""
Version: 4.0

Intelligent mailing list (password protected) - it sends the email message 
to the list of the email addresses.

Procedure: 

1. User sends the email to the address for example: iml-RANDOM-STRING@site .

Text expansion in mail body and attachments: 
        $0 - email address
        $1 - first parameter in CSV file email.csv
        $2 - second parameter
        ...
        $9 - 9th parameter

Content-Transfer-Encoding: "quoted-printable" and "base64" are prohibited in the main email header. 

Variables:
        mailEncoding - first mail encoding in the mail encoding autodetection
        (It detects correctly only emails with the content-transfer-encoding: 8bit)

2. Script i_mailinglist.py reads the message, it daemonises and it sends 
the message to the list of the test email addresses (first batch). 
The email header field 'To:' contains the recipient email address.

3. The message (document) {'_id': [Process ID], 'status': 'Ready'} is written to the queue (CouchDB).

4. Admin stores the document in CouchDB (sends a message):

        Abort - stop the script
{'_id': 'cmd_[Process ID], 'command': 'abort'}

        Continue - continue with emails in the second batch.
{'_id': 'cmd_[Process ID], 'command': 'continue'}

5. Script deletes the documents [Process ID] and cmd_[Process ID].

6. Admin receives the status report.

7. User can edit the mailing list (files emails.csv, test_emails.csv).
For example: The PHP-IDE (http://online-php.com) is installed on the web server.

Installation example:

1) Install the python package couchdbkit

pip install couchdbkit

2) Edit the configuration in the class ConfigHolder.

3) Install the script:

adduser iml-RANDOM-STRING

cp i_mailinglist.py /home/iml-RANDOM-STRING/
chown -R iml-RANDOM-STRING.iml-RANDOM-STRING /home/iml-RANDOM-STRING/i_mailinglist.py
chmod ogu+rx /home/iml-RANDOM-STRING/i_mailinglist.py

PYTHONPATH=/home/iml-RANDOM-STRING:$PYTHONPATH  
python -c 'import i_mailinglist'

vim /home/iml-RANDOM-STRING/emails.csv /home/iml-RANDOM-STRING/test_emails.csv
chown iml-RANDOM-STRING.iml-RANDOM-STRING /home/iml-RANDOM-STRING/emails.csv  /home/iml-RANDOM-STRING/test_emails.csv
chmod o-rw /home/iml-RANDOM-STRING/emails.csv 

echo '|/home/iml-RANDOM-STRING/i_mailinglist.py' >/home/iml/.forward
chown iml-RANDOM-STRING.iml-RANDOM-STRING /home/iml/.forward 

"""

import md5
import fcntl
import smtplib
import sys
import os
import traceback
import email.utils
import email.header
import syslog
import datetime
import time
import csv
import couchdbkit

class ConfigHolder:
        # utf8 name <martinmev@ubuntu>,param1,param2,param3
        # first batch
        testEmailsFile = "/home/iml/test_emails.csv"
        # second batch
        emailsFile="/home/iml/emails.csv"

        # parameters for the csv.reader
        # dialect:excel means the comma separated file 
        # details: http://www.python.org/dev/peps/pep-0305/

        csvReaderParams={'dialect':'excel'}
        #csvReaderParams={'dialect':'excel','delimiter':';'}

        # CouchDB
        # Server
        couchDBUri = 'https://USER:PASSWORD@SERVER'
        # DB Name
        couchDBName = 'comm'
        # Wait after request (seconds)
        queueRequestWait = 5

        # mailEncoding - first mail encoding in the mail encoding autodetection
        # (It detects correctly only emails with the content-transfer-encoding: 8bit)
        mailEncoding = 'utf8'
        maxExpansion = 10
        statusEmail="martinmev@ubuntu"
        smtpServer=('localhost',25,'ubuntu',30) # ('server',port,'local hostname',timeout_seconds)
        smtpDebugLevel = 0
        smtpStarttls = False
        smtpLogin=() # ('login,'password')
        wait = 1 # seconds

class SendMail(ConfigHolder):
        def mail(self,f,to,message):
                server = smtplib.SMTP(*self.smtpServer)
                server.set_debuglevel(self.smtpDebugLevel)
                if self.smtpStarttls:
                        server.starttls()
                if len(self.smtpLogin):
                        server.login(*self.smtpLogin)
                server.sendmail(f,to,message)
                server.close()

class Status(ConfigHolder):
        msg=[]
        def __init__(self):
                syslog.openlog('i_mailinglist',syslog.LOG_PID,syslog.LOG_MAIL)
        def message(self,m,error=False):
                p = syslog.LOG_ERR if error else syslog.LOG_INFO
                syslog.syslog(p,m)
                self.msg.append(datetime.datetime.now().isoformat()+' '+m)
        def __str__(self):
                return '\r\n'.join(self.msg)+'\r\n'
        def trace(self):
                t = traceback.format_exc()
                for x in  t.split('\n'):
                        self.message(x,True)

        def send(self,sendMail):
                f = self.statusEmail
                t = self.statusEmail
                message = "From: %s \r\n" \
                        "To: %s \r\n" \
                        "Content-Type: text/plain; charset=UTF-8\r\n" \
                        "Content-Transfer-Encoding: 8bit\r\n" \
                        "Subject: Status report\r\n\r\n" % (f,t)
                message += self.__str__()
                sendMail.mail(f,t,message)
                self.msg = []

class Emails(ConfigHolder):
        emails = set()
        emailsExpansion = {}
        def __init__(self, eFile):
                fd=open(eFile,'r')
                fcntl.flock(fd,fcntl.LOCK_EX)
                csvReader = csv.reader(fd,**self.csvReaderParams)
                for row in csvReader:
                        if len(row)==0:
                                continue

                        email = row[0].strip().decode('utf8');

                        if email=='':
                                continue

                        self.emails.add(email)

                        y = []
                        for field in row:
                                y.append(field.strip().decode('utf8'))
                        self.emailsExpansion[email] = y

        def genGetEmailAddress(self):
                for e in self.emails:
                        yield({'email':e,'expansionFields':self.emailsExpansion[e]})

        def expandMessage(self,expansionFields,text):
                count = 0
                for x in expansionFields+(self.maxExpansion-len(expansionFields))*[u'']:
                        text = text.replace('$'+str(count),x)
                        count+=1
                return text

                

class ParseAddress:
        name = None
        email = None
        headerEntry= None
        def __init__(self,addr):
                (self.name,self.email) = email.utils.parseaddr(addr)
                try:
                        h =  str(email.header.Header(self.name,'utf8'))
                        self.headerEntry=email.utils.formataddr((h,self.email))
                except UnicodeDecodeError:
                        pass

class MailMessage(ConfigHolder):
        _data= None
        header = []
        message = []
        encoding = None
        _encodings = None
        _encodingsAll = ['utf_8']

        def _getEncoding(self):
                for en in self._encodings:
                        try:
                                for d in self._data:
                                     x=d.decode(en)
                                self.encoding=en
                                return
                        except UnicodeDecodeError:
                                pass
                raise RuntimeError("Mail - no encoding detected")

        def __init__(self):
                self._encodings = [self.mailEncoding] + self._encodingsAll
                self._readMessage()

        def _readMessage(self):
                self._data = sys.stdin.readlines()
                self._getEncoding()
                isHeader = True
                for xline in self._data:
                        line = xline.decode(self.encoding)
                        sLine = line.strip()
                        if isHeader and(sLine==''):
                                isHeader=False
                                continue
                        elif isHeader:
                                x=line[:1]
                                if x!=x.strip():
                                        self.header[len(self.header)-1].append(sLine)
                                else:
                                        self.header.append([sLine])
                        else:
                                self.message.append(line.rstrip())


        def _compare(self,entry,value):
                return (value[:len(entry)]).lower()==entry.lower()

        def getHeaderEntry(self,entry):
                for x in self.header:
                        if self._compare(entry,x[0]):
                                return x
                return None

        def returnDate(self):
                return 'Date: '+email.utils.formatdate(None,True)

        def strHeaderEntry(self,entry):
                try:
                        l = self.getHeaderEntry(entry)
                        return ('\r\n\t'.join(l))+'\r\n'
                except TypeError:
                        return ''

        _mandatoryEntries = ['From:','Subject:','Content-Type:']
        _entries = ['MIME-Version:','X-Priority','X-MSMail-Priority:',\
        'Importance:']  
        
        def createMessage(self,to):
                msg =''
                for x in self._mandatoryEntries:
                        s = self.strHeaderEntry(x)
                        if s=='':
                                return ''
                        msg+=s

                msg+='To: '+to+'\r\n'
                msg+=self.returnDate()+'\r\n'

                cTE_ = self.strHeaderEntry('Content-Transfer-Encoding:')
                cTE = cTE_.lower()
                if (cTE.find('base64')!=-1) or (cTE.find('quoted-printable')!=-1):
                        raise RuntimeError(cTE_) # Content-Transfer-Encoding: 
                
                msg += "Content-Transfer-Encoding: 8bit\r\n" 
                for x in self._entries:
                        msg+=self.strHeaderEntry(x)                

                msg+='\r\n'+'\r\n'.join(self.message)+'\r\n'

                return msg.encode(self.encoding)

class EmailSender(ConfigHolder):
        emails = None
        mailMessage = None
        sendMail = None
        status = None
        fromEmail = None

        def __init__(self,emails,mailMessage,status):
                self.emails = emails
                self.mailMessage=mailMessage
                self.sendMail=SendMail()
                self.status=status
                
                f = self.mailMessage.strHeaderEntry('From:')
                if f =='':
                        raise RuntimeError("No 'From:' header")
                parsed = ParseAddress(f)
                self.fromEmail = parsed.email

        def sendEmails(self):
                self.status.message("Sending emails...")
                gen = self.emails.genGetEmailAddress()



                for g in gen:
                        eml=g['email']
                        parsed = ParseAddress(eml)
                        if parsed.headerEntry == None:
                                 self.status.message(eml+" - invalid charset",True)
                        message = self.mailMessage.createMessage(parsed.headerEntry)
                        messageUni = message.decode(self.mailMessage.encoding)
                        try:
                                messageUni = self.emails.expandMessage(g['expansionFields'],messageUni)
                                message=messageUni.encode(self.mailMessage.encoding)
                                self.sendMail.mail(self.fromEmail,parsed.email,message)
                                self.status.message(eml.encode('utf8')+' - OK')
                        except:
                                self.status.trace()
                                self.status.message(repr(eml)+' - ERROR - '+repr(g['expansionFields']))
                        
                        time.sleep(self.wait)

class Daemonizer():

        def _fork(self):        
                if (os.fork())>0:
                        sys.exit(0)

        def __init__(self):
                self.stdin = '/dev/null'
                self.stdout = '/dev/null'
                self.stderr = '/dev/null'

                self._fork()
                os.chdir("/") 
                os.setsid() 
                os.umask(0) 
        
                self._fork()
                sys.stdout.flush()
                sys.stderr.flush()
                si = file(self.stdin, 'r')
                so = file(self.stdout, 'a+')
                se = file(self.stderr, 'a+', 0)
                os.dup2(si.fileno(), sys.stdin.fileno())
                os.dup2(so.fileno(), sys.stdout.fileno())
                os.dup2(se.fileno(), sys.stderr.fileno())
                                
def main():

        status=Status()
        try:
                mailMessage=MailMessage()
                Daemonizer()
                to = ParseAddress(mailMessage.strHeaderEntry('To:'))

                srv = couchdbkit.Server(uri=ConfigHolder.couchDBUri)
                if not srv.info():
                        raise RuntimeError('CouchDB - no connection!')
                comm = srv.get_db(ConfigHolder.couchDBName)

                testEmails = Emails(ConfigHolder.testEmailsFile)        
                emailSender = EmailSender(testEmails,mailMessage,status)
                emailSender.sendEmails()
                del emailSender

                pid = os.getpid()
                comm.save_doc({'_id': str(pid), 'status': 'Ready'})
                cmdName = 'cmd_%d' % (pid,)
                while True:
                        time.sleep(ConfigHolder.queueRequestWait)
                        try:
                                cmd = comm.open_doc(cmdName) 
                        except couchdbkit.ResourceNotFound:
                                continue

                        try:
                                command = cmd.get('command','').lower()
                        except AttributeError:
                                continue
                        if command in ('continue', 'abort'):
                                break


                try:
                        comm.delete_doc(str(pid))
                        comm.delete_doc(cmdName)
                except couchdbkit.ResourceNotFound:
                        pass
                if command == 'abort':
                        raise RuntimeError('Abort.')

                emails = Emails(ConfigHolder.emailsFile)
                emailSender = EmailSender(emails,mailMessage,status)
                emailSender.sendEmails()

        except SystemExit:
                return
        except:
                status.trace()

        status.message('---END---')
        status.send(SendMail())
        
if __name__ == '__main__':
                main()

license_1_0.txt

Boost Software License - Version 1.0 - August 17th, 2003

Permission is hereby granted, free of charge, to any person or organization
obtaining a copy of the software and accompanying documentation covered by
this license (the "Software") to use, reproduce, display, distribute,
execute, and transmit the Software, and to prepare derivative works of the
Software, and to permit third-parties to whom the Software is furnished to
do so, all subject to the following:

The copyright notices in the Software and this entire statement, including
the above license grant, this restriction and the following disclaimer,
must be included in all copies of the Software, in whole or in part, and
all derivative works of the Software, unless such copies or derivative
works are solely in the form of machine-executable object code generated by
a source language processor.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE, TITLE AND NON-INFRINGEMENT. IN NO EVENT
SHALL THE COPYRIGHT HOLDERS OR ANYONE DISTRIBUTING THE SOFTWARE BE LIABLE
FOR ANY DAMAGES OR OTHER LIABILITY, WHETHER IN CONTRACT, TORT OR OTHERWISE,
ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
DEALINGS IN THE SOFTWARE.