David Szpunar: Owner, Servant 42 and Servant Voice

David's Church Information Technology

January 2nd, 2008 at 1:46 pm

How to Schedule Weekly Email Reports to Staff?

in: E-Mail

We have at least one weekly email that gets sent out to staff members each week. It’s a short report that each person fills out and sends to their immediate supervisor. It is supposed to be sent out early Monday morning every week. The administrative assistant that has been tasked with sending these to everyone has an email scheduled in Outlook ahead of time, but not only do these have to be set up manually in advance every week, but they also have not been getting sent automatically.

I could troubleshoot Outlook and try to see why it’s not sending the messages properly. But I’d rather fix the problem at its source, farm the email sending to a server and allow the admin. assistant to turn off her computer over the weekend while I’m at it. I’d also like to make the email appear to come from each person’s supervisor; as it is hitting “Reply” sends replies back to the admin. assistant and not the supervisor. Reply-to would fix this, but that requires compounding the complexity by sending individual emails to each group of people with the same supervisor!

What I want is an email scheduler. I’m open to how it works, but my initial thoughts are:

  1. It should be web-based
  2. It should run under Apache on Linux, preferably with Perl or PHP (personal preference on all of those)
  3. It should send email using our Exchange server as the SMTP server
  4. It should let me set up multiple administrators who may create emails, fill them out with From (or Reply-to), Subject, and Body, and select the time of day and frequency of the mailing.
  5. It should be free and/or open source, or at least very inexpensive.
  6. It shouldn’t try to do everything, unless I want everything that it does (how’s that for a requirement?!).

I’m willing to budge on one or more of those items if given a good reason. I’ve done a bit of web searching and have located a tool called LBE Email Scheduler, which is shareware that sells for $20 for personal use or $200 for business use (a cursory glance did not immediately reveal whether non-profit pricing was available). It’s Windows-based and seems to have the features I’m looking for, but I like the idea of running a server app rather than sending client-side. Server apps are much easier to give multiple users access to. My searching has been hampered by a bazillion (approximate :-) results for spamming tools, which, although some of them could probably be modified to do what I want, really isn’t what I’m looking to support!

I’m willing to consider creative solutions. Possibilities I’ve thought of so far, in no particular order and not particularly connected:

  • Set up WordPress-based intranet and use the cForms II plugin to create the report as a form that could be posted on the intranet. Would still need a weekly reminder email with a link to the form.
  • Find another method that gets away from using email but still sends a reminder each week (I’d like to get away from email entirely, but I don’t think there’s a better way to at least remind people!)
  • A couple of other wacky things using combinations of other open source projects that are probably too complex and that I don’t feel like linking to properly right now :-)
  • Build my own script to the specifications I outlined above.
  • Use SharePoint. I mention this because I know someone will say something about it :-) I’ve used SharePoint Services for about 4 hours two to three years ago, and that’s it (I installed it because it came with Windows Server and I wanted to play with it). I’m not going to be at the SharePoint Training that Jason Lee is hosting for a few reasons, all of them unfortunate and mostly my fault, but it’s not worth crying over spilt milk. Regardless, I know WordPress and at the very least Perl and PHP, so unless there’s a really, really good Windows-based solution that’s free or very cheap, I need a lot more convincing.

Here end my ponderings thus far, and begin the comments from you! (Thank you in advance!)

  • 1

    Well, I’m partial to SQL Server Reporting Services myself, but this takes time to actually set up the reports and would cost money to purchase a copy of SQL Server 2005. You’d also need to write the reports. However, permissions and scheduling are relatively painless and web-based.

    Without knowing what sort of reports you’re trying to send, it’s hard to say what will work best for you. Are the reports simply files that need to be attached to an e-mail on some regular basis? Is it a form that someone will fill out and send to someone?

    Ultimately, this sounds like a job for a CMS system – those meet your requirements of Web-Based, runs on Linux/Apache (and MySQL), and would allow you to have people submit their entries and summarize. I know that WebEmpoweredChurch.com has a compilation of TYPO3, but Drupal and Joomla also have a pretty big presence in the CMS world.

    The great thing is that you can either take an existing extension that will handle these sorts of forms or tack on your own using PHP for the most part. A Cron job on the server can check for your schedules periodically. (WEC uses a concept called Gabriel to handle all of its requests – Gabriel needs to be scheduled, but extensions using it do not.)

    Having investigated these to some extent, you may want to install/download one and give it a try. Most of these have some form of “sampler” package in a WAMP format that will let you install it in a sort of virtual environment that is only available while you run the app.

    So for what I’d suggest:
    Reminders set up on a schedule to all “front-end” users (aka Staff). Send this out with a link to your page to fill out the form.

    A Page on the CMS with a form. Set this up with the appropriate scripts and you can either have the “Reply-to” set automatically or have them set the reply-to.

    Perhaps using something like “DirectMail” in Typo3 – http://typo3.org/documentation/document-library/extension-manuals/direct_mail/2.5.2/view/toc/0/
    could work for you as well. I’m sure that similar items exist in other CMS systems.


    Peter on January 2nd, 2008
  • 2

    Not that I’m much help here, but everytime I’ve needed to do something like this, I would just hardcode a Perl script to do it. Not so glamorous, and you have to change it everytime positions change, but it worked for me. Good luck in your search.

    Matthew Irvine on January 4th, 2008
  • 3

    Thanks for all your comments and links, Pete! And Matthew, that’s my first instinct as well…just throw up some Perl in a cron job and call it done. But that creates more work for someone…namely me! I don’t mind a bit more work up front to get the functionality without me being involved any more. However, I also don’t want to get too complex and implement a full-blown…anything…just to send emails! Unless it’s a part of something bigger (like an intranet). So…a bit more thinking to do I think. Thanks again to both of you.

    David Szpunar on January 4th, 2008