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:
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:
-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.xls
message.txt
image.jpeg
The data worksheet of our sample Excel file looks like this:
And here is a detail of the options worksheet of our sample Excel file:
For this example we just use a single plain text message template,
message.txt:
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:
…
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’[email protected]’,
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:
…
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’[email protected]’,
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:
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:
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!
Del.icio.us
Digg
Reddit