How Office Automation Saved my Morning

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:\Users\Scott Seely\Downloads\Chicago 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);
    }
}

Posted 04-05-2010 8:00 AM by Scott Seely

[Advertisement]

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)