Archiving Office documents and e-mails to MSCRM team-sites

In a previous post, I described how we integrate SharePoint team-sites into MSCRM to provide collaboration features and to provide document archive functionality to our MSCRM customers. We typically give the users the ability to create WSS team-sites for each account, each opportunity or for each custom entity such as 'project'. If the customer also uses SharePoint Portal Server (SPS), we register the team-sites in the site directory and include them in SPS search scopes to ensure that e.g. documents are indexed by the SPS search engine.

In addition, we provide an Office add-in that allows users to directly archive Word documents and Outlook e-mail messages into the MSCRM team-sites (much like the "snap"). MSCRM has poor support for storing documents (only as attachments to notes), and the support for creating new documents from templates and merging data from the database is not very much better. The rather lackluster support for document production in MSCRM is something potential customers always react negative to in sales meetings.

This screenshot collage shows how the add-in is used in Word and Outlook (click to enlarge):
E-mails are archived using the .MSG format. The SPS search engine can be configured to index .MSG files, thus making it easier to find archived messages. Documents are readily indexed by SPS out-of-the-box.

The templates and the datasets that they use, are configured using a 'document production admin' team-site, and the add-in uses the standard WSS web-services to get the list of templates:
The templates support merging in data from the datasets into placeholders in the document (bookmarks, document variables, [xml element name] literal text). The merge engine is simple and requires no scripting of each template: it just loops through the dataset and looks for all placeholders with the same name as the dataset column names:
The produced document is of course automatically archived to the correct team-site when the merging has finished (screenshot). Optionally, a post merge Word macro can be run should a template require some scripting, e.g. to create an table listing the details of a quote.

The merge data used in the templates are provided through custom web-services. Stored procedures using FOR XML AUTO, ELEMENTS are used to return the dataset to the add-in as XML. A stored procedure takes some MSCRM entity IDs as input, and then retrieve data from the MSCRM database, and possibly other datasources, to provide one or more resulting datasets. This allows for returning data about e.g. both an account, its primary contact, a quote and quote details in one go.

The Office add-in (.NET1.1, non-VSTO) currently supports Office 2000, XP, and 2003. It might be made available to other Microsoft partners this spring, keep an eye on this blog for further details.