Creating Excel as Datasource for Mail Merge using C#

We can use excel as datasource for mail merge.We have to write the header and records in excel file which will act as the datasource for word mail merge.

The first row of excel will be treated as the header in the excel.

while using word as datasource and create a the word datasource we get an error “String longer than 255 characters ” when the concatenated fields string length increases 255. To overcome we can use excel as datasource.

Download Zip

Coding :

//Creating Excel file
private void CreateMailMergeExcelDataFile()
{
try
{
string[] fileds,record1;
 
Object oName = "C:\\TempDoc.xls";
string strHeader = "FirstName, LastName, Address, CityStateZip";
string strRecord1 = "John,Roy,31 New street,320009";
 
MSExcel.Application excelapp;
MSExcel.Workbook excelwrbook;
 
excelapp = new Microsoft.Office.Interop.Excel.Application();
excelapp.Visible = true;
MSExcel.Worksheet ws = new MSExcel.WorksheetClass();
 
excelwrbook = excelapp.Workbooks.Add(objMissing);
 
ws = (MSExcel.Worksheet)excelapp.ActiveWorkbook.ActiveSheet;
 
fileds = strHeader.Split(',');
record1 = strRecord1.Split(',');
//writing in excel you Can use datatable and Get the records and loop.here for
sample i have writing keeping two strings 
 
for (int i = 0; i < j =" 0;" style="color: rgb(0, 102, 0);"  
 //saving the excel workbook
excelwrbook.SaveAs(oName, 
MSExcel.XlFileFormat.xlTemplate, objMissing, 
objMissing, objMissing,objMissing, 
MSExcel.XlSaveAsAccessMode.xlExclusive,
objMissing, objMissing, objMissing, 
objMissing, objMissing);
excelapp.Quit();
 
//opening the excel to act as a datasource for word mail merge
wrdDoc.MailMerge.OpenDataSource("C:\\TempDoc.xls", 
ref objMissing, ref objMissing, ref objMissing, ref objMissing, 
ref objMissing,ref objMissing, ref objMissing, 
ref objMissing,ref objMissing, ref objMissing,
ref objMissing, ref oQuery,ref objMissing, ref objMissing,
ref objMissing);
 
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex);
}
}
 
private void button1_Click(object sender, System.EventArgs e)
{
try
{
Word.Selection wrdSelection;
Word.MailMerge wrdMailMerge;
Word.MailMergeFields wrdMergeFields;
Word.Table wrdTable;
string StrToAdd;
 
wrdApp = new Word.Application();
wrdApp.Visible = false;
 
// Add a new document.
wrdDoc = wrdApp.Documents.Add(ref objMissing, ref objMissing,
ref objMissing, ref objMissing);
wrdDoc.Select();
 
wrdSelection = wrdApp.Selection;
wrdMailMerge = wrdDoc.MailMerge;
 
// Create a MailMerge Data file using excel 
CreateMailMergeExcelDataFile();
// Create a string and insert it into the document.
StrToAdd = "Mail Merge";
wrdSelection.ParagraphFormat.Alignment =
Word.WdParagraphAlignment.wdAlignParagraphCenter;
wrdSelection.TypeText(StrToAdd);
 
InsertLines(2);
 
// Insert merge data.
wrdSelection.ParagraphFormat.Alignment =
Word.WdParagraphAlignment.wdAlignParagraphLeft;
wrdMergeFields = wrdMailMerge.Fields;
wrdMergeFields.Add(wrdSelection.Range, "FirstName");
wrdSelection.TypeText(" ");
wrdMergeFields.Add(wrdSelection.Range, "LastName");
wrdSelection.TypeParagraph();
 
wrdMergeFields.Add(wrdSelection.Range, "Address");
wrdSelection.TypeParagraph();
wrdMergeFields.Add(wrdSelection.Range, "CityStateZip");
 
InsertLines(2);
 
wrdSelection.ParagraphFormat.Alignment =
Word.WdParagraphAlignment.wdAlignParagraphRight;
 
Object objDate = "dddd, MMMM dd, yyyy";
wrdSelection.InsertDateTime(ref objDate, ref oFalse, ref objMissing,
ref objMissing, ref objMissing);
 
InsertLines(2);
 
wrdSelection.ParagraphFormat.Alignment =
Word.WdParagraphAlignment.wdAlignParagraphJustify;
 
wrdSelection.TypeText("Dear ");
wrdMergeFields.Add(wrdSelection.Range, "FirstName");
wrdSelection.TypeText(",");
InsertLines(1);
 
StrToAdd = "Thank you for using Mail Merge.";
wrdSelection.TypeText(StrToAdd);
// Perform mail merge.
wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execute(ref oFalse);
 
// Close the original form document.
wrdDoc.Saved = true;
wrdDoc.Close(ref oFalse, ref objMissing, ref objMissing);
 
 // Makes the merged doc visible
wrdApp.Visible = true;
 
// Release References.
wrdSelection = null;
wrdMailMerge = null;
wrdMergeFields = null;
wrdDoc = null;
wrdApp = null;
 
// Clean up temp file.
System.IO.File.Delete("C:\\TempDoc.xls");
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex);
}
}

Mail Merge Using C#

I was looking for doing a mail merge application and went through the web and found a good article from Microsoft link .This automates Microsoft Word to perform Mail Merge using C#.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Word = Microsoft.Office.Interop.Word;namespace mailmersamp1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

Word.Application wrdApp;
Word._Document wrdDoc;
Object oMissing = System.Reflection.Missing.Value;
Object oFalse = false;

private void InsertLines(int LineNum)
{
int iCount;

// Insert “LineNum” blank lines.
for (iCount = 1; iCount {
wrdApp.Selection.TypeParagraph();
}
}

private void FillRow(Word._Document oDoc, int Row, string Text1, string Text2, string Text3, string Text4)
{
// Insert the data into the specific cell.
oDoc.Tables[1].Cell(Row, 1).Range.InsertAfter(Text1);
oDoc.Tables[1].Cell(Row, 2).Range.InsertAfter(Text2);
oDoc.Tables[1].Cell(Row, 3).Range.InsertAfter(Text3);
oDoc.Tables[1].Cell(Row, 4).Range.InsertAfter(Text4);
}

private void CreateMailMergeDataFile()
{
Word._Document oDataDoc;
int iCount;
Object oName = “C:\TempDoc.doc”;
Object oHeader = “FirstName, LastName, Address, CityStateZip”; wrdDoc.MailMerge.CreateDataSource(ref oName, ref oMissing,ref oMissing, ref oHeader, ref oMissing, ref oMissing,ref oMissing, ref oMissing, ref oMissing);

// Open the file to insert data.
oDataDoc = wrdApp.Documents.Open(ref oName, ref oMissing,ref oMissing, ref oMissing, ref oMissing, ref oMissing,ref oMissing, ref oMissing, ref oMissing, ref oMissing,ref oMissing, ref oMissing, ref oMissing, ref oMissing,ref oMissing, ref oMissing);

for (iCount = 1; iCount {
oDataDoc.Tables[1].Rows.Add(ref oMissing);
}
// Fill in the data.

FillRow(oDataDoc, 2, “Roy”, “John”,”45 Main Street”, “Chennai, IND 6399873″);
FillRow(oDataDoc, 3, “Jan”, “Mike”,”34 cross Street”, “Trichy, IND 620006″);

// Save and close the file.
oDataDoc.Save();
oDataDoc.Close(ref oFalse, ref oMissing, ref oMissing);
}

private void button1_Click(object sender, System.EventArgs e)
{
Word.Selection wrdSelection;
Word.MailMerge wrdMailMerge;
Word.MailMergeFields wrdMergeFields;
Word.Table wrdTable;
string StrToAdd;

wrdApp = new Word.Application();
wrdApp.Visible = false;

// Add a new document.
wrdDoc = wrdApp.Documents.Add(ref oMissing, ref oMissing,ref oMissing, ref oMissing);
wrdDoc.Select();

wrdSelection = wrdApp.Selection;
wrdMailMerge = wrdDoc.MailMerge;

// Create a MailMerge Data file.
CreateMailMergeDataFile();

// Create a string and insert it into the document.
StrToAdd = “Mail Merge”;
wrdSelection.ParagraphFormat.Alignment =Word.WdParagraphAlignment.wdAlignParagraphCenter;
wrdSelection.TypeText(StrToAdd);

InsertLines(2);

// Insert merge data.
wrdSelection.ParagraphFormat.Alignment =
Word.WdParagraphAlignment.wdAlignParagraphLeft;
wrdMergeFields = wrdMailMerge.Fields;
wrdMergeFields.Add(wrdSelection.Range, “FirstName”);
wrdSelection.TypeText(” “);
wrdMergeFields.Add(wrdSelection.Range, “LastName”);
wrdSelection.TypeParagraph();

wrdMergeFields.Add(wrdSelection.Range, “Address”);
wrdSelection.TypeParagraph();
wrdMergeFields.Add(wrdSelection.Range, “CityStateZip”);

InsertLines(2);

// Right justify the line and insert a date field
// with the current date.
wrdSelection.ParagraphFormat.Alignment =
Word.WdParagraphAlignment.wdAlignParagraphRight;

Object objDate = “dddd, MMMM dd, yyyy”;
wrdSelection.InsertDateTime(ref objDate, ref oFalse, ref oMissing,
ref oMissing, ref oMissing);

InsertLines(2);

// Justify the rest of the document.
wrdSelection.ParagraphFormat.Alignment =
Word.WdParagraphAlignment.wdAlignParagraphJustify;

wrdSelection.TypeText(“Dear “);
wrdMergeFields.Add(wrdSelection.Range, “FirstName”);
wrdSelection.TypeText(“,”);
InsertLines(1);

// Create a string and insert it into the document.
StrToAdd = “Thank you for using Mail Merge.”;
wrdSelection.TypeText(StrToAdd);

InsertLines(2);

// Insert a new table with 3 rows and 4 columns.
wrdTable = wrdDoc.Tables.Add(wrdSelection.Range, 3, 4,
ref oMissing, ref oMissing);

// Set the column widths.
wrdTable.Columns[1].SetWidth(100, Word.WdRulerStyle.wdAdjustNone);
wrdTable.Columns[2].SetWidth(100, Word.WdRulerStyle.wdAdjustNone);
wrdTable.Columns[1].SetWidth(100, Word.WdRulerStyle.wdAdjustNone);
wrdTable.Columns[2].SetWidth(100, Word.WdRulerStyle.wdAdjustNone);

// Set the shading on the first row to light gray.
wrdTable.Rows[1].Cells.Shading.BackgroundPatternColorIndex =
Word.WdColorIndex.wdGray25;

// Bold the first row.
wrdTable.Rows[1].Range.Bold = 1;

// Center the text in Cell (1,1).
wrdTable.Cell(1, 1).Range.Paragraphs.Alignment =
Word.WdParagraphAlignment.wdAlignParagraphCenter;

// Fill each row of the table with data.
FillRow(wrdDoc, 1, “Number”, “Name”,”Dept”,”Note”);
FillRow(wrdDoc, 2, “100”, “abc”,”Cse”,”New”);
FillRow(wrdDoc, 3, “101”, “def”,”Ece”,”New”);

// Go to the end of the document.
Object oConst1 = Word.WdGoToItem.wdGoToLine;
Object oConst2 = Word.WdGoToDirection.wdGoToLast;
wrdApp.Selection.GoTo(ref oConst1, ref oConst2, ref oMissing, ref oMissing);

// Create a string and insert it into the document.
StrToAdd = “n For additional information regarding the ” +
“Mail Merge, ” + “you can visit our Web Site at “;
wrdSelection.TypeText(StrToAdd);

// Insert a hyperlink to the Web page.
Object oAddress = “http://www.fordevs.com”;
Object oRange = wrdSelection.Range;
wrdSelection.Hyperlinks.Add(oRange, ref oAddress, ref oMissing,
ref oMissing, ref oMissing, ref oMissing);

// Create a string and insert it into the document
StrToAdd = “. Thank you for your interest in Mail Merge. rnrn” +
“Sincerely,rn” + “Jackrn”;
wrdSelection.TypeText(StrToAdd);

// Perform mail merge.
wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execut
e(ref oFalse);

// Close the original form document.
wrdDoc.Saved = true;
wrdDoc.Close(ref oFalse, ref oMissing, ref oMissing);

// Makes the merged doc visible
wrdApp.Visible = true;

// Release References.
wrdSelection = null;
wrdMailMerge = null;
wrdMergeFields = null;
wrdDoc = null;
wrdApp = null;

// Clean up temp file.
System.IO.File.Delete(“C:\TempDoc.doc”);
}
}
}

Download Zip

Note :

We get a error when the concatenated fields string length exceeds 255. To Overcome we can use excel as datasource instead of word.

Using Excel as Datasource