C# - Microsoft Word 2010 Automated Mail Merge Part 2 of 2

Back to Part 1

In this example I'll use a more dynamic approach that reads customer data from a SQL database and customizes the query before generating the mail merge.

The ASP.NET application pulls in all of the customer data from from the Northwind database and allows paging and sorting of the data via a Grid View Control. Checkbox controls allow selection of records that will be included in the mail merge. Clicking the Mail Merge button selects all of the checked customers and launches Microsoft Word with all of the customer data pre-populated into the mail merge template (shown below).

Download the Northwind Sample Database - Here
Download the Microsoft Mail Merge Sample Code - Here

First thing I noticed was that Microsoft no longer includes the sample databases with SQL 2008R2, after a couple of searches I found that you can still download them and run scripts or restore the database files.

After I downloaded the sql2000sampleDb.msi I ran the install. Once the install was complete I opened a command prompt and navigated to C:\SQL Server 2000 Sample Databases. Because my SQL server is installed as the default instance I typed sqlcmd -i instpubs.sql to perform the install, I verified that the Northwind database was now installed on my default SQL instance. Note: if you have a non-default SQL instance you can use the following syntax - sqlcmd -S .\InstanceName -i instpubs.sql

I created a new Visual Studio 2010 ASP.NET project, added a new reference to the project by expanding references from the solution explorer and selecting new reference. I selected the COM tab and chose the Microsoft Word 14 reference. This will add 2 new references to the project - Microsoft.Office.Core and Microsoft.Office.Interop.Word. 



This example uses the Microsoft MSDN sample code for the basic mail merge functions.

Add a using statement for the newly added reference as follows:
using Word = Microsoft.Office.Interop.Word;





I cut the first four lines of the Microsoft code and pasted it at the beginning of the class declaration to ensure proper scope for these variables.

Next I Selected the Server Explorer tab and created a connection to my local SQL Server and Northwind database.

I added GridView and button controls to the page

Next I configured the datasource for the Gridview control and selected the Northwind connection string from the dropdown

Next A select * statement is chosen from the Northwind Customers table on the configure the Select Statement screen

This completes the Gridview datasource

Next I added a checkbox control to the Gridview to allow selecting one or more customers. Select the source view of the aspx page and add the following code to the gridview under the columns node.

Back to the design view - double click on the button on the form and add the following code to the  button click event.

The code will iterate through the gridview control and select only the rows that were checked. I built a very simple customer class to hold the data from the selected rows. This can be customized to include any of the fields from the SQL database but for now I'm only including the ID, ContactName and ContactTitle fields. A Generic Dictionary <T> Collection is used to hold the customer objects and this will be passed to  the CreateMailMergeDataFile method.

In the same button click event that you added the snippet above add all of the code in the button1_click event from the MSDN sample code (not all code shown highlighted below)

Next add the remainder of the MSDN sample code into the aspx page just under the public partial class declaration.

Replace the highlighted code in the CreateMailMergeDataFile with the code shown below

Replace highlighted code with this code

I included my project files but you will have to modify the connection string information to your SQL database if it's not on the local machine in the default SQL instance.

Download Visual Studio Solution

Back to Part 1

C# - Microsoft Word 2010 Automated Mail Merge Part 1 of 2

Continue to Part 2

I was recently tasked with building an add-on for a CRM application to allow the application to integrate with Microsoft Word and generate a mail merge.

I decided to use two examples for demonstrating how to generate the mail merge, the first will be from a Microsoft MSDN code sample and demonstrate a hard coded method of outputting data from my C# application to Microsoft Word.

For the second example, I'll demonstrate a more dynamic approach by connecting to a SQL sample database (Northwind). I'll build an ASP.NET user interface to allow selection and filtering of customers prior to sending the data to Microsoft Word for the mail merge.

Tools
Visual Studio 2010
Microsoft Word 2010
Microsoft SQL Server 2008R2

Part 1
Microsoft Mail Merge Code - Here

Microsoft created some sample code for integrating custom c# applications with Microsoft Word and automating the mail merge, the code provides the mechanism for programmatically generating the mail merge with some inline customer data.

From Visual Studio 2010 I created a new ASP.NET project, the Microsoft code was developed with a Windows Forms application in mind but I ported it into an ASP.NET application. First I added a new reference to the project by expanding references from the solution explorer and selecting new reference. I selected the COM tab and chose the Microsoft Word 14 reference. This will add 2 new references to the project Microsoft.Office.Core and Microsoft.Office.Interop.Word.

Add a using statement for the newly added reference as follows:
using Word = Microsoft.Office.Interop.Word;

I cut the first four lines of the Microsoft code and pasted it at the beginning of the class declaration to ensure proper scope for these variables.


Next I switched to the design view of the default.aspx page in my project and clicked on the toolbox and added a new button to the current page.

Next I double clicked the new button and pasted the remainder of the code from the Microsoft article into the click event handler of the new button.

I built the application with no errors and launched it for a test.

I clicked the button and it looks like everything is working as expected so far, Microsoft Word launches and displays three customer forms that are ready to be printed/mailed.

The standard form data is consistent on all of the user forms and the inline unique customer information is populated into the variable field locations.

This example can be modified to read from a text or csv file or any number of methods that would allow an automated extraction of customer data.

The data can also be kept inline in the code behind as is.

This completes the first example, this example provides a good look into the Word object model and what it takes to programmatically create and manipulate Word objects. Again this is a pretty static and simple method for demonstrating the mail merge. A more advanced approach will be described in Part 2.

Continue to Part 2