Using cron to send bulk email on a shared host
I’m running a conference booking system at www.togetherconference.com and we need to send out lots of emails to delegates and potential delegates. Our host uk2.net only allows 100 emails per hour from the website, so I needed to find a way of queueing them and sending in batches…
First a mysql db table…
CREATE TABLE IF NOT EXISTS `email` ( `to` varchar(500) NOT NULL, `from_name` text NOT NULL, `from_email` text NOT NULL, `copy` text NOT NULL, `subject` varchar(500) NOT NULL, `message` text NOT NULL, `attachment` text NOT NULL, `sent` datetime NOT NULL, `email_id` int(11) NOT NULL auto_increment, PRIMARY KEY (`email_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
I’ve not set up attachments yet, so that table continues everything else we need.
Here’s a function to queue messages
function QueueEmail($to,$subject,$message,$copy,$from_name,$from_email,$attachment) { $sqlsafe=array(); $sqlsafe['to']=mysql_real_escape_string($to); $sqlsafe['from_name']=mysql_real_escape_string($from_name); $sqlsafe['from_email']=mysql_real_escape_string($from_email); $sqlsafe['subject']=mysql_real_escape_string($subject); $sqlsafe['message']=mysql_real_escape_string($message); $sqlsafe['attachment_name']=mysql_real_escape_string($attachment); $sqlsafe['copy']=mysql_real_escape_string($copy); $sql="INSERT INTO together_email.email (`to`,`from_name`,`from_email`,`copy`,`subject`,`message`,`sent`,`attachment`)VALUES('{$sqlsafe['to']}','{$sqlsafe['from_name']}','{$sqlsafe['from_email']}','{$sqlsafe['copy']}','{$sqlsafe['subject']}','{$sqlsafe['message']}',NOW(),'{$sqlsafe['attachment']}')"; $result=mysql_query($sql) or die(mysql_error()); if($result) {return TRUE;}else{return FALSE;} }
I tend to call it with some basic messaging
if(QueueEmail($to,$subject,$message,$copy,$from_name,$from_email,$attachment)) { echo "Message to $to queued
"; } else { echo "Message to $to NOT queued
"; }
So that handles creating messages and storing them in the database queue.
Here’s my cronemail.php file
//email sending script from db queue define("DB_SERVER", "#");//replace # with server name define("DB_USER", "#");//replace # with mysql username define("DB_PASS", "#");//replace # with mysql password define("DB_NAME", "#");//replace # with db name $attachment=array(); // connect to database $db=mysql_connect(DB_SERVER,DB_USER,DB_PASS)or die("Couldn't connect to database"); mysql_select_db(DB_NAME)or die("Couldn't open database".mysql_error() ); //initialise phpmailer script require("class.phpmailer.php"); $mail = new PHPMailer(); //Grab 80 messages $sql="SELECT * FROM email ORDER BY email_id LIMIT 0,80"; $result=mysql_query($sql); if(mysql_num_rows($result)>0) {//only proceed if emails queued in db while($row=mysql_fetch_assoc($result)) { $mail->SetFrom($row['from_email'], $row['from_name']); $mail->IsHTML(true); $mail->AddAddress($row['to']); if(!empty($row['copy']))$mail->AddAddress($row['copy']); if(!empty($row['attachment'])) { $path=$row['attachment']; $mail->AddAttachment($path, $name = "", $encoding = "base64",$type = "application/octet-stream"); $attachment[]=$path; } $mail->Subject = $row['subject'] ; $mail->Body=$row['message']; if($mail->Send()) { //successful send, so delete from DB $sql="DELETE FROM email WHERE email_id='".mysql_real_escape_string($row['email_id'])."'"; mysql_query($sql)or die(mysql_error()); } $mail->ClearAllRecipients();//clears all recipients $mail->ClearCustomHeaders();//clears headers for next message } } foreach($attachment AS $file) { unlink($file);//gets rid of uploaded attachments from temporary folder }
The code is well commented – make sure you replace the hashes!
You will also need the excellent phpmailer script
Now we need to set up our shared host to execute cronemail.php every hour!
Go to your CPANEL and click on cron jobs – on my version there is a standard link and an advanced link
I use standard!
Common settings gives you some common options – once an hour is what we want (although for testing once every five minutes would be less tedious)
The command to run took a bit longer to get right! A lot of blog posts get this bit wrong!!!
You need to find out your php path – most likely to be /usr/local/bin/php
Then you need the path to the script – best put in a non-public accessable area or in the document root if you must!
/home/(username)/public_html/cronemail.php
So now you can type
/usr/local/bin/php -f /home/(username)/public_html/cronemail.php
into the command to run field and press
Hope it helps…
Here it is in a zip file with a tester script index.php –emailstuff. Don’t use index.php in a production environment as there is no form checking!