Automating Word Mail Merge in Visual Studio – C# sample

The Mail Merge functionality in Microsoft Word is one of the most powerful features that enables you to create, amongst other things, simple form letters.

It is, however, one of the tricky things to get your head around when trying to automate it in either an Office add-in or a stand-alone application using the Word Object model. In this article, I’ll guide you through creating a Microsoft Word add-in that will make it easier for the user to create Word merge documents that incorporates data from a SQL Server database.

• Creating the Word COM add-in project
• Building the Word add-in UI
• Running the Mail Merge

Creating the Word COM add-in project

To start, create a new ADX COM Add-in project in Visual Studio (2012, 2010, 2008 or 2005) using Add-in Express for Office and .net.

Select you programming language of choice and minimum supported version of Office. In this example we’ll be using Visual C# (VB.NET and C++.NET are supported as well) and Microsoft Office 2010 and above.

Since, we’ll only be working with Word, select Microsoft Word as the only supported application.

Building the Word add-in UI

After the Microsoft Word addin project has been created in Visual Studio, add a new ADX Word Task Pane item to you project. You can find the template for this item under Add-in Express Items > Word.

This advance Word task pane will be our primary user interface. It will contain a toolbar with buttons to connect to and retrieve database information from a SQL Server. The final design should resemble the following in Visual Studio:

Double-click the Connect to server button and add the following code to the button’s Click event handler:

private void btnConnect_Click(object sender, EventArgs e)
{
    DataConnectionConfiguration dcs =
        new DataConnectionConfiguration(null);
    dcs.LoadConfiguration(dcd);
 
    if (DataConnectionDialog.Show(dcd) == DialogResult.OK)
    {
        var tables = SqlUtils.GetAllTables(dcd.ConnectionString);
 
        SqlConnectionStringBuilder builder =
            new SqlConnectionStringBuilder();
        builder.ConnectionString = dcd.ConnectionString;
        dbName = builder.InitialCatalog;
        serverName = builder.DataSource;
        connectionString = dcd.ConnectionString;
 
        TreeNode rootNode = new TreeNode(
            builder.InitialCatalog, 1, 1);
        TreeNode tablesNode = rootNode.Nodes.Add(
            "Tables", "Tables", 2, 2);
 
        foreach (string table in tables)
        {
            TreeNode tableNode = tablesNode.Nodes.Add(
                table, table, 3, 3);
            tableNode.Tag = "table";
        }
 
        tvDatabase.Nodes.Add(rootNode);
        tvDatabase.ExpandAll();
    }
}

The above code will display the Windows data connection dialog and display all the database tables in the database the user selected. You can download the source code for this dialog from MSDN.

The code that retrieves all the database and server specific information is located in the SqlUtils.cs class. The tree view will contain all the database tables as illustrated in the following image:

Next, we need to add the code to load all the columns of the table the user would be able to double-click on. To do this, select the tree view control in the Visual Studio forms designer and double-click next to its NodeMouseDoubleClick event in the properties window to generate an event handler.

Add the following code to the NodeMouseDoubleClick event handler:

private void tvDatabase_NodeMouseDoubleClick(object sender,
    TreeNodeMouseClickEventArgs e)
{
    TreeNode selectedNode = e.Node;
    if (selectedNode.Tag.ToString() == "table")
    {
        var columns = SqlUtils.GetAllColumns(
            connectionString, selectedNode.Text);
        foreach (string col in columns)
        {
            TreeNode columnNode = selectedNode.Nodes.Add(
                col, col, 6, 6);
            columnNode.Tag = "column";
        }
        tableName = selectedNode.Text.Replace("dbo.", "");
        selectedNode.Expand();
    }
    else if (selectedNode.Tag.ToString() == "column")
    {
        Word.Application wordApp = null;
        Word.Document doc = null;
        Word.Selection selection = null;
        Word.MailMerge wordMerge = null;
        Word.MailMergeFields wordMergeFields = null;
 
        try
        {
            wordApp = (Word.Application)WordAppObj;
            doc = wordApp.ActiveDocument;
 
            wordMerge = doc.MailMerge;
            wordMergeFields = wordMerge.Fields
            selection = wordApp.Selection;
 
            wordMergeFields.Add(
                selection.Range, selectedNode.Text);
 
        }
        finally
        {
            if (wordMergeFields != null)
                Marshal.ReleaseComObject(wordMergeFields);
            if (wordMerge != null)
                Marshal.ReleaseComObject(wordMerge);
            if (selection != null)
                Marshal.ReleaseComObject(selection);
            if (doc != null)
                Marshal.ReleaseComObject(doc);
        }
    }
}

The code above checks whether the user clicked on a table or a column name. If the user double-clicks on a table, it will add the tables’ columns as child nodes, as illustrated below.

And if the user double-clicks on a column node it will add the column name to the active Word documents’ MailMerge fields.

Running the Mail Merge

Using the above mentioned approach, the user can easily design their letter or document layout and when ready run the Mail merge for all the records in the selected table. To execute the mail merge, we’ve added a button to the custom Word task pane. Add the following code to the buttons’ Click event:

private void btnDoMerge_Click(object sender, EventArgs e)
{
    Word.Application wordApp = null;
    Word.Document doc = null;
    Word.MailMerge wordMerge = null;
    Word.MailMergeFields wordMergeFields = null;
 
    object sqlQuery = string.Empty;
    object connection = string.Empty;
    string odcPath = string.Empty;
 
    Object oMissing = System.Reflection.Missing.Value;
    Object oFalse = false;
 
    try
    {
        wordApp = WordAppObj as Word.Application;
        doc = wordApp.ActiveDocument;
        wordMerge = doc.MailMerge;
        wordMergeFields = wordMerge.Fields;
 
        sqlQuery = String.Format("Select * From \"{0}\"", tableName);
        connection = String.Format(
            "Provider=SQLOLEDB.1;Integrated Security=SSPI;" +
            "Persist Security Info=True;Initial Catalog={0};Data Source={1}",
            dbName, serverName);
        odcPath = CreateODCFile(serverName, dbName);
 
        wordMerge.OpenDataSource(odcPath, ref oMissing,
            ref oMissing, ref oMissing, ref oMissing,
            ref oMissing, ref oMissing, ref oMissing,
            ref oMissing, ref oMissing, ref oMissing,
            ref connection, ref sqlQuery, ref oMissing,
            ref oMissing, ref oMissing);
 
        wordMerge.Destination =
            Word.WdMailMergeDestination.wdSendToNewDocument;
        wordMerge.Execute(ref oFalse);
    }
    finally
    {
        if (wordMergeFields != null)
            Marshal.ReleaseComObject(wordMergeFields);
        if (wordMerge != null)
            Marshal.ReleaseComObject(wordMerge);
        if (doc != null)
            Marshal.ReleaseComObject(doc);
    }
}

The above code, builds a SQL selected query as well as a connection string to the selected server and database. It then executes the OpenDataSource method of the MailMerge object. The first parameter is the path to an Office Data Connection (.odc) file, which we created by calling the CreateODCFile method.

Most of the other parameters for the OpenDataSource method can be set to a missing object, except for the Connection and SQLStatement parameters.

All that is now left to do is to set the destination of the mail merge – in this case a new Word document – as well as to call the MailMerge objects’ Execute method. You can also choose to set the destination of the mail merge to either print the documents or send the results to e-mail.

The final result should be a document with a letter for each customer in the Northwind database. Please see the included sample project for a complete working example of the project I’ve explained in this article.

Thank you for reading. Until next time, keep coding!

Available downloads:

This sample Outlook add-in was developed using Add-in Express for Office and .net:

World Mail Merge add-in (C#)