Mail Merge in Rich Text Documents

• Feb 21, 2023
• 9 minutes to read

This article shows how you can implement Mail Merge with Word Processing Document API. The example involves the following steps:

1. Load a Document Template
2. Add Mail Merge Fields to the Template
3. Add a Data Source
4. Merge Data into Template and Save Document

Load a Document Template

The template is a document that contains field placeholders. You will later replace these placeholders with actual fields that refer to data source columns.

Call the RichEditControl.LoadDocument or RichEditControl.LoadDocumentTemplate method to load a document template.

The code sample below loads the following template:

C#

using DevExpress.Office.Services;
using DevExpress.XtraRichEdit;
using DevExpress.XtraRichEdit.API.Native;


richEditControl.LoadDocument("template.docx");
AddMailMergeFields(richEditControl.Document);
//...

VB.NET

Imports DevExpress.Office.Services
Imports DevExpress.XtraRichEdit
Imports DevExpress.XtraRichEdit.API.Native

richEditControl.LoadDocument("template.docx")
AddMailMergeFields(richEditControl.Document)
'...

Add Mail Merge Fields

The FieldCollection.Create method allows you to add fields to the template. Use one of the following fields to insert data from the database:

MERGEFIELD

Inserts plain text.

DOCVARIABLE

Inserts formatted content: variables, document elements, or entire document content.

INCLUDEPICTURE

Inserts images. Use the INCLUDEPICTURE field as follows: {INCLUDEPICTURE "{PictureLocation}" \d} (the PictureLocation is the path to the desired picture).

Use the IUriStreamProvider service to insert pictures from a database.

The code sample below shows how to insert MERGEFIELD and INCLUDEPICTURE fields in the template. The first INCLUDEPICTURE field inserts an image from a database. (The "dbimg://" prefix is a stream generated by the IUriStreamProvider implementation). The second field inserts a static image from a file.

C#

using DevExpress.XtraRichEdit;
using DevExpress.XtraRichEdit.API.Native;
//...

private void AddMailMergeFields(Document document)
{
    DocumentRange[] pictureRanges = document.FindAll("Photo", SearchOptions.WholeWord);

    foreach (var pictureRange in pictureRanges)
    {
        DocumentPosition picturePosition = pictureRange.End;

        // Delete the placeholder
        document.Delete(pictureRange);

        // Insert a field to a picture from a database
        Field pictureField = document.Fields.Create(picturePosition, @"INCLUDEPICTURE ""dbimg://{ placeholder }""");

        // Find a placeholder for a nested MERGEFIELD
        DocumentRange nestedFieldRange =
            document.FindAll("{ placeholder }", SearchOptions.WholeWord, pictureField.CodeRange).First();

        // Clear the placeholder range
        document.Delete(nestedFieldRange);

        // Create a nested field
        document.Fields.Create(nestedFieldRange.Start, "MERGEFIELD EmployeeID");
    }

    // Find a placeholder for another image
    // in the footer
    SubDocument footer = document.Sections[0].BeginUpdateFooter();
    DocumentRange[] imageRanges =
        footer.FindAll("image", SearchOptions.WholeWord);
    foreach (var imageRange in imageRanges)
    {
        DocumentPosition imagePosition = imageRange.End;

        // Delete the phrase
        footer.Delete(imageRange);

        // Create a field at the placeholder's position
        footer.Fields.Create(imagePosition, @"INCLUDEPICTURE ""DevExpress-Logo-Large-Color.png""");
    }
    footer.EndUpdate();

    // Find a placeholder for the FirstName field:
    DocumentRange[] nameRanges =
        document.FindAll("FirstName", SearchOptions.WholeWord);
    foreach (var nameRange in nameRanges)
    {
        DocumentPosition namePosition = nameRange.End;

        // Delete the phrase
        document.Delete(nameRange);

        // Create a field at the placeholder position
        document.Fields.Create(namePosition, @"MERGEFIELD ""FirstName""");
    }
}

VB.NET

Imports DevExpress.XtraRichEdit
Imports DevExpress.XtraRichEdit.API.Native
'...

Private Shared Sub AddMailMergeFields(ByVal document As Document)
    Dim pictureRanges() As DocumentRange = document.FindAll("Photo", SearchOptions.WholeWord)

    For Each pictureRange In pictureRanges
        Dim picturePosition As DocumentPosition = pictureRange.End

        ' Delete the placeholder
        document.Delete(pictureRange)

        ' Insert a field to a picture from a database
        Dim pictureField As Field = document.Fields.Create(picturePosition, "INCLUDEPICTURE ""dbimg://{ placeholder }""")

        ' Find a placeholder for a nested MERGEFIELD
        Dim nestedFieldRange As DocumentRange = document.FindAll("{ placeholder }", SearchOptions.WholeWord, pictureField.CodeRange).First()

        ' Clear the placeholder range
        document.Delete(nestedFieldRange)

        ' Create a nested field
        document.Fields.Create(nestedFieldRange.Start, "MERGEFIELD EmployeeID")
    Next pictureRange

    ' Find a placeholder for another image
    ' in the footer
    Dim footer As SubDocument = document.Sections(0).BeginUpdateFooter()
    Dim imageRanges() As DocumentRange = footer.FindAll("image", SearchOptions.WholeWord)
    For Each imageRange In imageRanges
        Dim imagePosition As DocumentPosition = imageRange.End

        ' Delete the phrase
        footer.Delete(imageRange)

        ' Create a field at the placeholder's position
        footer.Fields.Create(imagePosition, "INCLUDEPICTURE ""DevExpress-Logo-Large-Color.png""")
    Next imageRange
    footer.EndUpdate()

    ' Find a placeholder for the FirstName field:
    Dim nameRanges() As DocumentRange = document.FindAll("FirstName", SearchOptions.WholeWord)
    For Each nameRange In nameRanges
        Dim namePosition As DocumentPosition = nameRange.End

        ' Delete the phrase
        document.Delete(nameRange)

        ' Create a field at the placeholder position
        document.Fields.Create(namePosition, "MERGEFIELD ""FirstName""")
    Next nameRange
End Sub

IUriStreamProvider Service Implementation

The code sample below shows the IUriStreamProvider implementation used to insert images from a database. The INCLUDEPICTURE field in the template has a nested MERGEFIELD that refers to the EmployeeID field from the database. The IUriStreamProvider.GetStream method parses the received URI (the INCLUDEPICTURE field), finds the required data row, and returns the MemoryStream with an image.

Note

Make sure that the MERGEFIELD field nested in the INCLUDEPICTURE field refers to the data table’s primary key. Otherwise, the IUriStreamProvider service cannot correctly find the required table row.

ImageStreamProvider.cs

using DevExpress.Office.Services;
using System;
using System.Data;
using System.IO;

public class ImageStreamProvider : IUriStreamProvider
{
    static readonly string prefix = "dbimg://";
    DataTable table;
    string columnName;

    public ImageStreamProvider(DataTable sourceTable, string imageColumn)
    {
        this.table = sourceTable;
        this.columnName = imageColumn;
    }


    public Stream GetStream(string uri)
    {
        // Parse the retrieved URI string
        uri = uri.Trim();
        if (!uri.StartsWith(prefix))
            return null;

        // Remove the prefix from the retrieved URI string
        string strId = uri.Substring(prefix.Length).Trim();
        int id;

        // Check if the string contains the primary key
        if (!int.TryParse(strId, out id))
            return null;

        // Retrieve the row that corresponds
        // with the key
        DataRow row = table.Rows.Find(id);
        if (row == null)
            return null;

        // Convert the image string from this row
        // to a byte array
        byte[] bytes = Convert.FromBase64String(row[columnName] as string) as byte[];
        if (bytes == null)
            return null;

        // Return the MemoryStream with an image
        MemoryStream memoryStream = new MemoryStream(bytes);
        return memoryStream;
    }
}

ImageStreamProvider.vb

Imports DevExpress.Office.Services
Imports System
Imports System.Data
Imports System.IO

Public Class ImageStreamProvider
    Inherits IUriStreamProvider

    Shared ReadOnly prefix As String = "dbimg://"
    Private table As DataTable
    Private columnName As String

    Public Sub New(ByVal sourceTable As DataTable, ByVal imageColumn As String)
        Me.table = sourceTable
        Me.columnName = imageColumn
    End Sub

    Public Function GetStream(ByVal uri As String) As Stream
        uri = uri.Trim()
        If Not uri.StartsWith(prefix) Then Return Nothing
        Dim strId As String = uri.Substring(prefix.Length).Trim()
        Dim id As Integer
        If Not Integer.TryParse(strId, id) Then Return Nothing
        Dim row As DataRow = table.Rows.Find(id)
        If row Is Nothing Then Return Nothing
        Dim bytes As Byte() = TryCast(Convert.FromBase64String(TryCast(row(columnName), String)), Byte())
        If bytes Is Nothing Then Return Nothing
        Dim memoryStream As MemoryStream = New MemoryStream(bytes)
        Return memoryStream
    End Function
End Class

Use DOCVARIABLE Fields in the Mail Merge Template

The DOCVARIABLE field allows you to insert any type of content into a document – from a simple variable to dynamic data from a database or another document. Variables can be stored in a document or calculated within the RichEditControl.CalculateDocumentVariable event.

The code sample below demonstrates how to handle the event to calculate the DOCVARIABLE field value. In this example, this field should insert a number of years the employee worked at a company. The nested MERGEFIELD refers to the HireDate entry in the database.

C#

private static void RichEditControl_CalculateDocumentVariable(object sender, CalculateDocumentVariableEventArgs e)
{
    if (e.Arguments.Count > 0)
    {
        // Retrieve the MERGEFIELD field value
        DateTimeOffset hireDate = Convert.ToDateTime(e.Arguments[0].Value);
        DateTimeOffset currentDate = DateTime.Now;

        // Calculate the difference between the current date
        // and the hire date
        var dif = currentDate.Subtract(hireDate);

        // Calculate the number of years
        int years = dif.Days / 365;

        // Specify the DOCVARIABLE field value
        e.Value = years.ToString();
        e.Handled = true;
    }
    e.Handled = true;
}

VB.NET

Private Shared Sub RichEditControl_CalculateDocumentVariable(ByVal sender As Object, ByVal e As CalculateDocumentVariableEventArgs)
    If e.Arguments.Count > 0 Then
        ' Retrieve the MERGEFIELD field value
        Dim hireDate As DateTimeOffset = Convert.ToDateTime(e.Arguments(0).Value)
        Dim currentDate As DateTimeOffset = DateTime.Now

        ' Calculate the difference between the current date
        ' and the hire date
        Dim dif = currentDate.Subtract(hireDate)

        ' Calculate the number of years
        Dim years As Integer = dif.Days \ 365

        ' Specify the DOCVARIABLE field value
        e.Value = years.ToString()
        e.Handled = True
    End If
    e.Handled = True
End Sub

Add a Data Source

RichEditControl supports the following data source types:

• System.Collections.IList
• System.Collections.ArrayList
• System.Data.DataTable

Use the RichEditMailMergeOptions.DataSource property to specify the data source used in a mail merge. If the data source contains multiple data tables, use the RichEditMailMergeOptions.DataMember property to define a specific data member.

The code sample below shows how to use the XML data source:

C#

using DevExpress.XtraRichEdit;
using System.Data;

DataSet xmlDataSet = new DataSet();
xmlDataSet.ReadXml("Employees.xml");
xmlDataSet.Tables[0].PrimaryKey = new DataColumn[] {
     xmlDataSet.Tables[0].Columns[0] };


richEditControl.Options.MailMerge.DataSource = xmlDataSet.Tables[0];
//...

VB.NET

Imports DevExpress.XtraRichEdit
Imports System.Data

Private xmlDataSet As New DataSet()
xmlDataSet.ReadXml("Employees.xml")
xmlDataSet.Tables(0).PrimaryKey = New DataColumn() { xmlDataSet.Tables(0).Columns(0) }

richEditControl.Options.MailMerge.DataSource = xmlDataSet.Tables[0]
'...

Merge Data into Template and Save the Document

Call the RichEditControl.MailMerge method to merge data into a template and save the document to the specified file or stream. You can use the MailMergeOptions class properties to specify additional mail merge options. Pass the MailMergeOptions object as the MailMerge method parameter to apply these options.

Note

The control’s Options.MailMerge.DataSource property is ignored if you call the MailMerge method and pass a MailMergeOptions object as a parameter. Use the method parameter’s DataSource property to specify the data source instead.

The code sample below specifies mail merge options, runs the mail merge, and saves the document to the specified file.

C#

using DevExpress.Office.Services;
using DevExpress.XtraRichEdit;
using DevExpress.XtraRichEdit.API.Native;

//...
RichEditControl.CalculateDocumentVariable += RichEditControl_CalculateDocumentVariable;

// Register the URI provider service
IUriStreamService uriStreamService = richEditControl.GetService<IUriStreamService>();
uriStreamService.RegisterProvider(new ImageStreamProvider(xmlDataSet.Tables[0], "Photo"));

MailMergeOptions myMergeOptions =
    richEditControl.Document.CreateMailMergeOptions();
myMergeOptions.DataSource = xmlDataSet.Tables[0];
myMergeOptions.MergeMode = MergeMode.NewSection;

richEditControl.MailMerge(myMergeOptions, "result.docx", DocumentFormat.OpenXml);

VB.NET

Imports DevExpress.Office.Services
Imports DevExpress.XtraRichEdit
Imports DevExpress.XtraRichEdit.API.Native

'...
richEditControl.CalculateDocumentVariable += RichEditControl_CalculateDocumentVariable

Dim uriStreamService As IUriStreamService = wordProcessor.GetService(Of IUriStreamService)()
uriStreamService.RegisterProvider(New ImageStreamProvider(xmlDataSet.Tables(0), "Photo"))

Dim myMergeOptions As MailMergeOptions = richEditControl.Document.CreateMailMergeOptions()
myMergeOptions.DataSource = xmlDataSet.Tables(0)
myMergeOptions.MergeMode = MergeMode.NewSection

richEditControl.MailMerge(myMergeOptions, "result.docx", DocumentFormat.OpenXml)

Mail Merge Events

RichEditControl ships with the following events to control specific mail merge steps:

MailMergeStarted

Fires before mail merge starts.

MailMergeFinished

Fires when the mail merge is completed.

MailMergeRecordStarted

Fires before each data record is merged with the document in the mail merge process.

MailMergeRecordFinished

Fires after each data record is merged with the document in the mail merge process.

The code sample below shows how to handle the MailMergeRecordStarted and MailMergeRecordFinished events to insert additional content to the document:

View Example: Use DOCVARIABLE Fields

C#

using DevExpress.XtraRichEdit;
using DevExpress.XtraRichEdit.API.Native;
//...

private static void RichEditControl_MailMergeRecordStarted(object sender, MailMergeRecordStartedEventArgs e)
{
    DocumentPosition position = e.RecordDocument.Range.Start;
    DocumentRange _range =
        e.RecordDocument.InsertText(position, String.Format("Created on {0:G}\n\n", DateTime.Now));
    CharacterProperties cp = e.RecordDocument.BeginUpdateCharacters(_range);
    cp.FontSize = 8;
    cp.ForeColor = Color.Red;
    cp.Hidden = true;
    e.RecordDocument.EndUpdateCharacters(cp);
}

private static void RichEditControl_MailMergeRecordFinished(object sender, MailMergeRecordFinishedEventArgs e)
{
    e.RecordDocument.AppendDocumentContent("Docs\\bungalow.docx", DocumentFormat.OpenXml);
}

VB.NET

Imports DevExpress.XtraRichEdit
Imports DevExpress.XtraRichEdit.API.Native
'...

Private Shared Sub RichEditControl_MailMergeRecordStarted(ByVal sender As Object, ByVal e As MailMergeRecordStartedEventArgs)
    Dim position As DocumentPosition = e.RecordDocument.Range.Start
    Dim _range As DocumentRange = e.RecordDocument.InsertText(position, String.Format("Created on {0:G}" & vbLf & vbLf, DateTime.Now))
    Dim cp As CharacterProperties = e.RecordDocument.BeginUpdateCharacters(_range)
    cp.FontSize = 8
    cp.ForeColor = Color.Red
    cp.Hidden = True
    e.RecordDocument.EndUpdateCharacters(cp)
End Sub

Private Shared Sub RichEditControl_MailMergeRecordFinished(ByVal sender As Object, ByVal e As MailMergeRecordFinishedEventArgs)
    e.RecordDocument.AppendDocumentContent("Docs\bungalow.docx", DocumentFormat.OpenXml)
End Sub

Tip

If your template document contains fields that are not bound to database fields, you can replace those fields with their values (unlink fields). Use the following methods to do this:

• Field.Unlink()
• FieldCollection.Unlink()
• Document.UnlinkAllFields()

Mail Merge Functionality in the User Interface

The RichEditControl ships with the Mail Merge ribbon tab, which allows users to insert merge fields, toggle field codes and results and view the merged data. Refer to the following topic for details on how to create the ribbon UI for the RichEditControl: How to: Create the RichEditControl with a Ribbon UI

Tip

Use the DocumentCapabilitiesOptions.Fields property to disable or hide the mail merge UI elements.