Pieter van der Westhuizen

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

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.

Creating a new Word COM Add-in project in Visual Studio

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.

Selecting C# as the programming language of choice and Office 2010 as the minimum supported version

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

Selecting 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.

Adding a task pane to the Word add-in project

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:

The custom task pane's design 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.

Connection properties

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:

The tree view of all the database tables

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.

Generating an event handler for the NodeMouseDoubleClick event

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.

A double-click on a table adds the tables' columns as child nodes

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

A double-click on a column node adds 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#)

5 Comments

  • http://0.gravatar.com/avatar/40874c7300e2dccd3628b3542ae4d492?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Guido Leenders says:

    For this functionality to work, you can also use an add-in from Invantive on Office 2010 and 2013 named Invantive Composition. With Invantive Composition you can merge database contents with templates, but it is more for non-technical end users because they just need to put the query in a model editor and then insert building blocks through a button. But I may be biased working at Invantive :-)

  • http://0.gravatar.com/avatar/64ed04f800bbf29c29d3df257820fe45?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Fco Z says:

    good morning, thanks for the input, download the add WordMerger, but I can not load it into word. I tried to load the file in “… \ Loader \ adxloader64.dll” but doing word tells me that is not a valid office add-in.

    That I can do to load it. Greetings.

  • http://0.gravatar.com/avatar/e1a4c2b21a5186e0b27c1c601f418b76?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Pieter van der Westhuizen says:

    Hi There,

    Do you have Add-in Express and Visual Studio installed?

  • http://1.gravatar.com/avatar/bc66074687a363985c22950d1c332575?s=32&d=http%3A%2F%2F1.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G David Zondray says:

    Very informative thanks for sharing. Let me share my code of C# for executing mail merge, I hope it will be also useful for the community. I have used Aspose.Words for .NET: http://www.aspose.com/.net/word-component.aspx

    1st code:

    Performs a simple insertion of data into merge fields and sends the document to the browser inline.

    // Open an existing document.
    Document doc = new Document(MyDir + “MailMerge.ExecuteArray.doc”);

    // Fill the fields in the document with user data.
    doc.MailMerge.Execute(
    new string[] {“FullName”, “Company”, “Address”, “Address2″, “City”},
    new object[] {“James Bond”, “MI5 Headquarters”, “Milbank”, “”, “London”});

    // Send the document in Word format to the client browser with an option to save to disk or open inside the current browser.
    doc.Save(Response, “MailMerge.ExecuteArray Out.doc”, ContentDisposition.Inline, null);

    // Executes a mail merge with repeatable regions.

    Here is the C# code

    public void ExecuteWithRegionsDataTable()
    {
    Document doc = new Document(MyDir + “MailMerge.ExecuteWithRegions.doc”);

    int orderId = 10444;

    // Perform several mail merge operations populating only part of the document each time.

    // Use DataTable as a data source.
    DataTable orderTable = GetTestOrder(orderId);
    doc.MailMerge.ExecuteWithRegions(orderTable);

    // Instead of using DataTable you can create a DataView for custom sort or filter and then mail merge.
    DataView orderDetailsView = new DataView(GetTestOrderDetails(orderId));
    orderDetailsView.Sort = “ExtendedPrice DESC”;
    doc.MailMerge.ExecuteWithRegions(orderDetailsView);

    doc.Save(MyDir + “MailMerge.ExecuteWithRegionsDataTable Out.doc”);
    }

    private static DataTable GetTestOrder(int orderId)
    {
    DataTable table = ExecuteDataTable(string.Format(
    “SELECT * FROM AsposeWordOrders WHERE OrderId = {0}”, orderId));
    table.TableName = “Orders”;
    return table;
    }

    private static DataTable GetTestOrderDetails(int orderId)
    {
    DataTable table = ExecuteDataTable(string.Format(
    “SELECT * FROM AsposeWordOrderDetails WHERE OrderId = {0} ORDER BY ProductID”, orderId));
    table.TableName = “OrderDetails”;
    return table;
    }

    ///
    /// Utility function that creates a connection, command,
    /// executes the command and return the result in a DataTable.
    ///
    private static DataTable ExecuteDataTable(string commandText)
    {
    // Open the database connection.
    string connString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” +
    DatabaseDir + “Northwind.mdb”;
    OleDbConnection conn = new OleDbConnection(connString);
    conn.Open();

    // Create and execute a command.
    OleDbCommand cmd = new OleDbCommand(commandText, conn);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    DataTable table = new DataTable();
    da.Fill(table);

    // Close the database.
    conn.Close();

    return table;
    }

    Thanks

  • http://0.gravatar.com/avatar/e1a4c2b21a5186e0b27c1c601f418b76?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Pieter van der Westhuizen says:

    Hi David,

    Thank you for sharing!

Post a comment

Have any questions? Ask us right now!