logo domkeconsulting

Beratung und Entwicklung - Mehr Produktivität für Microsoft Office

logo domkeconsulting

Consultant and Developer - Get more productivity from Microsoft Office

A typical Office Automation Sample using C#

This project is a typical example how Word, Excel and Outlook can be automated by a Windows Forms application developed with C# 4.0, using a pinch from the Redemption library.

Tasks

In a Law Firm, the payments arriving on trust accounts have to be processed so that the payment is transferred to the correct destination. The workflow connects accounting (where the payment is processed for the first time), administration (which organizes the outflow) and the attorney (which is responsible for the matter, to which the trust payment is related, and who can solely decide about the destination).

In the future, a SharePoint workflow or another system may proceed all elements in a smooth way. However, for the upcoming two or three years a quick and cheap solution would be of great help for the administration, which currently has to deal with Word form documents and e-mails created manually in a time-consuming way. So the tasks are:

  • Create an application which automates the manual process using Word, Excel, and Outlook. Office 2003 is currently used, but Office 2010 is looming around the corner, so the application should work with both versions.
  • Use an existing database with attorney and secretary addresses for e-mail recipient creation
  • Save the form documents to the existing Document Management System (Interwoven), and create links which can be attached to e-mails
  • Send e-mails automatically without user interaction
  • Create a transaction log and prompt in the case of errors
  • Create a MSI installer
  • ... and don't use to much budget ... and have it ready last week ...

UML Packet Diagram

Solution

A Windows Forms application was produced using C# 4.0 as programming language. The Redemption library from Dmitry Streblechenko was used to facilitate the creation of e-mails in Outlook (see www.dimastr.com). Additionally I use the NLog library (http://nlog-project.org) for tracing and error logging.

There is a simple user interface, which lets the user select the Excel source and start the processing. All single tasks are run through background workers, so that the user interface can display the current task details and remains responsive (allowing the user to cancel the processing):

Details

Project costs

The whole project needed 120 hrs for planning, development, administrative tasks and changes after prototyping. At least 40 hrs were due to change requests and additional requirements not calculated at the beginning; on the other hand, I was able to save time in using code fragments from other projects (e.g. communication with the Interwoven API and integration/configuration tasks for the NLog library).

Project benefits

This application is not really appreciated by the IT management, because "it's only used by a handful of users", and "it's a local application not integrated in the big picture". However, the application is an extreme time-saver for this handful, and is saving lot's of hours of moving data from A to B through a manual workflow. At least 3 to 4 hrs per week are saved now in the adminstration. So break-even is reached after 30 weeks.

Some information about the application structure

Here some notes about the application and the office automation it contains.

  • MainController class structures the task sequence.
  • First it checks the environment, creates a temp folder, checks if the SQL Server database is available and if the user can connect to all DMS libraries. Then the processing starts.
  • The ExcelWorker class starts a new Excel application (without user interaction), loads the source file (the Excel workbook). It checks if the file corresponds to the specification, and reads the UsedRange of sheet 1. Since each worksheet row contains a data record, all row data are transferred into instances of a TrustRecord class and added to a list. Excel is closed.
  • The DatabaseWorker connects to the SQL Server database. It loops through the list of TrustRecords, gets its attorney ID and queries the e-mail addresses of the secretary from the database.
  • The WordWorker starts an invisible Word session (so that the user is not interrupted in his work, if he happens to have documents open), loops through the list of TrustRecords, and creates for each record a Word document based on a simple form template. All documents are saved in the temp folder with a temp filename. The Word session is closed.
  • The DMSWorker loops through the list of TrustRecords, gets the client and matter information from each TrustRecord, creates a new entry in the appropriate DMS library, and uploads the document to the DMS server. It then creates a Link File (a reference to the DMS document) and saves it in the temp folder.
  • The EmailWorker creates a connection to the Outbox of the user's e-mail account using the Redemption RDO objects. Again, this class loops through the TrustRecords list, creates a new e-mail for each recipient, adds body text from a template, attaches the DMS link file, and sends the e-mail. This can be done while the user has Outlook open - he may see suddenly new items arriving in his "Sent Items" folder.
  • At the end, a protocol file in Excel is created and saved to the DMS. In the case of suppressed errors (like missing e-mail addresses), the user is prompted and can look up the error source in the protocol.

As usual with this type of application, all references to Office objects must be carefully resolved to prevent memory leaks and issues, especially in Outlook.

Development Tools

Version control is done with Mercurial Tortoise. The MSI installer is created with WIX using MSIFactory from Indigo Rose. Very helpful during development is the LogExpert log file analyzer (http://logexpert.codeplex.com).