Mail Merge for Email

If you have a spreadsheet that includes some or many email addresses and you want to send a personalized email to each of them, read on. You'll find a PHP script and instructions for doing exactly that.

The mailing is a merge of spreadsheet data into an email template for sending customized email.

You may have a short list of destinations where a holiday message is to be sent to, as an example. Or you may have a large list that are sent an email every week.

The PHP script uses the PHP built-in mail() function to send the emails.

The mail() function has been misused over the years and email deliverability has suffered when sent that way. The article contains tips on how to increase deliverability.

Note that the PHP script's actual mailing section, where mail() is employed, is a separate function which may be replaced with information to use a different emailing service. (We send Possibilities with MailGun.)

Here is an overview of how to do a mail-merge emailing. It assumes the PHP script is on the server and ready to use.

Export your spreadsheet as a CSV file. Upload the CSV file to your server where the PHP script can read it (more info further below).
Make a simple web page on your server to be an email template. It contains a placeholder for each of the outgoing email personalization points.
Run the PHP script.

For each email address in the CSV file, the PHP script makes a copy of the template and personalizes it. Then emails it out.

"Email Merge" is the name of the PHP script.

Email Merge can handle small lists and large lists. The exported CSV file may contain more than just the email addresses. There may be names, ids, and other information, all of which may optionally be merged into the outgoing email.

The web page that serves as the email template can handle placeholders formatted in any way you wish, so long as there is no space anywhere within the placeholder. These example placeholders for the recipient's email address provide an idea of how versatile Email Merge is: {{EMAIL}} <<EMAIL>> [[EMAIL]] ---EMAIL--- E.M.A.I.L

There are a number of things to consider when sending email from your server. And there are limitations. I'll mention what seem the most pertinent to me in the section about how to customize the Email Merge PHP script.

Before customizing Email Merge, let's mention the CSV file. Then the web page file that serves as the email template.

CSV File

Generally, a CSV file is an export from a spreadsheet. But CSV files can also be generated by backing up a MySQL table.

There is no practical limit to how many email addresses or rows the CSV file may have.

Here is an example of a short CSV file.

"will@example.com","Will Bontrager"
"name@isp.com","The Person's Name"
"AnotherName@isp.com","M. Smith"

The column order may be any order that suits you. When you customize the Email Merge PHP script, you will specify which column contains information for specific email-template placeholders

The Web Page Email Template

The email template is a simple HTML-formatted web page. It may contain image tags and clickable links. JavaScript, however, is generally ignored in email readers.

Placeholders are placed where personalized information is to be inserted.

Here is an example email template web page.

<!doctype html>
<html>
<head>
<title>An email</title><!-- title tag so the page can be validated if desired -->
<style type="text/css">
html, body { font-size:100%; font-family:sans-serif; }
</style>
</head>
<body>
<!-- Subject: Good news {{NAME}}! -->
<p>
Hi, {{NAME}}!
</p>
<p>
This is to let you know <b>good things</b> are happening!
</p>
<p>
<a href="https://example.com/register.php">Click here to register.</a> 
Remember, {{NAME}}, when you register use email address {{EMAIL}} so I know you are a valid registrant.
</p>
<p>
Sincerely,
</p>
<p>
Me!
</p>
<p>
NOTE: To unsubscribe permanently, <a href="https://example.com/unsubscribe?{{EMAIL}}">click here</a>.
</p>
</body>
</html>

In the above example, placeholders are bounded with double curly braces, {{EMAIL}} and {{NAME}}.

Other placeholders and/or styles of placeholders are acceptable so long as there is no space within any placeholder. When you customize the Email Merge PHP script, you will specify the placeholders for the software to look for.

Notice the email subject line embedded within the email template web page. The subject line is used in the outgoing email. It can be located anywhere within the BODY area of the web page email template.

<!-- Subject: Good news {{NAME}}! -->

The subject line is within an HTML comment tag. It begins with "Subject:" and what follows is what's used in the outgoing email. And yes, the subject line may be personalized.

The Email Merge PHP Script

Now we get to the workhorse. Email Merge is the script that personalizes and sends the email.

There is a lot of customization. The source code is below. Save it as file MergeAndMail.php or other *.php file name. After the source code, you'll find customization notes.

<?php
/*
Email Merge
Version 1.0
August 12, 2021
Will Bontrager Software LLC
https://www.willmaster.com/
*/

// Specify the locations of the HTML content and the CSV files.
$LocationOfHTMLfile = 'mergeme.html';
$LocationOfCSVfile = 'mergeme.csv';

// To update a text file of email addresses that have been sent, 
//    specify the file location here. (For no update, leave blank.)
$LocationOfUpdateFile = 'mailingupdate.txt';

// Specify the "From:" email address for the outgoing emails.
$FromEmailAddress = 'will@example.com';

// Optionally, specify the "From:" name for the outgoing emails.
$FromName = 'Willie B.';

// Specify the column number in the CSV file that contains 
//    the email address where the email is to be sent to. 
//    (The leftmost column is number 1).
$ToEmailColumn = 1;

// Specify a default subject line for the outgoing email.
$DefaultSubjectLine = 'News of importance!';

// If mailing must be slowed down, how many seconds to delay between each.
$SecondsDelay = 3;

// Specify the placeholders to replace in the HTML content and 
//    the column number of the CSV file to replace the placeholder.
// Make placeholder and column number sets one line each (the 
//    leftmost column is number 1).
// Separate the placeholder from the column number with a space.
// Start below the next line and insert above the "REPLACEDATA;" line.
$ReplaceData = <<<REPLACEDATA
{{NAME}} 2
{{EMAIL}} 1
REPLACEDATA;

// If your outgoing email needs any special header lines, 
//    specify them, a separate line for each, between the 
//    source code lines containing SPECIALHEADERS
$SpecialHeaders = <<<SPECIALHEADERS
List-Unsubscribe: https://example.com/unsubscribe?{{EMAIL}}
SPECIALHEADERS;

//////////////////////////
// End of customization //
//////////////////////////

$FromName = trim($FromName);
$FromEmailAddress = trim($FromEmailAddress);
$LocationOfCSVfile = trim($LocationOfCSVfile);
$LocationOfUpdateFile = trim($LocationOfUpdateFile);
$LocationOfHTMLfile = trim($LocationOfHTMLfile);
$ToEmailColumn = intval($ToEmailColumn);
$SecondsDelay = intval($SecondsDelay);
$ToEmailColumn--;

if(strpos($LocationOfHTMLfile,'/')===0) { $LocationOfHTMLfile = $_SERVER['DOCUMENT_ROOT'].(preg_replace('/^'.preg_quote($_SERVER['DOCUMENT_ROOT'],'/').'/','',$LocationOfHTMLfile)); }
else { $LocationOfHTMLfile = __DIR__."/$LocationOfHTMLfile"; }
if(strpos($LocationOfCSVfile,'/')===0) { $LocationOfCSVfile = $_SERVER['DOCUMENT_ROOT'].(preg_replace('/^'.preg_quote($_SERVER['DOCUMENT_ROOT'],'/').'/','',$LocationOfCSVfile)); }
else { $LocationOfCSVfile = __DIR__."/$LocationOfCSVfile"; }
if( strlen($LocationOfUpdateFile)>0 )
{
   if(strpos($LocationOfUpdateFile,'/')===0) { $LocationOfUpdateFile = $_SERVER['DOCUMENT_ROOT'].(preg_replace('/^'.preg_quote($_SERVER['DOCUMENT_ROOT'],'/').'/','',$LocationOfUpdateFile)); }
   else { $LocationOfUpdateFile = __DIR__."/$LocationOfUpdateFile"; }
}
else { $LocationOfUpdateFile = false; }

$Placeholders = array();
foreach( preg_split('/[\r\n]+/',trim($ReplaceData)) as $line )
{
   $line = trim($line);
   if( ! preg_match('/\w/',$line) ) { continue; }
   $ta = preg_split('/\s+/',$line);
   $number = array_pop($ta);
   $placeholder = preg_replace('/\s+\d+$/','',$line);
   $Placeholders[$placeholder] = $number;
}

$Headerlines = array();
foreach( preg_split('/[\r\n]+/',trim($SpecialHeaders)) as $line )
{
   $line = trim($line);
   if( ! preg_match('/\w/',$line) ) { continue; }
   $Headerlines[] = $line;
}
$Headerlines[] = 'MIME-Version: 1.0';
$Headerlines[] = 'Content-Type: text/html; charset=utf-8';
if( strlen($FromName) < 1 ) { $Headerlines[] = "From: $FromEmailAddress"; }
else { $Headerlines[] = "From: \"$FromName\" <$FromEmailAddress>"; }
$headerTemplate = implode("\r\n",$Headerlines);

$contentTemplate = file_get_contents($LocationOfHTMLfile);
if( strlen($contentTemplate) < 2 ) { echo "Unable to get $LocationOfHTMLfile"; exit; }

$subjectTemplate = false;
$match = array();
preg_match('/<!\-\-.*?Subject\:\s*([^(\-\->)]+)\-\->/',$contentTemplate,$match);
if( count($match)==2 ) { $subjectTemplate = trim($match[1]); }
if( ! $subjectTemplate ) { $subjectTemplate = $DefaultSubjectLine; }

$R = fopen($LocationOfCSVfile,'r');
if( ! $R ) { echo "Unable to open $LocationOfCSVfile"; exit; }
$W = false;
if(strlen($LocationOfUpdateFile)>0) { $W = fopen($LocationOfUpdateFile,'w'); }

echo '<p>Mailing is starting</p>';
for($i=0;$i<600;$i++) { echo '<span style="color:transparent;">. </span>'; } flush(); // To flush output buffer.
while( ($csvline = fgetcsv($R)) !== false )
{
   $to = $csvline[$ToEmailColumn];
   $subject = ReplacePlaceholders($subjectTemplate,$csvline);
   $content = ReplacePlaceholders($contentTemplate,$csvline);
   $headers = ReplacePlaceholders($headerTemplate,$csvline);
   SendThePersonalizedEmail($to,$subject,$content,$headers);
   if( $W )
   {
      fputs($W,date('r')." $to\r\n");
      fflush($W);
   }
   if( $SecondsDelay ) { sleep($SecondsDelay); }
}
echo '<p>Mailing is done</p>';

function ReplacePlaceholders($s,$csvdata)
{
   global $Placeholders;
   foreach( $Placeholders as $placeholder => $colnumber )
   {
      $colnumber--;
      $s = str_replace($placeholder,$csvdata[$colnumber],$s);
   }
   return $s;
}

function SendThePersonalizedEmail($to,$subject,$content,$headers)
{
   // This section uses the built-in mail() PHP email sending function.
   // You may modify this section to mail in a different way.
   mail( $to, $subject, $content, $headers );
}

?>

Notes about the customizations —

`$LocationOfHTMLfile`
`$LocationOfCSVfile`

These two variables are assigned the locations of the HTML content file and the CSV file, respectively.

If a file is in the same directory where MergeAndMail.php is installed, only the file name needs to be specified. Otherwise, specify the file location relative to your domain's document root. As an example, the absolute URL https://example.com/test/file.html would translate to /test/file.html for its location relative to document root.

`$LocationOfUpdateFile`

And update file is a list of timestamps and destination emails that MergeAndMail.php has sent. It can come in handy when you have a large list and have the process throttled to comply with the hosting company's requirements. (See $SecondsDelay further below.)

An update file is optional.

If you want MergeAndMail.php to update a list of timestamps, specify the file name and location here. When the file exists, it may be loaded into a browser window. The window may then be refreshed from time to time to see the emailing progress.

`$FromEmailAddress`

Specify the "From:" email address for the outgoing emails. If the email address domain name (the part after the "@" character) is the same as the domain name where MergeAndMail.php is installed, email delivery may be more reliable.

`$FromName`

The "From:" name for outgoing emails is optional.

`$ToEmailColumn`

In the CSV file specified in $LocationOfCSVfile (further above), one of the columns contains the email address where emails are to be sent to.

Specify that column number. The leftmost column of the CSV file is number 1 and the rest of the columns are numbered consecutively.

`$DefaultSubjectLine`

Specify a subject line for the outgoing email that will be used if no subject is provided in the web page email template. (See The Web Page Email Template section for information about how to specify the email subject within the email template.)

`$SecondsDelay`

Some hosting companies limit how many emails may be sent from the domains they host. The value for $SecondsDelay can be used to throttle the outgoing email frequency.

Specify the number of seconds to pause between each email that is sent.

To limit outgoing email to 100 per hour, specify 36 seconds as the $SecondsDelay value. The calculation is:

3600 ÷ 100 = 36

(3,600 is number of seconds per hour.)

For limiting to 1000 per day:

86400 ÷ 1000 = 86

(Result rounded down to an integer. 86,400 is number of seconds per day.)

$SecondsDelay may have a value of 0 (zero) to omit throttle.

`$ReplaceData = <<<REPLACEDATA`
…
`REPLACEDATA;`

At $ReplaceData is where you specify the placeholders in your web page email template and the column numbers of the CSV file to be used when replacing the placeholders.

The information for $ReplaceData is inserted between the two red lines, where you see the … dots.

For each unique placeholder, specify the placeholder, a space, and the column number on a line, one line per set. Example:

{{NAME}} 2
{{EMAIL}} 1

The above specifies that the each instance of the placeholder {{NAME}} in the web page email template is to be replaced with the information in column 2 of the CSV file. And, it specifies that the placeholder {{EMAIL}} in the web page email template be replaced with the information in column 1 of the CSV file.

Your email placeholders may be a format different than the above example so long as there is no space within the placeholder.

Note: To avoid having to change this section with every mailing, ensure your CSV columns always contain the same type of information as you specify here.

`$SpecialHeaders = <<<SPECIALHEADERS`
…
`SPECIALHEADERS;`

If you wish to send special header lines within the outgoing email, in addition to what is automatically sent, specify them here.

Most emails have many header lines. Email reading software generally hides most of them to reduce clutter.

Your email reading software may have a menu item you can use to reveal all the header lines. The menu item may be "View Source" or "Full Headers" or something else that suggests the available data.

There is much information available on the web about email header lines. One that I think is not too overly techy is Understanding an email header.

The source code for MergeAndMail.php contains one special header line:

List-Unsubscribe: https://example.com/unsubscribe?{{EMAIL}}

The List-Unsubscribe line provides a means for online email services to send an unsubscription notice when their user requests it. Having a List-Unsubscribe line may enhance email deliverability.

You may remove List-Unsubscribe (give it a valid value if you keep it) and/or add your own special header lines.

That is all for the customization.

Upload the customized MergeAndMail.php to your server. Test it with one or very few email addresses in a CSV file, and a web page email template with a placeholder, or several placeholders.

MergeAndMail.php personalizes a copy of the web page email template with information in the CSV file. Then sends it.

If you wish to use a mailing system other than the built-in PHP mail() function, the code in the last function of MergeAndMail.php may be replaced with code to use your preferred mailing system or service.

(This article first appeared with an issue of the Possibilities newsletter.)

Will Bontrager

We only suggest and recommend what we believe is of value. As remuneration for the time and research involved to provide quality links, we generally use affiliate links when we can. Whenever we link to something not our own, you should assume they are affiliate links or that we benefit in some way.