Mail Merge with DataTable source in C# and VB.NET

A mail merge is a process for creating multiple personalized mail messages based on a single message template and data source. The template message contains fixed text, which will be the same in every output message, and variables, which act as placeholders that are replaced by values from the data source. Once the mail merge starts, the template message is cloned for every data element in the data source, but with the variables replaced by values from the current data element.

The following example shows how to perform a mail merge using the GemBox.Email component. A MailMessage instance is used as a template message, where Subject, BodyText and BodyHtml contain fixed text and variables, defined as %<value name>%.

Value name can contain letters or digits, but no white space, and must be surrounded with '%' characters. To, Cc, Bcc and ReplyTo are predefined value names that can be used to set the corresponding properties in the output message. All predefined names are optional except 'To', since it represents the recipient's mail address.

Screenshot

Data Table Source Screenshot

See the full code below.

1using GemBox.Email;
2using System.Collections.Generic;
3using System.Data;
4
5class Sample
6{
7    [STAThread]
8    static void Main(string[] args)
9    {
10        // If using Professional version, put your serial key below.
11        ComponentInfo.SetLicense("FREE-LIMITED-KEY");
12
13        // Create template message
14        MailMessage template = new MailMessage(new MailAddress("sender@example.com"));
15        template.Subject = "%FirstName%, check our new offer ...";
16        template.BodyText = "Dear %Title% %LastName%,\r\n" +
17                            "\r\n" +
18                            "Check out or new offer on %link%.\r\n" +
19                            "Visit us today and get additional %discount%% discount!\r\n" +
20                            "\r\n" +
21                            "Your,\r\n" +
22                            "Special Store\r\n" +
23                            "\r\n" +
24                            "This email was sent to %To%.";
25        template.BodyHtml = "<div>" +
26                                "Dear <strong>%Title% %LastName%</strong>,<br/>" +
27                                "<br/>" +
28                                "Check out our new offer on this <a href=\"%link%\">link</a>.<br/>" +
29                                "<span style=\"color:red;font-size:90%\">Visit us today and get additional %discount%% discount!</span><br/>" +
30                                "<br/>" +
31                                "Sincerly Yours,<br/>" +
32                                "Special Store<br/>" +
33                                "<br/>" +
34                                "<span style=\"color:gray;font-size:80%\">This email was sent to <a href=\"mailto:%To%\">%To%</a>.</span>" +
35                            "</div>";
36
37        // Define data table columns ...
38        DataTable data = new DataTable();
39        data.Columns.Add("To", typeof(string));
40        data.Columns.Add("Title", typeof(string));
41        data.Columns.Add("FirstName", typeof(string));
42        data.Columns.Add("LastName", typeof(string));
43        data.Columns.Add("Discount", typeof(int));
44        data.Columns.Add("Link", typeof(string));
45
46        // ... and add some data
47        data.Rows.Add("jonh@example.com", "Mr.", "John", "Doe", 10, "http://example.com/check.html?name=John");
48        data.Rows.Add("jane@example.com", "Mrs.", "Jane", "Doe", 20, "http://example.com/check.html?name=Jane");
49        data.Rows.Add("joe@example.com", "Mr.", "Joe", "Unknown", 15, "http://example.com/check.html?name=Joe");
50
51        // Perform mail merge
52        IList<MailMessage> messages = MailMerge.Merge(template, data);
53
54        // Save messages to disk
55        for (int i = 0; i < messages.Count; i++)
56            messages[i].Save("Message_" + i, MailMessageFormat.Eml);
57    }
58}
1Imports GemBox.Email
2
3Module Samples
4
5    Sub Main()
6
7        ' If using Professional version, put your serial key below.
8        ComponentInfo.SetLicense("FREE-LIMITED-KEY")
9
10        ' Create template message
11        Dim template As New MailMessage(New MailAddress("sender@example.com"))
12        template.Subject = "%FirstName%, check our new offer ..."
13        template.BodyText = "Dear %Title% %LastName%,\r\n" &
14                            "\r\n" &
15                            "Check out or new offer on %link%.\r\n" &
16                            "Visit us today and get additional %discount%% discount!\r\n" &
17                            "\r\n" &
18                            "Your,\r\n" &
19                            "Special Store\r\n" &
20                            "\r\n" &
21                            "This email was sent to %To%."
22        template.BodyHtml = "<div>" &
23                                "Dear <strong>%Title% %LastName%</strong>,<br/>" &
24                                "<br/>" &
25                                "Check out our new offer on this <a href=""%link%"">link</a>.<br/>" &
26                                "<span style=""color:red;font-size:90%"">Visit us today and get additional %discount%% discount!</span><br/>" &
27                                "<br/>" &
28                                "Sincerly Yours,<br/>" &
29                                "Special Store<br/>" &
30                                "<br/>" &
31                                "<span style=""color:gray;font-size:80%"">This email was sent to <a href=""mailto:%To%"">%To%</a>.</span>" &
32                            "</div>"
33
34        ' Define data table columns ...
35        Dim data As New DataTable()
36        data.Columns.Add("To", GetType(String))
37        data.Columns.Add("Title", GetType(String))
38        data.Columns.Add("FirstName", GetType(String))
39        data.Columns.Add("LastName", GetType(String))
40        data.Columns.Add("Discount", GetType(Integer))
41        data.Columns.Add("Link", GetType(String))
42
43        ' ... And add some data
44        data.Rows.Add("jonh@example.com", "Mr.", "John", "Doe", 10, "http://example.com/check.html?name=John")
45        data.Rows.Add("jane@example.com", "Mrs.", "Jane", "Doe", 20, "http://example.com/check.html?name=Jane")
46        data.Rows.Add("joe@example.com", "Mr.", "Joe", "Unknown", 15, "http://example.com/check.html?name=Joe")
47
48        ' Perform mail merge
49        Dim messages As IList(Of MailMessage) = MailMerge.Merge(template, data)
50
51        ' Save messages to disk
52        For i As Integer = 0 To messages.Count - 1
53            messages(i).Save("Message_" & i, MailMessageFormat.Eml)
54        Next
55
56    End Sub
57
58End Module

Check next sample or find out more about GemBox.Email and GemBox Software.