I would like to talk about a recent project I worked on where the requirement was to create Microsoft Word Agendas, for distribution, based on a collaborative agenda item submission process. The client wanted to be able to have all of its product managers submit agenda topics for upcoming meetings, then the teams would be able to generate agendas for any previous meeting or any future planned meetings dynamically.

What we decided to do was utilize a SharePoint list as the source for our Agenda Items. This way multiple users could submit agenda items, specify the time required, agenda details, etc, directly through SharePoint. We then created a Word 2007 Add-In that would allow the Project Manager to create agendas, just by selecting a scheduled date. The basic workflow is as follows:

1. Users log into a SharePoint portal and then create a new list item in the custom Agenda List template.
2. The Project Manager approves the items and finalizes the date and times.
3. Then the Project Manager or a support staff member generates the agendas using the Word .Add-in.

Technically, this project used multiple technologies. We used Microsoft Word 2007, Visual Studio 2005 Tools for Office (VSTO), the SharePoint API and Web Services and a host of other helper utilities and tools.

The first step was to create the Word agenda template that the Project Managers wanted to use. We worked with the Project Manager and the Clients to come up with a simple standard agenda template that would display all the necessary information regarding the agenda Items. We then created the custom list in SharePoint. This involved defining the columns, column types, list layout and views. The next step was to create the Word Add-in, using VSTO (Visual Studio Tools for Office) to connect to SharePoint, via its web services, and then automatically populate the agenda template. After the basic code and project were complete we refactored it for extensibility so that it can be reused for future modifications to the list and the template. The final step was to create the deployment packages and instructions for the end user.

During this project we learned a lot about working with SharePoint to automate Word. Below are some of the troubles we encountered and how we resolved them.

1. CAML Creation and Verification. Creating CAML to query the SharePoint Web Services. I don’t know a lot of people that are experts in this technology, so we used the U2UCamlCreator tool to write this query for us.
   
   Note: The URL for this tool is: U2UCamlCreator
   Note: When you get the CAML Query syntax and you need to pass it to the SharePoint Web Services, remember to remove the <Query>element as the web service will not return the results if you have this.
   
   
2. SharePoint Web Services Location and Formats. The SharePoint list web services are per site and use the following syntax: http://<YOURSERVERNAME>:<PORT>/<SITE>/_vti_bin/lists.asmx
   One interesting aspect of using the web services to query and retrieve data is how SharePoint replaces certain special character and spaces in column names with xml valid characters. For example: a space is replaced with _x0020_ and “: “is replaced with _x0030_
   This is good to know when you are querying with CAML or programmatically retrieving column names and formats.
   
3. Deploying the CASPOL changes in the MSI. Deployment of the add-in was pretty straight forward, but to automate the CASPOL, security settings was not. In order for the add-in to work on the client, you need to fully trust the assembly that contains the add-in code. This is done by using the .NET Code Access Security Policy tool or CASPOL.exe. The problem is that you want to automatically add the appropriate entrees into the Code Access Policy during installation. To do this, you need to use the ORCA.exe tool, which is available only if you install the Microsoft Windows Software Development Kit: ORCA.EXE
   Once you have that installed, you can follow the basic instructions from Microsoft on how to add a custom action to your MSI file that uses the ORCA generated MSI. (Link follows) Custom Actions using ORCA.EXE
   
   Note: Remember that modifying the MSI using this tool is a one way change. Which means that if you re-open the Visual Studio Setup Project (Below) and recompile it, any modifications that you made using ORCA will be lost. Consequently, the add-in will not work, because none of the CASPOL policy changes will have been added.
   
   
4. Deploying the List to the MOSS servers. To deploy the list easily, so that we could support future upgrades and modifications, we decided to use a SharePoint Feature Package. This way we could install it on any SharePoint 2007 server and selectively activate it where we needed it. To do this we performed the steps below:
   
   1. We created the custom list, through the SharePoint GUI. We created the columns, validation rules, etc.
   2. We then saved this custom list as a Template and then exported the Template from the MOSS List Template Gallery.
   3. We then cracked open the STP file by renaming it to *.CAB. This enabled us to view the Manifext.xml file, which is essentially the list schema.
      
      Note: One interesting fact about this is that you can modify the manifest.xml file and then when you are ready to update the STP file, you can simple recreate a new CAB file by using the MakeCab.exe program located under c:\windows\system32. Then you can simple rename the *.CAB to *.STP and re-upload the list definition into the gallery.
      
   4. We then copied the CustomList schema from the default SharePoint Feature Hive: c:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\FEATURES\CustomList\CustList\schema.xml
      This provided us with a working custom list template, that we could now copy in our customized modifications.
   5. Created the new Custom List Schema.xml. We simply copied in the <Fields> and <ViewFields>from the Manifest.xml file into the new Schema.xml file. Then we overwrote the default <ContentType>section to include our custom columns in New and Edit forms.
   6. Finally we created the Feature.xml files and the Elements.xml file so that we can deploy the feature.
5. Extensibility.  This was really the key to the project.  We needed a way to create flexibility into the system, which would enable us to re-use this technology for other needs, such as meeting minuets, action items, other agendas, etc.  To do this, we stored a lot of the parameters and options in the Word Template itself.  We even went as far as storing the entire CAML query in a Word Custom Property.  The Add-In would open up the template and read the properties from the Word Document.  These properties gave us all the information we needed to connect to the portal list, pull the data and format it.  If you open up the source code (Below) and view the Word Template you will be able to see the Custom Properties.  In Word 2007, click on the Office Button (Top Right) --> Prepare --> Properties.  This will open the Properties options.  On this set of dialogs, click on the Document Properties (Top Left) menu and click Advanced Properties.  On this form, select the Custom Tab.  This will now allow you to see and create Custom Properties.  Below are a list of properties we used:
   
   

   Property Name	Description
   _POSSPLISTURL	The SharePoint List Web Services URL.  This is specific to your Server and Site.
   _SPLISTNAME	The SharePoint List Name where the data is stored.
   _ITEMDETAILCOLUMN	This is where the Item Detail is.  This is a SharePoint List Column Name.
   _TIMEREQUIREDCOLUMN	This is where the Time the Agenda Item will take is stored.  This is a SharePoint List Column Name.
   _ONAGENDACOLUMN	This determines if the Agenda made the meeting or not.  This is a SharePoint List Column Name.
   _PRIORITYCOLUMN	This determines the Agenda Items Priority.  This is a SharePoint List Column Name.
   _WHO1COLUMN	This provides the client attendees.  This is a SharePoint List Column Name.
   _WHO2COLUMN	This provides the staff attendees.  This is a SharePoint List Column Name.
   AGENDAQUERY1	This is the CAML Query.  Because Word Custom Properties are limited to 256 characters, we have 2 custom properties so they can be concated.
   AGENDAQUERY2	This is the 2nd halve of the CAML Query.

   
   

• How to create a simple feature: http://msdn2.microsoft.com/en-us/library/ms475286.aspx
• How to create a Custom List Definition: http://msdn2.microsoft.com/en-us/library/ms466023.aspx
• Creating Custom List Definition Overview: http://jopx.blogspot.com/2007/05/sharepoint-2007-how-to-create-custom.html
• MSI Clean Up Utility: http://support.microsoft.com/kb/290301

To download the source code, features and utilities, click on the link below.
Agenda Creator c# Source Code