Replace tokens from excel file in email app (like mailmerge)

Question

Hello Everyone,

I've made the email and attachment section of the program, pulling an email from an excel file.

However i'm trying to work out how to create the mailmerge part. I've allowed the user of the program to select the headings of the columns of the excel document.

Say the document has Email, First Name and Last Name. It could have more of less columns but we'll use these for arguement. They can add these as tokens "<%First Name%>  in the rich text box. So i want to find these tokens and replace them with the text from that row that equals the email address used.

The code below I can't get to work, and does not include the email sending function. The email address would be selected from the combo box loaded with the form.

private void button1_Click(object sender, EventArgs e)

{

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0");

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * from [Sheet1$]", con);

DataTable dt = new DataTable();

da.Fill(dt);

string[] excelSheetNames = new string[dt.Rows.Count];

int i = 0;

int c = 0;

foreach (DataRow row in dt.Rows)

{

excelSheetNames[i] = row[c].ToString();

i++;

}

for (int s = 0; s < excelSheetNames.Length; s++)

{

listBox1.Items.Add(excelSheetNames[s].ToString());

int j = 0;

c++;

foreach (DataRow row in dt.Rows)

{

excelSheetNames[j] = row[c].ToString();

i++;

}

for (int f = 0; f < excelSheetNames.Length; f++)

{

string textReplaced = excelSheetNames[f].ToString();

foreach (string item in comboBox1.Items)

{

string textReplace = "<%" + item + "%>";

string str = richTextBox1.Text;

StringBuilder sb = new StringBuilder(str);

sb.Replace(textReplace, textReplaced);

richTextBox1.Text = sb.ToString();

}

}

}

con.Close();

}

private void Form1_Load(object sender, EventArgs e)

{

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0");

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * from [Sheet1$]", con);

DataTable dt = new DataTable();

da.Fill(dt);

string[] excelSheetNames = new string[dt.Rows.Count];

int i = 0;

int c = 0;

foreach (DataColumn column in dt.Columns)

{

excelSheetNames[i] = column.ToString();

i++;

}

for (int s = 0; s < excelSheetNames.Length; s++)

{

comboBox1.Items.Add(excelSheetNames[s].ToString());

}

con.Close();

}