Use Nested Mail Merge in C# and VB.NET

Nested mail merge is a powerful feature that enables you to import a relational or hierarchical data source into a template document in a single statement.

You can find more info about nested merge ranges on the Nested mail merge help page.

The following examples will show how you can use the nested mail merge feature in C# and VB.NET, using the GemBox.Document component.

Nested Merge with DataSet

A relational data source is a data source that has a tabular structure. The information is stored in multiple tables(DataTable objects) that are logically connected with relationships (DataRelation objects).

The following example shows how you can perform a nested mail merge with a relational object (DataSet) as a data source.

Word document generated from merging nested ranges with relational DataSet object
Screenshot of Word file created with relational nested merge
Upload your file (Drag file here)
using GemBox.Document;
using System;
using System.Data;
using System.Linq;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        ComponentInfo.SetLicense("FREE-LIMITED-KEY");

        int numberOfProjects = %ProjectsCount%;
        int itemsPerProject = %ItemsCount%;

        string projectsRangeName = "Projects";
        string itemsRangeName = "Items";

        // Create relational data.
        var projects = new DataTable(projectsRangeName);
        projects.Columns.Add("Id", typeof(int));
        projects.Columns.Add("Name", typeof(string));

        var items = new DataTable(itemsRangeName);
        items.Columns.Add("ProjectId", typeof(int));
        items.Columns.Add("Date", typeof(DateTime));
        items.Columns.Add("Hours", typeof(int));
        items.Columns.Add("Unit", typeof(double));
        items.Columns.Add("Price", typeof(double));

        // Create DataSet with parent-child relation.
        var data = new DataSet();
        data.Tables.Add(projects);
        data.Tables.Add(items);
        data.Relations.Add(itemsRangeName, projects.Columns["Id"], items.Columns["ProjectId"]);

        for (int projectIndex = 1; projectIndex <= numberOfProjects; projectIndex++)
        {
            int id = projectIndex;
            string name = $"Project {projectIndex}";

            projects.Rows.Add(id, name);

            for (int itemIndex = 1; itemIndex <= itemsPerProject; itemIndex++)
            {
                DateTime date = DateTime.Today
                    .AddMonths(projectIndex - numberOfProjects)
                    .AddDays(itemIndex - itemsPerProject);
                int hours = itemIndex % 3 + 6;
                double unit = projectIndex * 35.0;
                double price = hours * unit;

                items.Rows.Add(id, date, hours, unit, price);
            }
        }

        var document = DocumentModel.Load("%InputFileName%");

        // Customize mail merging to achieve calculation of "TotalPrice" for each project.
        document.MailMerge.FieldMerging += (sender, e) =>
        {
            if (e.MergeContext.RangeName == "Projects" && e.FieldName == "TotalPrice")
            {
                var total = data.Tables[e.MergeContext.RangeName].Rows[e.MergeContext.RecordIndex]
                    .GetChildRows(itemsRangeName).Sum(item => (double)item["Price"]);

                var totalRun = new Run(e.Document, total.ToString("0.00"));
                totalRun.CharacterFormat = e.Field.CharacterFormat.Clone();

                e.Inline = totalRun;
                e.Cancel = false;
            }
        };

        // Execute nested mail merge.
        document.MailMerge.Execute(data, null);
        
        document.Save("MergedNestedRangesOutput.%OutputFileType%");
    }
}
Imports GemBox.Document
Imports System
Imports System.Data
Imports System.Linq

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        ComponentInfo.SetLicense("FREE-LIMITED-KEY")

        Dim numberOfProjects As Integer = %ProjectsCount%
        Dim itemsPerProject As Integer = %ItemsCount%

        Dim projectsRangeName As String = "Projects"
        Dim itemsRangeName As String = "Items"

        ' Create relational data.
        Dim projects As New DataTable(projectsRangeName)
        projects.Columns.Add("Id", GetType(Integer))
        projects.Columns.Add("Name", GetType(String))

        Dim items As New DataTable(itemsRangeName)
        items.Columns.Add("ProjectId", GetType(Integer))
        items.Columns.Add("Date", GetType(DateTime))
        items.Columns.Add("Hours", GetType(Integer))
        items.Columns.Add("Unit", GetType(Double))
        items.Columns.Add("Price", GetType(Double))

        ' Create DataSet with parent-child relation.
        Dim data As New DataSet()
        data.Tables.Add(projects)
        data.Tables.Add(items)
        data.Relations.Add(itemsRangeName, projects.Columns("Id"), items.Columns("ProjectId"))

        For projectIndex As Integer = 1 To numberOfProjects

            Dim id As Integer = projectIndex
            Dim name As String = $"Project {projectIndex}"

            projects.Rows.Add(id, name)

            For itemIndex As Integer = 1 To itemsPerProject

                Dim [date] As DateTime = DateTime.Today _
                    .AddMonths(projectIndex - numberOfProjects) _
                    .AddDays(itemIndex - itemsPerProject)
                Dim hours As Integer = itemIndex Mod 3 + 6
                Dim unit As Double = projectIndex * 35.0
                Dim price As Double = hours * unit

                items.Rows.Add(id, [date], hours, unit, price)

            Next
        Next

        Dim document = DocumentModel.Load("%InputFileName%")

        ' Customize mail merging to achieve calculation of "TotalPrice" for each project.
        AddHandler document.MailMerge.FieldMerging,
            Sub(sender, e)
                If e.MergeContext.RangeName = "Projects" And e.FieldName = "TotalPrice" Then

                    Dim total = data.Tables(e.MergeContext.RangeName).Rows(e.MergeContext.RecordIndex) _
                        .GetChildRows(itemsRangeName).Sum(Function(item) CDbl(item("Price")))

                    Dim totalRun As New Run(e.Document, total.ToString("0.00"))
                    totalRun.CharacterFormat = e.Field.CharacterFormat.Clone()

                    e.Inline = totalRun
                    e.Cancel = False

                End If
            End Sub

        ' Execute nested mail merge.
        document.MailMerge.Execute(data, Nothing)

        document.Save("MergedNestedRangesOutput.%OutputFileType%")

    End Sub
End Module

Nested Merge with Object

A hierarchical data source is a data source that has a tree-like structure. The information is stored in multiple records (any Objects) that are connected through links (with Properties).

The following example shows how you can perform a nested mail merge with a hierarchical object (created using LINQ and anonymous type) as a data source.

Word document generated from merging nested ranges with hierarchical anonymous object
Screenshot of Word file created with hierarchical nested merge
Upload your file (Drag file here)
using GemBox.Document;
using System;
using System.Linq;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        ComponentInfo.SetLicense("FREE-LIMITED-KEY");

        int numberOfProjects = %ProjectsCount%;
        int itemsPerProject = %ItemsCount%;

        // Create hierarchical data source using LINQ and anonymous types.
        var projects = Enumerable.Range(1, numberOfProjects).Select(projectIndex =>
        {
            return new
            {
                Name = $"Project {projectIndex}",
                Items = Enumerable.Range(1, itemsPerProject).Select(itemIndex =>
                {
                    DateTime date = DateTime.Today
                        .AddMonths(projectIndex - numberOfProjects)
                        .AddDays(itemIndex - itemsPerProject);
                    int hours = itemIndex % 3 + 6;
                    double unit = projectIndex * 35.0;
                    double price = hours * unit;

                    return new { Date = date, Hours = hours, Unit = unit, Price = price };
                }).ToArray()
            };
        }).ToArray();

        var document = DocumentModel.Load("%InputFileName%");

        // Customize mail merging to achieve calculation of "TotalPrice" for each project.
        document.MailMerge.FieldMerging += (sender, e) =>
        {
            if (e.MergeContext.RangeName == "Projects" && e.FieldName == "TotalPrice")
            {
                var total = projects[e.MergeContext.RecordIndex].Items.Sum(item => item.Price);

                var totalRun = new Run(e.Document, total.ToString("0.00"));
                totalRun.CharacterFormat = e.Field.CharacterFormat.Clone();

                e.Inline = totalRun;
                e.Cancel = false;
            }
        };

        // Execute nested mail merge.
        document.MailMerge.Execute(projects, "Projects");

        document.Save("MergedNestedRangesOutputWithObject.%OutputFileType%");
    }
}
Imports GemBox.Document
Imports System
Imports System.Linq

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        ComponentInfo.SetLicense("FREE-LIMITED-KEY")

        Dim numberOfProjects As Integer = %ProjectsCount%
        Dim itemsPerProject As Integer = %ItemsCount%

        ' Create hierarchical data source using LINQ and anonymous types.
        Dim projects = Enumerable.Range(1, numberOfProjects).Select(
            Function(projectIndex)
                Return New With
                {
                    .Name = $"Project {projectIndex}",
                    .Items = Enumerable.Range(1, itemsPerProject).Select(
                        Function(itemIndex)

                            Dim [date] As DateTime = DateTime.Today _
                                .AddMonths(projectIndex - numberOfProjects) _
                                .AddDays(itemIndex - itemsPerProject)
                            Dim hours As Integer = itemIndex Mod 3 + 6
                            Dim unit As Double = projectIndex * 35.0
                            Dim price As Double = hours * unit

                            Return New With {.Date = [date], .Hours = hours, .Unit = unit, .Price = price}
                        End Function).ToArray()
                }
            End Function).ToArray()

        Dim document = DocumentModel.Load("%InputFileName%")

        ' Customize mail merging to achieve calculation of "TotalPrice" for each project.
        AddHandler document.MailMerge.FieldMerging,
            Sub(sender, e)
                If e.MergeContext.RangeName = "Projects" And e.FieldName = "TotalPrice" Then

                    Dim total = projects(e.MergeContext.RecordIndex).Items.Sum(Function(item) item.Price)

                    Dim totalRun As New Run(e.Document, total.ToString("0.00"))
                    totalRun.CharacterFormat = e.Field.CharacterFormat.Clone()

                    e.Inline = totalRun
                    e.Cancel = False

                End If
            End Sub

        ' Execute nested mail merge.
        document.MailMerge.Execute(projects, "Projects")

        document.Save("MergedNestedRangesOutputWithObject.%OutputFileType%")

    End Sub
End Module

See also


Next steps

GemBox.Document is a .NET component that enables you to read, write, edit, convert, and print document files from your .NET applications using one simple API. How about testing it today?

Download Buy