In my last article for Linux Journal, (http://www.linuxjournal.com/content/best-wishes-new-year) I shared some of my New Year's Resolutions. One of those resolutions was to communicate more regularly with my friends and family. In this article, I'm going to describe one of the first steps I took toward making this happen.

A few months ago, I left a job that I had been at for 14 years, so I had accumulated quite a few people at work that I cared about. As I was saying good-bye to many of my soon to be ex-coworkers, I promised to get an email out to them as soon as I could to let them know how I was doing. That was, like I said, a few months ago and the letter never went out; life just seemed to get in the way and I never got around to it.

I knew I didn't have time to write an individual letter to each of my friends. Doing the old copy-paste routine is actually pretty time consuming and more error prone than you might imagine; it's real easy to forget to add a subject, or to not get all of the message into the clipboard, etc. What I needed was an efficient, error proof means of sending out an email to all of my friends and family. I'm a programmer by trade, so the solution was obvious. I understand that there are unscrupulous people out there who have built a business model out of this very same solution, but that's not my intention. I just don't want to have to hand-address 100 emails, or worse yet, that many envelopes!

As I have written before, (http://www.linuxjournal.com/content/managing-your-life-egroupware) I use E-Groupware to store just about every piece of information that is important to me. So, I had a ready-made list of friends and their email addresses. I guess I could have used EGW's email distribution mechanism, but I don't use EGW's email client and wanted something a bit more manageable. What I ended up doing was creating a new contact category. Then I assigned this category to the people I wanted to receive my newsletter. As time goes on, I'll be able to manage my “Christmas Card List” with EGW. Nice.

Writing a program that speaks SMTP is one thing. My program needed to format the message properly for a variety of mail clients. What's worse is that I wanted to send a brief text message and attach my newsletter as a .pdf file. I also wanted to attach a family picture as a .jpg file. My initial thoughts were that this was going to be difficult, even in perl. Man, was I wrong! It turned that by using the MIME::Lite module, it was almost trivial. Let's look at some code.

     1  #!/usr/bin/perl

     2

     3  use DBI;

     4  use MIME::Lite;

     5

     6  $dbh = DBI->connect("dbi:Pg:dbname=db_name;host=example.com”, "db_user", "passwd");

     7

     8  $sth = $dbh->prepare("select cat_id from egw_categories where cat_name='Christmas'");

     9  $sth->execute();

    10  ($id) = $sth->fetchrow_array();

    11

    12  open FILE, "./2008.txt";

    13  @data = <FILE>;

    14  $data = join("", @data);

    15

    16  $clause = "(cat_id = \'$id\' or cat_id like \'$id,%\' or cat_id like \'%,$id,%\' 
	or cat_id like \'%,$id\')";

    17

    18  $sth = $dbh->prepare(" select contact_email from egw_addressbook where $clause

    19          and contact_email is not null

    20                  union

    21          select contact_email_home from egw_addressbook where $clause 

    22                  and contact_email_home is not null");

    23  $sth->execute();

    24

    25  while ((@a) = $sth->fetchrow_array()) {

    26          print "X: $a[0]\n";

    27

    28          $a[0] = "mdiehl\@diehlnet.com";

    29

    30          $message = MIME::Lite->new(

    31                  From            => "mdiehl\@diehlnet.com",

    32                  To              => $a[0],

    33                  Subject         => "Greetings!",

    34                  Data            => $data,

    35                  );

    36

    37

    38          $message->attach(

    39                  Type            => "application/pdf",

    40                  Encoding        => "base64",
    41                  Path            => "./2008.pdf",

    42                  Filename        => "2008.pdf"

    43                  );

    44

    45          $message->attach(

    46                  Type            => "image/jpeg",

    47                  Encoding        => "base64",

    48                  Path            => "./2008.jpg",

    49                  Filename        => "2008.jpg"

    50                  );

    51

    52          MIME::Lite->send("sendmail", "smtp.example.com", timeout=>20);

    53

    54          $message->send;

    55  }

Lines 1-7 of the program are mere boilerplate. In lines 8-10, I look up the index number for the contact category named “Christmas.” We'll use that index number later on, so we keep in a variable.

Lines 12-14 are where I read in an external file for use as the brief message I mentioned earlier. This method allowed me to create the file with my favorite editor and even spell-check it before I sent it. I also didn't have to hard-code my message in my program, which is a double plus.

I'll come back to line 16 in a moment, but first let's talk about the SQL in lines 18-23. Some of my friends have business email addresses and some have personal email addresses. Some have both. EGW allows me to store these addresses separately. I wanted to write a single SQL query that would fetch all of these addresses in a single column so that my perl script would be more intuitive. What I did is wrote a query for the home address and a query for the business address and took the union of the two queries. Most of the selection logic is tied up in that $clause variable that I defined on line 16.

I hate the way EGW associates a category with a contact. EGW uses the cat_id field to store a comma-delimited list of categories that a contact belongs to, and the list isn't even sorted. So, in line 16, I build a clause that looks for our category id in the cat_id field, wherever it may appear in the field. The value may be the only one on the list, in which case, there will be no commas. It may also be the first, middle, or last entry on the list. I assigned this clause to a variable because both halves of the SQL union needed the same logic and simply copying and pasting it in would obscure the logic. It's ugly, but it works. Now that you know what it's supposed to do, it's not too hard to understand.

On line 25, we start to loop over each record so we can send them an email. Line 26 is just a print statement so I can be sure the program is running properly. Line 28 is a bit more interesting. Here I overwrite the data fetched from the database with my own email address. This is a debugging trick. I didn't want to debug this program and risk sending a bunch of garbage emails to people I claim to care about. Just before I sent my letter out, I removed this line and the program worked as expected... and tested.

In lines 30-35, we create a new email message with the MIME::Lite module. We also assign values to the From:, To:, and Subject: headers. The text message that we read in earlier is put into the email at this point, using the Data field.

Then we create 2 file attachments in lines 38-50. The first one is the .pdf file while the second one is the .jpg file. There are a few things that are noteworthy about this part of the code. First, I had to assign a mime time to each attachment, as well as an encoding scheme. This much is pretty intuitive from looking at the code.

Another thing that is worth mentioning about these attachments is that I was able to specify the name of an external file that I wanted attached, instead of having to read the file in myself. Also, I was able to specify a “suggested” filename for the attachment in the event that one of my friends actually wants to save the file to their hard drive.

Lines 52-55 are where I send the message out the door and close the main loop. I had initially tried to use my own mail server as the gateway, but ran into a problem. My server, for some reason, was refusing to relay the messages. Certainly, I want to be a good Net Citizen and not be an open relay, but I should have been able to use my server to send regular emails. I never resolved this issue and simply used my ISP's relay.

When I was contemplating this program, I was a bit concerned that my friends might find it a bit tacky that I essentially wrote a mass-mailer in order to send them an otherwise impersonal email greeting. So far, no body seems either to have noticed, or cared that the message wasn't written directly to them. I've received replies indicating that they were happy to receive my message and enjoyed the family picture. Overall, I think the process was a success. There's also the added benefit of having the program and contact list ready for use the next time I want to say hi to my friends or customers.