Using cron to send bulk email on a shared host

  |   By  |  4 Comments

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 . The -f tells the php compiler to run the file. Don’t forget to change (username) to your username.

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!

name

ABOUT THE AUTHOR - ANDY MOYLE

Andy Moyle is a church leader and web developer. His biggest project is the Church Admin WordPress plugin and app. He also runs, mainly so he can eat pizza.