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);
}
}

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>