Creating a mail merge application for attachments

This will be a bit of an experiment – my first development related post without source code or specific technical detail. Will it be useful to anyone, or helpful to me to write? Time will tell, but I have an urge to (finally) post on this topic but it’s not really appropriate or feasible to demo the application in detail.

I’m a complete noob at mail merging. I’ve never had to create one nor do I foresee having to in the future. However, some people in the business-side of my organization use it extensively and the MS Office suite does the job just fine. Unless you need to include attachments.

In my case, the entire purpose was attachments. The text content of the emails could actually be static, but my organization needed a way to send an email to 500+ VIPs with a personalized highly-sensitive attachment containing compensation data and other things. Regularly. This process actually took a couple of administrative assistants days to go through – one email at a time, double triple quadruple-checking that the right document was attached to the right recipient.

Lets automate that

It’s interesting (and yet not surprising) that the application I developed to automate this has been one of the biggest success stories. It’s incredibly basic – hell – not even very well written at first, but it does an important job in a “high-stakes” type of setting. How’s it set up?

Two components are required

1. A collection of files to be sent to each individual recipient
2. A list of all recipients

(The recipient list is required in this implementation so the end-user can select/deselect who to send an email. There are other reasons as well. Admittedly there are some compelling options for eliminating this need, however.)

These components can come from anywhere – In this case we typically have the individual recipient files on some network share, and the list of recipients comes from a database or a simple .csv file in the application.

Merging the attachments

Obviously, we need a way to link each file to an individual recipient. The best way would be to use some unique identifier, such as an employee id number. Note that we have an employee id number included in the file name of our file.  …And we have the same id in the recipient list:

The application of course links the two together and this is where it will present to the end-user the result of the merge. Here the user can verify that the correct documents were linked and select who to actually send the email to, if not everyone. The application then loops through the selected recipients and creates a MailMessage, attaches the appropriate file, and sends it down the tubes.

Additional details

As I said, in this particular implementation the “merging” of text within the emails wasn’t even necessary (but it could be just as easy). So the subject, body and other details of the resulting emails were uniform and already set up in the application.

Another benefit of having a separate recipient list is that the web UI can indicate to a user when it failed for find the appropriate file – preventing them from sending that email. Of course this may work both ways – warning the user that it found some files that don’t match a recipient.

A challenging future

One instance of this application has been used for over two years without many problems or changes necessary. But I have another instance that is far more challenging to manage – the nature of the attachments and recipient list changes each time. Ideally it needs to be user-serviceable.

The challenge is how to implement it. The recipient list my users would provide most likely would come from an Excel file – where one mistake could blow the whole system up, rendering it incapable of reading the file. To a lesser extent, the files are also finicky. What if a different naming convention is needed or the way to link the files with the recipients needs to be a little bit different? It may be an enormous effort to build an application so flexible that the user could configure all this at run-time – and without confusion.

A user mistake could potentially send critically sensitive data to the wrong person. And frankly, I don’t want to see who gets blamed for that… if you know what I mean. ;) Still, I cant help but think there is more to this application.

And yet at the end of the day, all it does is send emails like this one to our example recipient, Jill.