How to: Perform a Mail Merge

• Sep 27, 2023
• 3 minutes to read

This tutorial shows how to use the Spreadsheet Mail Merge functionality to automatically generate a document based on data retrieved from a data source.

1. Create a mail merge template and specify template ranges.

   C#

   // Create a mail merge template.
   IWorkbook workbook = spreadsheetControl.Document;
   Worksheet template = workbook.Worksheets[0];
   template.Cells["C2"].Formula = "FIELDPICTURE(\"Photo\", \"range\", C2, FALSE, 50)";
   template.Cells["C3"].Formula = "=FIELD(\"FirstName\")&\" \"&FIELD(\"LastName\")";
   template.Cells["B4"].Value = "Position:";
   template.Cells["C4"].Formula = "FIELD(\"Title\")";
   template.Cells["B5"].Value = "Birth Date:";
   template.Cells["C5"].Formula = "FIELD(\"BirthDate\")";
   template.Cells["C5"].NumberFormat = "MMMM dd, yyyy";
   template.Cells["B6"].Value = "Hire Date:";
   template.Cells["C6"].Formula = "FIELD(\"HireDate\")";
   template.Cells["C6"].NumberFormat = "MMMM dd, yyyy";
   template.Cells["B7"].Value = "Home Phone:";
   template.Cells["C7"].Formula = "FIELD(\"HomePhone\")";
   template.Cells["B8"].Value = "Address:";
   template.Cells["C8"].Formula = "=FIELD(\"Address\")&\" \"&FIELD(\"City\")";
   template.Cells["B9"].Value = "About:";
   template.Cells["C9"].Formula = "FIELD(\"Notes\")";
   
   // Set a detail range in the template.
   CellRange detail = template.Range["C1:C9"];
   detail.Name = "DETAILRANGE";
   
   // Set a header range in the template.
   CellRange header = template.Range["B1:B9"];
   header.Name = "HEADERRANGE";
   

   VB.NET

   ' Create a mail merge template.
   Dim workbook As IWorkbook = spreadsheetControl.Document
   Dim template As Worksheet = workbook.Worksheets(0)
   template.Cells("C2").Formula = "FIELDPICTURE(""Photo"", ""range"", C2, FALSE, 50)"
   template.Cells("C3").Formula = "=FIELD(""FirstName"")&"" ""&FIELD(""LastName"")"
   template.Cells("B4").Value = "Position:"
   template.Cells("C4").Formula = "FIELD(""Title"")"
   template.Cells("B5").Value = "Birth Date:"
   template.Cells("C5").Formula = "FIELD(""BirthDate"")"
   template.Cells("C5").NumberFormat = "MMMM dd, yyyy"
   template.Cells("B6").Value = "Hire Date:"
   template.Cells("C6").Formula = "FIELD(""HireDate"")"
   template.Cells("C6").NumberFormat = "MMMM dd, yyyy"
   template.Cells("B7").Value = "Home Phone:"
   template.Cells("C7").Formula = "FIELD(""HomePhone"")"
   template.Cells("B8").Value = "Address:"
   template.Cells("C8").Formula = "=FIELD(""Address"")&"" ""&FIELD(""City"")"
   template.Cells("B9").Value = "About:"
   template.Cells("C9").Formula = "FIELD(""Notes"")"
   
   ' Set a detail range in the template.
   Dim detail As CellRange = template.Range("C1:C9")
   detail.Name = "DETAILRANGE"
   
   ' Set a header range in the template.
   Dim header As CellRange = template.Range("B1:B9")
   header.Name = "HEADERRANGE"
   

   The resulting template is shown in the following image:

   

2. Define the mail merge mode and document orientation. In this example, the mail merge mode is set to Multiple Sheets so that each data record is merged into a separate worksheet in a single workbook.

   C#

   // Set the mail merge mode to "Multiple Sheets".
   workbook.DefinedNames.Add("MAILMERGEMODE", "\"Worksheets\"");
   
   // Set the document orientation.
   workbook.DefinedNames.Add("HORIZONTALMODE", "TRUE");
   

   VB.NET

   ' Set the mail merge mode to "Multiple Sheets".
   workbook.DefinedNames.Add("MAILMERGEMODE", """Worksheets""")
   
   ' Set the document orientation.
   workbook.DefinedNames.Add("HORIZONTALMODE", "TRUE")
   

3. Use the IWorkbook.MailMergeDataSource property to bind a mail merge template to a data source. Perform a mail merge using the IWorkbook.GenerateMailMergeDocuments method. The resulting workbook can be saved to a file or stream, or sent to another SpreadsheetControl for further processing.

   C#

   workbook.MailMergeDataSource = EmployeesInfo.GetData();
   // Perform a mail merge.
   IList<IWorkbook> result = workbook.GenerateMailMergeDocuments();
   result[0].SaveDocument("MailMergeResult.xlsx");
   

   VB.NET

   workbook.Document.MailMergeDataSource = EmployeesInfo.GetData()
   ' Perform a mail merge.
   Dim result As IList(Of IWorkbook) = workbook.GenerateMailMergeDocuments()
   result(0).SaveDocument("MailMergeResult.xlsx")
   

   The image below shows the result: