Monday, November 7, 2011

Sending an Email Merge from Microsoft Access using PHP

I was recently faced with a network restriction on sending out emails to more than 20 recipients from Outlook when we had over 800 members on our Access database who required to be mailed notifications for meetings and newsletters. I did try a number of mail merge options including Worldmerge by Coloradosoft, but I felt that what we needed was a simple hosted solution that avoided the need for an onsite mail server. In the long term we need to move to a service like Mailchimp, but everyone would (presumably) need to verify their opt in again and I am not sure how we would keep it in synch with our database.

Because of these difficulties I decided that as a short term solution I would generate recipients as CSV files from a database query and send the emails with a PHP script hosted on a web server. This worked and here is how I did it. I hope this is easy to follow. Its not intended to be a downloadable script solution, but a way of showing how the issue can be addressed fairly simply. It might help teach the basics of the PHP mail function too.

Generating the CSV file
Set up a query in Access which selects the recipients, but shows only first name, last name and email address. By incorporating the persons name into the email it makes it  more personal and I have found I get more feedback.

The Mailer Script
For simplicity, the bulk email script exists as three separate parts. The first (mailer.html) is an html form; the second (writefile.php) is a script for writing the address data to a file and the third (mailer.php) processes it and sends out the emails.There is a fourth file called addressfile.txt which is the list of names and email addresses.

This form I called “mailer.html” and consists of two forms on the one page.
The first sends certain values to the mailer script (from email address, real name, subject, message and footer). the second allows the comma separated values to be written to a text file on the server. here is the html for the two forms on mailer.html (aplogies for the use of tables - this wad done in a hurry):

<form method="post" action="mailer.php" name="mailer">
<table style="text-align: left;" border="0" cellpadding="2" cellspacing="2">
<td align="undefined" valign="undefined">From:
<td align="undefined" valign="undefined"><input name="from" value=""></td>
<td align="undefined" valign="undefined">Realname:&nbsp;&nbsp;</td>
<td align="undefined" valign="undefined"><input name="realname" value="Your Real Name"></td>
<td align="undefined" valign="undefined">Subject:&nbsp;&nbsp;</td>
<td align="undefined" valign="undefined"><input name="subject"></td>
<textarea cols="70" rows="20" name="message"></textarea><br>
<textarea cols="70" rows="10" name="footer">
<input name="btn" value="Submit" type="submit">
<form method="post" action="writefile.php" name="mailer"><br>
<textarea cols="70" rows="20" name="list"></textarea><br>
<input name="btn" value="Submit" type="submit">

You could set a default value for any of these fields so you don’t have to keep retyping them every time you send an email.

Write File Script
Open the CSV file in a text editor and copy and paste the values into the form. Press the submit button and it will write these to a file called addressfile.txt.

I called this script writefile.php

$filename = "addressfile.txt";
// file will need chmod to 666 or 777
$text = $_POST['list'];
$fp = fopen ($filename, "w");
if ($fp) {
fwrite ($fp, $text);
fclose ($fp);
echo ("File written");
else {
echo ("File was not written");

You should create an empty file called addressfile.txt and upload it to the same directory as the script and CHMOD it to 666 to give the scruipt the intial parameters to work from. A simpler solution would be to allow the upload of the CSV file from the form. I didn’t do it this way at this stage as I did not know for sure what the file permissions situation would be on writing a file. I will be replacing it shortly with that system. Some web servers will write files from PHP as a different user and you might not be able to delete or modify them so this is not an exact process.

Mail Processor Script
Clicking on the submit button in the top form of mailer.html posts the form values to the script mailer.php (below), opens up addressfile.txt, reads each line, explodes the entry into an array of first name, last name and email address thensends individual emails to each of these recipients. For simplicity it only handles plain text emails at the moment. It adds a “Dear $firstname” line to the beginning of the email.

There are some in-line comments that help explain what the script is doing:

//assign post data from the form to variables
$from = $_POST ['from'];
$realname = $_POST['realname'];
$subject = $_POST ['subject'];
$message = $_POST ['message'];
$footer = $_POST ['footer'];
//this first section processes the body of the email message
//PHP will escape any apostrophes or quotation marks so first we have to strip out the slashes
$message = stripslashes($message);
//to retain formating we limit lines to 70 characters and prevent leading full stops being deleted
$message = wordwrap($message,70,"\n");
$message = str_replace("\n", "\n", $message);
$message = str_replace("\n.", "\n..", $message);
//add a blank line at end of message to separate it from the footer
$message = $message . "\r\n\r\n";
//headers for the email
$headers = "From: \"".$realname."\" <".$from.">\r\n";
$headers .= "Return-Path: <".$from.">\r\n";
$headers .= "Content-type: text/plain; charset=iso-8859-1\r\n";
//open the text file containing the recipients
$file = fopen("addressfile.txt", "r") or exit("Unable to open file!");
//read the file one line at a time until the end is reached
  $dbentry = fgets($file);
//split the line from the text file into firstname, lastname and recipient
  $delimiter = ",";
  $splitcontents = explode($delimiter, $dbentry);
  $firstname = $splitcontents[0];
  $lastname = $splitcontents[1];
  $recipient = $splitcontents[2];
//generate first line and add blank line below it
//$firstline = "Dear $firstname $lastname,\r\n\r\n";
//version for just first name - you only need one of these lines
$firstline = "Dear $firstname,\r\n\r\n";
//join the first line, body text and footer together
  $bodytext = $firstline . $message . $footer;
//send the email
//show success message
  echo "Message sent to $recipient. <br />";
//close the file

Limitations and improvements
This all works OK under PHP5 and should do under PHP4 as well. Its a quick and dirty piece of programming and its not claiming to be well written, but it works.

Some issues that might need addressing:

  • The write file process needs to be changed to uploading the CSV file rather than writing the data. 
  • You may need to list your web server as a valid sender in your SPF DNS record to allow mail to be received by some mail services (depending how tightly your SPF records are set up).
  • With a long mailing list it takes a while for the confirmation page to appear. It would be better to open a window with javascript and write each successful send as a separate line.
  • Moving to a proper templating system where [[Firstname]] would be replaced by $firstname wherever that appeared. Could then move to having any number of fields in the query.
  • Allowing HTML if I could find a way of editing html effectively in a browser window.

Some web hosts limit the number of emails that can be sent using PHP so it might not work for you. I am sending emails to people who are members of an organisation and have opted in (in writing!) to receive emails. You should not use scripts like this for spamming. If you are a spammer you probably know more about this sort of programming than I do so you will have learned nothing from reading this.