ExcelMailer - Excel-driven mailing of personalized multipart MIME messages

ExcelMailer is a small command line business tool for emailing personalized messages with attachments. Data and options are specified via an Excel file and the message content via plain text or HTML file templates. For each data row in the Excel file ExcelMailer prepares and sends, when explicitly requested, a personalized MIME email message and its attachments if any. ExcelMailer is OS-independent -- there is no need for Windows, or Outlook, or an Exchange or Lotus Notes server, or any other special infrastructure. The only requirement is that either your local machine must be able to connect to a running SMTP server or it must be able to post to a companion web script that can. The Excel file may be processed on Windows, Mac OS X, or on any variety of Linux/Unix systems. ExcelMailer is GPL3 licensed free software.

Highlights

  • The text message may be in plain text or in HTML or in both i.e. in HTML with an alternative plain text version.
  • May define any number of data columns (only to is required) that are then all accessible in the message templates with ${ column heading }.
  • Rich and flexible set of options, that may be conveniently saved in the Excel file itself as well as be explicitly overridden on the command line.
  • Automatic recognition of input text encodings. Output text is always encoded in UTF-8.
  • Extensive data validation before sending any messages. No emails are sent unless explicitly requested and all data checks pass. No error goes silent.
  • Data rows can be tagged, for test runs or selective re-runs.
  • All actions, such as sending of an email, are logged to a file named ${ excel file }.log, placed alongside the Excel file.

Excel File Format

The Excel workbook must contain sheets named data and options -- other sheets are allowed and ignored. Rows whose first cell starts with "#" are considered to be comment rows, thus not interpreted as data. Blank rows are also ignored. The first data row in a sheet is taken to be the heading row, and all other rows that follow are the actual data. Note the following constraints on data values:

  • In all cells, any leading or trailing whitespace is stripped.
  • Email values may be specified in any of the following forms: [email protected], <[email protected]>, some description <[email protected]>, "Some description" <[email protected]>. Any preceeding descriptive text may not contain any "," (comma) characters.
  • All file paths, e.g. for messages or attachments, are either absolute or relative to location of Excel file itself. File paths may not contain any "," (comma) characters.

Data worksheet

The data sheet may contain as many columns as desired, and in any order. A few data columns, if present, are given special meaning:

tags Space-seperated tags for this row. to Comma-separated list of email addresses. Required. cc Comma-separated list of email addresses. bcc Comma-separated list of email addresses. attachments Comma-separated list of files to attach. May be specified either as absolute paths, or relative to location of the Excel file.

Options worksheet

The options sheet is provided for convenience, to be able to save (and comment) any preferred options along with the specific Excel file itself. The options sheet consists essentially of one heading row and one data row -- this means any rows that follow are automatically taken as comments. All options in the options sheet may be explictly overridden on the command line and running with the -h or --help switch, the full list of available command line options is displayed:

-h, --help show this help message and exit -f FROM, --from=FROM the value of the From: header -r REPLY-TO, --reply-to=REPLY-TO the value of the Reply-to: header -s SUBJECT, --subject=SUBJECT the ${templated} subject of the email message -m MESSAGE-TEXT-FILENAMES, --message-files=MESSAGE-TEXT-FILENAMES the filenames for the message texts, comma separated -t TAG, --tag=TAG process only data rows with this tag -w SECONDS, --wait=SECONDS number of seconds to wait between each message -c USER@SERVER:PORT, --connection-smtp=USER@SERVER:PORT SMTP connection, as "[user@]server:port" -p URL, --post-to-url=URL send message by HTTP POSTing to URL, value of connection-smtp is passed as parameter -l EMAIL-ADDRESS, --log-to-bcc=EMAIL-ADDRESS optional email address to log all messages via bcc -o COMPOSED-MESSAGE-FILENAME, --log-to-file=COMPOSED-MESSAGE-FILENAME save composed message under this ${templated} filename -x, --execute-send actually execute mailing of composed MIME messages -n, --nulls allow null/empty data values in message templates -q, --quiet do not write log messages to stdout -d, --details-conditions conditions for using, modifying and distributing -v, --version show program version and exit

Message templates

Message templates are simple text or HTML files. All cells in the data sheet may be accessed from templates via the heading name for the column, using the syntax ${ heading } -- this requires that headings be valid python identifiers, i.e. must consist of alphanumeric and "_" characters. The subject, that is specified in the options sheet, may also be personalized i.e. may include any ${ heading } and for convenience is also exposed to templates as "${subject}". To output a literal "$" character in a message, the template text must double it, i.e. "$$".

Example

It is recommended to create a work folder for each mailing. In that folder you will place the Excel file with all the data, the one or two template files for the message and all referenced attachments. Note that message files and attachments may be freely organized in sub-folders if you prefer. In this simple example we will use a folder called mailing_txt, a single plain text template and an image attachment. The folder contents therefore are:

mailing_txt/ mailing_txt.xls message.txt image.jpeg

The data worksheet of our sample Excel file looks like this:

Sample data worksheet in Excel file

And here is a detail of the options worksheet of our sample Excel file:

Sample options worksheet in Excel file

For this example we just use a single plain text message template, message.txt:

${salutation} ${first}, please see the attached document! Best, Mario ps: ${comment}

This is a command line tool. For Windows this means we first run a cmd dos window, and then change directory to where we have copied the ExcelMailer program. We always do test runs first:

$ python excelmailer.py mailing_txt/mailing_txt.xls ... ExcelMailer started [13360] Running with: options(from_='Me <[email protected]>', reply_to='', subject='${first}, your image file', message_files='message.txt', tag='test', wait=10, connection_smtp='', post_to_url='', log_to_bcc='', log_to_file='', execute_send=False, nulls=0, quiet=0) Processing rows tagged with [test] 1: row(tags=u'test', salutation=u'yo', first=u'mario', last=u'ruggier', to=u'mario@ruggier.org', cc='', bcc='', attachments=u'image.jpeg', comment=u'A test with an attached jpeg.') 2: SKIPPING: row(tags='', salutation=u'Hi there ', first=u'John', last=u'Dough', to=u'"John Dough" <[email protected]>', cc='', bcc='', attachments=u'image.jpeg', comment=u"It's been a while!") ExcelMailer finished [13360]

OK, no errors! But notice that this run took into account only those rows tagged with test (row 2 is skipped). To include all rows we can either go back and modify the tag option in the Excel file, or we can just override this setting by running with an empty --tag option, i.e. -t "". For the sake of brevity, we assume that the test run on all rows also gives no errors, and so we can safely proceed to send the messages. This is done by running with --execute-send, or -x. Thus, putting both these options together, we run the following command:

$ python excelmailer.py mailing_txt/mailing_txt.xls -t "" -x ... ExcelMailer started [13363] Running with: options(from_='Me <[email protected]>', reply_to='', subject='${first}, your image file', message_files='message.txt', tag='', wait=10, connection_smtp='', post_to_url='', log_to_bcc='', log_to_file='', execute_send=False, nulls=0, quiet=0) Processing all rows 1: row(tags=u'test', salutation=u'yo', first=u'mario', last=u'ruggier', to=u'mario@ruggier.org', cc='', bcc='', attachments=u'image.jpeg', comment=u'A test with an attached jpeg.') Adding attachment(file=image.jpeg, ctype=image/jpeg, encoding=None) Message sent [ok] 2: row(tags='', salutation=u'Hi there ', first=u'John', last=u'Dough', to=u'"John Dough" <[email protected]>', cc='', bcc='', attachments=u'image.jpeg', comment=u"It's been a while!") Adding attachment(file=image.jpeg, ctype=image/jpeg, encoding=None) Message sent [ok] ExcelMailer finished [13363]

Download and Installation

First, please ensure that you have Python 2.5 (earlier versions may work) and the xlrd package installed on your system. To check this, at the shell or dos prompt, executing the following command should give no errors:

python -c "import xlrd"

Note for Windows XP users: you are highly recommended to install a Python distribution that is more comfortably customized for Windows, e.g. this description of how to install ActivePython. If you are new and curious about Python, take a look at the Python Tutorial, or this other one at Dive Into Python, or even How to Think Like a Computer Scientist.

Download the ExcelMailer package:

excelmailer-0.2.zip (180Kb, 2007-12-23)

Unzip the distribution archive to a comfortable working area, e.g in ~/excelmailer or if on Windows in C:\My Documents\ExcelMailer and change directory into it. You may then follow step-by-step the example detailed above. To create a new mailing, first just duplicate any of the provided sample mailings folder and then modify it as needed.

Questions, Feedback, Suggestions, Contributions

ExcelMailer is free software. Should you have any question about using it please go ahead, formulate it nicely and ask it via email.

In particular, bug reports or suggestions for improvements are particularly appreciated. If you wish to help make ExcelMailer better, please look at as many similar tools as you can and then suggest any improvements you'd wish to see. And, if you like ExcelMailer, please tell others about it!