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

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

}

Well after much trial and error and just sitting down and actually thinking about it (and writing manually on paper how it would turn out) I finally worked it out.

It was as simple as creating an IEnumerable wrapper. See below:

internal class LinqList : IEnumerable, IEnumerable
   {
       IEnumerable items;

       internal LinqList(IEnumerable items)
       {
           this.items = items;
       }

       #region IEnumerable Members
       IEnumerator IEnumerable.GetEnumerator()
       {
           foreach (T item in items)
               yield return item;
       }

       IEnumerator IEnumerable.GetEnumerator()
       {
           IEnumerable ie = this;
           return ie.GetEnumerator();
       }
       #endregion
   }

And then just using the code to apply a select function where the field title equals the email address been used. Then simply using stringbuilder and replace to find the item and replace with the relivent text from datatable:

Variables.emailAddress = excelSheetNames[s].ToString();
               string field = cboxTo.SelectedItem.ToString();
               string email = Variables.emailAddress;

               // create a wrapper around Rows
               LinqList rows = new LinqList(dTable.Rows);

               // do a simple select
               IEnumerable selectedRows = from r in rows
                                                   where (string)r[field] == email
                                                   select r;
               // output result
               string str = rtxtBody.Text;
               string str1 = txtSubject.Text;
               StringBuilder sb = new StringBuilder(str);
               StringBuilder sa = new StringBuilder(str1);
               foreach (string item in cboxTo.Items)
               {
                   foreach (DataRow row in selectedRows)
                   {
                       string textReplacedBody = row[item].ToString();
                       string textReplaceBody = "<%" + item + "%>";
                       sb.Replace(textReplaceBody, textReplacedBody);
                       string textReplacedSub = row[item].ToString();
                       string textReplaceSub = "<%" + item + "%>";
                       sa.Replace(textReplaceSub, textReplacedSub);
                   }

               }
               Variables.richText = sb.ToString();
               Variables.subjectText = sa.ToString();
               sendEmail();

Any questions let me know.

Josh

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Josh Bauer17y ago
Well after much trial and error and just sitting down and actually thinking about it (and writing manually on paper how it would turn out) I finally worked it out.

It was as simple as creating an IEnumerable wrapper. See below:

internal class LinqList : IEnumerable, IEnumerable
   {
       IEnumerable items;

       internal LinqList(IEnumerable items)
       {
           this.items = items;
       }

       #region IEnumerable Members
       IEnumerator IEnumerable.GetEnumerator()
       {
           foreach (T item in items)
               yield return item;
       }

       IEnumerator IEnumerable.GetEnumerator()
       {
           IEnumerable ie = this;
           return ie.GetEnumerator();
       }
       #endregion
   }

And then just using the code to apply a select function where the field title equals the email address been used. Then simply using stringbuilder and replace to find the item and replace with the relivent text from datatable:

Variables.emailAddress = excelSheetNames[s].ToString();
               string field = cboxTo.SelectedItem.ToString();
               string email = Variables.emailAddress;

               // create a wrapper around Rows
               LinqList rows = new LinqList(dTable.Rows);

               // do a simple select
               IEnumerable selectedRows = from r in rows
                                                   where (string)r[field] == email
                                                   select r;
               // output result
               string str = rtxtBody.Text;
               string str1 = txtSubject.Text;
               StringBuilder sb = new StringBuilder(str);
               StringBuilder sa = new StringBuilder(str1);
               foreach (string item in cboxTo.Items)
               {
                   foreach (DataRow row in selectedRows)
                   {
                       string textReplacedBody = row[item].ToString();
                       string textReplaceBody = "<%" + item + "%>";
                       sb.Replace(textReplaceBody, textReplacedBody);
                       string textReplacedSub = row[item].ToString();
                       string textReplaceSub = "<%" + item + "%>";
                       sa.Replace(textReplaceSub, textReplacedSub);
                   }

               }
               Variables.richText = sb.ToString();
               Variables.subjectText = sa.ToString();
               sendEmail();

Any questions let me know.

Josh