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