Sending Recurring Emails to Thousands, using Simplenews as a Backend

I recently had a rather unique project requirement on one of my sites: I needed to send out a weekly email to hundreds (soon to be thousands) of site users, with the same template each week, but with the latest data from the website.

Basically, what I wanted to do was create a View on my site of the latest 10-12 items, and have that view be sent out to everyone (along with the views header/footer) in HTML (with a plain text alternative, of course), but I didn't want to have to create a new newsletter by hand each week to do this (this is something at which the Simplenews module excels... minus the automation).

After debating over whether I should write my own module to do the dirty work of sending out batches of emails, using modules like Views Send along with Rules and Views Bulk Operations, and some other crazy ideas, I finally found a potent combination for sending out automated weekly newsletters in a highly performant and optimal way, using the following modules:

  • Simplenews (for queuing/sending emails, managing subscriptions)
  • Rules (for scheduling the newsletters)
  • Elysia Cron (for easy cron scheduling and performance)
  • Views (to build the body of the newsletter)
  • Mime Mail (to send HTML emails, and to be able to easily have a custom email-friendly stylesheet)

Making the Newsletter

I knew I wanted to have a simple list of a certain content type, laid out as a table (historically, tables are easy to work with in HTML emails—divs are out, and HTML5 is way, way out), with one field as one column, another field as another column, etc.

The easiest way to do this? Views, by a mile.

However, I needed an easy way to send the view I created to hundreds or thousands of people, so I investigated many different methods for sending views programmatically by email... Views Send + Rules seemed promising, but didn't work. Views Send + Rules + Views Bulk Operations could've possibly worked, but I found I would still have to find a way to cope with sending hundreds/thousands of emails.

I ended up using Simplenews—I simply created a new newsletter, used views_embed_view() to embed the view in the body of the newsletter, subscribed all users to the newsletter (using the simplenews_subscribe_user() function), and set up a function on hook_user() $op 'insert' that would automatically subscribe new users, whether they are created programmatically or by a real person.

Setting Up Cron

I wanted to have cron-like control over the times when the newsletter would be sent, but I also didn't want to have to set up a separate script or functionality in my custom module to be called by system cron—I wanted to be able to control the frequency of the emails (basically, Fridays at 10:30 a.m. or thereabouts) and do so with Rules.

Rules has a great conditional trigger that allows you to have your rule run any time Rules' cron task is run... of course, with out-of-the-box Drupal, you don't have a lot of control over individual cron tasks—you can simply call cron.php at a preset time, and all cron tasks are run. For performance reasons, this is a very bad idea on larger/active sites.

Enter Elysia Cron.

Using Elysia Cron, I had very fine-grained control on how often each module's cron task is run. Basically, I set up my system cron to hit Drupal's cron.php every minute... but Elysia Cron lets me define how often individual cron tasks run. I set Rules to run every 10 minutes (so I can make my own Rule run at 10:30 a.m. (which is on a 10 minute mark), and Simplenews to run every 7 minutes. This guarantees that I can get the Rule to set up the Simplenews newsletter, and Simplenews to start sending it, within a few minutes of 10:30 a.m.

I also have set other cron tasks to occur more or less frequently, for performance reasons: search indexing every 5 minutes, watchdog cron once a day, and update.module cron every week. This makes for a much snappier cron run across the board, and lets me send out more simplenews newsletters, since on simplenews cron runs, nothing else will take up precious seconds of my PHP script time limit.

Adding a Scheduled Rule

Now that I had Rules' cron firing every ten minutes, I had to make the Rule I set up work correctly.

The first step was to add a Rule that ran on the Event "Cron maintenance tasks are performed."

The condition I added was of the type "Execute custom PHP code," and I used the following to check whether it's 10:30 a.m. on Friday (and, in that case, return 'TRUE' — otherwise, 'FALSE'):

<?php
/* Get the current time/date/day */
$time = getdate();

/* Set up our comparison variables */
$current_day = $time['weekday']; // Sunday, Monday, Tuesday...
$24_hour_time = sprintf("%02d", $time['hours']) . sprintf("%02d", $time['minutes']);
$
24_hour_time = (int) $24_hour_time; // Cast $24_hour_time to an integer

/* CHANGE THESE TO SUIT YOUR FANCIES: */
$range = range(1027, 1033, 1); // Time is inside this window, in one-minute increments
$day = "Friday"; // Set this to whichever day of the week you'd like

/* Check if the day is correct, and the time is in our predefined range */
if (($current_day == $day) && in_array($24_hour_time, $range)) {
  return
TRUE;
}
else {
  return
FALSE;
}
?>

The action I added was again "Execute custom PHP code," and I used the following to tell Simplenews to re-send a particular newsletter (I could've created a new newsletter node, then had Simplenews send that, but it's easier for my purposes to simply tell Simplenews to re-send the original newsletter. Basically, changing the status of a row in simplenews_newsletters to '1' makes Simplenews send out the newsletter to everyone. (After the newsletter is sent, the status switches back to '2'):

<?php
$nid
= 1503; // nid of newsletter to re-send here.
$node = node_load($nid); // Load the newsletter node
db_query("UPDATE {simplenews_newsletters}
                SET s_status = '1'
                WHERE nid = %d"
, $nid);
module_load_include('inc', 'simplenews', 'includes/simplenews.mail'); // Function below is not in simplenews.module
simplenews_send_node($node); // Tell Simplenews to queue the node for sending
?>

I had tested all this code first using Devel's "Execute PHP Code" page (which is a godsend!).

HTML Email Woes

(or... Microsoft Outlook 2007 is worse than Internet Explorer 6 and much worse than Outlook 2003!)

I am using Mime mail to send out HTML-formatted emails (with plain-text equivalents), because I really want the emails to be readable to the people in this organization. Almost all the users have Outlook 2007, and a few are still running 2003.

So, I thought, "As long as the HTML email works in Mail (Mac OS X—which I use) and Outlook 2003, it should probably also work in Outlook 2007, which is newer than 2003. But Microsoft came back and bit me for the nth time. Outlook 2007 rendered HTML with the same engine as Microsoft Word, which doesn't allow half of the CSS2 selectors/features that were implemented even in Internet Explorer 6!

This meant that my custom styling I had added via Mime mail in my theme's mail.css file had to be rewritten. I had to use only the first selector in a series. For instance, in cck fields, classes for a particular item are usually defined as class="class1 class2 field-name-class3" - but Outlook 2007 only recognizes .class1, not .class2 or .field-name-class3. Yikes!

After a bit of fiddling and re-theming the view embedded in my email, I was able to get Outlook 2007 to show the email just as beautifully as Mail for Mac, Gmail online, or Outlook 2003.

The Finished Product

We now have a website capable of sending thousands of automated emails on a scheduled basis; users can opt-in or opt-out of emails (we have user accounts automatically set to be subscribed to this particular newsletter when they create an account (see this post for more); and our server's CPU/memory is barely nudged on cron runs when the emails are sent.

Comments

Change the name of the variable $military_time please. In almost any country of the world it is just the regular time (24 hour format), except for 3(!) countries in the world. Name the variable just 'time' or time_24, as the rest of the world would do.

@Richard - I've updated the code to use the variable $24_hour_time... but in America, it's almost more confusing to people to designate it something other than military time—old habits die hard... just saying :-)

@Jeff: great move. It's very much appreciated!

Hi,
I'm the (new) co-maintainer of Simplenews module, and we were discussing a few weeks ago how to improve Simplenews to be more usable in the future, so we're looking for well-described complex use cases to see how we could make the module evolve. Well, your post is amazingly well-described, and your need seems to be fairly common, judging by issues I see sometimes in Simplenews issue queue.
So, great work for your post, and we'll try to make the module better for the next time you need it ;)

Best regards,

@Simon Georges - thanks for all your help with this! I think it would be great if you could simply have a function in simplenews core that allows someone to set the status of any given newsletter node... other than that, maybe rudimentary rules support would be nice.

Also, I just found Simplenews Scheduler when I was browsing Simplenews-related projects, and it looks like that's another good avenue for those needing scheduled newsletter publishing capabilities...

Have you tried using the Mime Mail CSS Compressor (sub-module of Mime Mail) to automatically inline your styles? That should give you the broadest possible compatibility among email clients, at least if you limit yourself to the most common CSS rules.

I had tried using the CSS Compressor, but I had some trouble recompiling PHP with the DOM extension, so I scrapped my work on that and focused on getting the limited set of rules I used working in Outlook 2007.

Our offices are starting to upgrade to Outlook 2010, so hopefully this will be a non-issue as time progresses. Still, good to remember to use the Compressor if it's easily able to be run on your server!

Hello,

You're solution is exactly what I am trying to do however, it does not seem to change the status and resend the same e-mail. I was successfully able to send out 1 e-mail but haven't received another one. Are there any other steps that you included or that I should check for to debug? Thanks!

Just a note: I had to update the snippet that runs the simplenews_send_node() hook, due to some Simplenews changes in 2.x-dev. See: http://drupal.org/node/783308, specifically the comment by nostop8.

Sorry about not including that in the original post! I've updated the post with the fixed code.

I am sending out a newsletter everyday at 7 am. I set up simplenews cron to run every weekday at 7am and rules cron runs every hour every day at 7am as well. I am using your logic and creating a holiday array. If today matches any of the days in the array, it is not supposed to execute. However, everyday at 7am, it still sends out the e-mail. When I try to force the cron job to run, it reads the rule I created however not on the automated process first thing in the morning. However, if I change simplenews cron to run every hour, it accepts the rules only after it sends the initial e-mail at 7am. I'm not sure where it's failing.

my condition is

<?php
$ttdate
=getdate(date("U")); $tfdate =$ttdate[month] . $ttdate[mday] . $ttdate[year]; print $tfdate;

// Add as many holidays as desired.
$tholidays = array();
$tholidays[0] = "January172011"; // test day
$tholidays[1] = "February212011"; // test day
$tholidays[2] = "March242011"; // test day
$tholidays[3] = "March282011"; // spring recess
$tholidays[4] = "March282011"; // spring recess
$tholidays[5] = "March292011"; // spring recess
$tholidays[6] = "March302011"; // spring recess
$tholidays[7] = "April12011"; // spring recess

if (in_array($tfdate, $tholidays)) {
return
TRUE;
}
endif
?>

With this just set up as a condition, it wasn't working so I tried to include the same condition into the action as follows

<?php
$ttdate
=getdate(date("U")); $tfdate =$ttdate[month] . $ttdate[mday] . $ttdate[year]; print $tfdate;
// Add as many holidays as desired.
$tholidays = array();
$tholidays[0] = "January172011"; // test day
$tholidays[1] = "March242011"; // test day
$tholidays[2] = "March282011"; // spring recess
$tholidays[3] = "March282011"; // spring recess
$tholidays[4] = "March292011"; // spring recess
$tholidays[5] = "March302011"; // spring recess
$tholidays[6] = "April12011"; // spring recess
$tholidays[7] = "March252011"; // test day

if (in_array($tfdate, $tholidays)) {

}
else {

$nid = 1510; // nid of newsletter to re-send here.
$node = node_load($nid); // Load the newsletter node
db_query("UPDATE {simplenews_newsletters}
                SET s_status = '1'
                WHERE nid = %d"
, $nid);
module_load_include('inc', 'simplenews', 'includes/simplenews.mail'); // Function below is not in simplenews.module
simplenews_send_node($node); // Tell Simplenews to queue the node for sending
}
?>

Still no luck.

Am I supposed to be including the

<?php
 
?>
tags surrounding both the condition and the action?

I don't have time to diagnose your specific problem at this time, but I would say that a few well-placed dsm() calls with variables in them (perhaps using Devel module's Execute PHP page?) using your code above would help you verify that you have the right time/date values in the code.

Hi, I just posted a comment but am not seeing it displayed here. Is there a validation or approval process on comments?

No, it's just the Boost module - the page probably didn't dump out of the cache yet.

I am creating a similar situation, using simplenews and simplenews scheduler. The issue: If I have not content in the view, I don't want the simplenews newsletter sent. There is an option for code snippet to stop sending if no content, but it breaks the process and the next time there is content, nothing is sent (documented here: http://drupal.org/node/1522590). Any thoughts?

I haven't worked much with Simplenews Scheduler; you'd probably get more traction if you post an issue with your specific problem/request in that issue queue.

Recently I had some problems when sending e-mails with simplenews newsletter module for Drupal. While this is a very good module, sometimes made Drupal cron to fail and stuck. I also wanted full control on sending mails and to avoid some of the limitations of Drupal cron timings. Since I didn't have the time to enhance the simplenews module I did this script to be sure that my mails are sent. This script needs simplenews newsletter module and Drupal in order to work. It has been tested with Drupal 7 (but should work on any version) and simplenews 7.

The script works out of drupal and speacks to the database so its better for performance and mail control.

You can learn more and get the script from

http://tecorange.com/content/mail-send-addon-drupal-simplenews-newsletter

Hi!
I'm trying to follow your procedure, but i'm stuck when i try to embed the view in my simplenews template. The fields are showing up, but they don't use the custom templates of my view. Maybe you can help me ?
Thanks in advance !
Sarah