Karmic Code Monkey

I had procrastinated on mailing out information for the Chicago Give Camp. I wanted to make sure the email went out early in the week and in the morning so that people would probably read it. This morning, I was determined to get the mailing out. I started out brute forcing this thing, but the tedium hit me fast. Switching between apps, double checking that I copy/pasted the right info, and that I didn’t have any screw ups got to me fast-after about 6 messages. I had another 84 to go.

The email I was sending out was a classic form letter: insert recipients name in one spot, insert my info in a few others, and send. I wanted all the email history to show up in my Outlook ‘Sent Items’ and I wanted the message to look nice (aka HTML formatting). In about 20 minutes, I had the task completed and the email sent. Here is  what I did:

1. I saved the form letter as HTML and made sure that the fields to replace were easily identified. I was going to use string.Replace(string, string) to fill in the form fields. I added the HTML file to the solution and told VS to copy the file to the output directory on build. The file isn’t a resource, just an asset that shows up in a well known location.

2. I identified where I needed to stop and start in the spreadsheet. I was on row 8 and needed to go through row 89. I didn’t need a general purpose solution, I needed something that saved me from mind-numbing tedium, so I hard coded these values.

3. I identified which columns contained the information I needed and ran a quick test to get the values out of the cells from Excel.

4. I tested a couple of times with sending the email  to myself instead of to the actual recipient. This was a low bar unit test that was easy to remove once things appeared to work.

5. I changed the code to send to the actual recipient and, once all the messages went out, marveled at a job well done!

As software developers, we frequently write tools that are meant to be general purpose. Some days, it’s fun to just write a piece of throwaway code that doesn’t solve any grand problems, but does allow you to get a one time task done quickly. Today was one of those days.

Here is the code, in case you are curious. Cut and paste into your own applications at your own risk. This code is not production ready, and other disclaimers that basically mean run  this code in a debugger.

static void Main(string[] args)
{
    var excelApp = new Microsoft.Office.Interop.Excel.Application();
    var outlookApp = new Microsoft.Office.Interop.Outlook.Application();
    var spreadsheet = excelApp.Workbooks.Open(

          @"C:UsersScott SeelyDownloadsChicago Charities.xlsx");
    Microsoft.Office.Interop.Excel.Worksheet worksheet = spreadsheet.Worksheets[1];
    string originalEmail = File.ReadAllText("GiveCampLetter.htm")
          .Replace("[Insert your name]", "Scott Seely")
          .Replace("[insert your email]", "xxxx@xxxxx.xxx")
          .Replace("[insert your preferred contact number]", "847-xxx-xxxx");
    for  (int i = 8; i < 90; ++i)
    {
        dynamic realnameCell = worksheet.Cells[i, "C"];
        var realname = realnameCell.FormulaLocal;
        dynamic emailCell = worksheet.Cells[i, "F"];
        var email = emailCell.FormulaLocal;
        if (string.IsNullOrEmpty(realname) || string.IsNullOrEmpty(email))
        {
            continue;
        }
        Microsoft.Office.Interop.Outlook.MailItem mail = outlookApp.CreateItem(

          Microsoft.Office.Interop.Outlook.OlItemType.olMailItem);
        mail.Subject = "Midwest Give Camp";
        mail.To = email;
        mail.HTMLBody = originalEmail.Replace("[insert contact name]", realname);
        mail.Send();
        Console.WriteLine("{0}: {1}", realname, email);
    }
}