none
How to send generic list<T> in tabular form in body of mail via outlook

    Question

  • I am using C# for my problem.
    I have an Excel file which has many worksheets. From "Fisrt Sheet", I am looking for a character "x" which will be present in some cells of a specific column (occurence of "x" will be in one specific column only, in different cells of that column). I am looking for "x" and extracting the corresponding row's details in a generic list (with naming the headers of extracted field). Now, I have to send this generic list<t> in "tabular format" in body of the mail via Outlook.
    I am stuck with sending of list in tabular format in the mail body. 
    I just want to know how to send my generated list in tabular form in mail body.
    Please help me with my problem

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO;
    using System.Data;
    using Excel = Microsoft.Office.Interop.Excel;
    using Outlook = Microsoft.Office.Interop.Outlook;
    
    namespace xlsm
    {
        class New
        {
            static void Main(sting[] args)
            {
    	    sting st; 
                long rCnt, cCnt;
                long rows = 0, columns = 0;
    
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range rng;
    
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(@"F:\Doc_Excel", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets["First Sheet"];
    
    
                rng = xlWorkSheet.UsedRange;
                rows = rng.Rows.Count;
                columns = rng.Columns.Count;
    
                List<Memo> lst = new List<Memo>();
    
                for (rCnt = 1; rCnt < rows; rCnt++)
                {
                    for (cCnt = 1; cCnt < columns; cCnt++)
                    {
                        
                        if ((rng.Cells[rCnt, cCnt] as Excel.rng).Value2 != null)
                        {
                            st = (rng.Cells[rCnt, cCnt] as Excel.rng).Value2.Tosting();
                            if (st == "x")
                            {
                                Memo ms = new Memo();
                                
                                ms.MemoName = (rng.Cells[rCnt, 1] as Excel.rng).Value2.Tosting();
                                ms.Type = (rng.Cells[rCnt, 2] as Excel.rng).Value2.Tosting();
                                ms.Ext = (rng.Cells[rCnt, 3] as Excel.rng).Value2.Tosting();
                                ms.Seller = (rng.Cells[rCnt, 4] as Excel.rng).Value2.Tosting();
                                ms.Warehouse = (rng.Cells[rCnt, 5] as Excel.rng).Value2.Tosting();
                                lst.Add(ms);  
    
                            }
                        }
                       
                    }
                }
    
                try
                {
                    
                    Outlook.Application oApp = new Outlook.Application();               
                    Outlook.MailItem oMsg = (Outlook.MailItem)oApp.CreateItem(Outlook.OlItemType.olMailItem);
                    oMsg.HTMLBody = "";
                    oMsg.Subject = "Memo contents as required.";               
                    Outlook.Recipients oRecims = (Outlook.Recipients)oMsg.Recipients;               
                    Outlook.Recipient oRecip = (Outlook.Recipient)oRecims.Add("abc@xyz.com");
                    oRecip.Resolve();               
                    oMsg.Send();
                    
                    oRecip = null;
                    oRecims = null;
                    oMsg = null;
                    oApp = null;
                }
                catch (Exception ex)
                {
                }
                xlWorkBook.close(true, null, null);
                xlApp.Quit();
    
                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);
            }        
    
        }
        public class Memo
        {
            public string MemoName { get; set; }
            public string Type { get; set; }
            public string Ext { get; set; }
            public string Seller { get; set; }
            public string Warehouse { get; set; }
        }
    
    

    Saturday, April 15, 2017 10:14 AM

Answers

  • I think that a "brute force" approach could work well in this case. When building the oMsg.HTMLBody, just concatenate all your data with the interspersed html tags to build the table. Something similar to this:

    StringBuilder sb = new StringBuilder();
    sb.Append("<table>");
    foreach (var row in YourList)
    {
        sb.Append("<tr>");
        sb.Append("<td>" + row.YourFirstField + "</td>");
        //Repeat previous line for all of you fields
        sb.Append("</tr>");
    }
    sb.Append("</table>");
    oMsg.HTMLBody = sb.ToString();

     
    • Marked as answer by Kuku5 Saturday, April 15, 2017 2:58 PM
    Saturday, April 15, 2017 11:27 AM
    Moderator
  • this is extracting the information but I can't see any table borders and also the table headers are missing

    Just add to the HTML any adornments that you want. For the table borders, use <table border="1"> instead of <table>. For the headers, add a line with <tr><th>FirstHeader</th><th>SecondHeader></th>...</tr>.

    You will probably also want to add to add some styles to make it "pretty", and embed the table within a <html> and <body> like Karen demonstrated in another answer. Just think about the body of your email like a Web Page, and "paint" it using the same kind of tags that you would use if you were creating a web page.

    • Marked as answer by Kuku5 Saturday, April 15, 2017 3:25 PM
    Saturday, April 15, 2017 3:17 PM
    Moderator
  • Conceptually speaking you need to add formatting (css styles) and the proper tags.

    var sb = new StringBuilder();
    
    // create header with css styling
    sb.AppendLine("<head><style> table {font-family: arial, sans-serif; border-collapse: collapse; width: 100%;} " + 
        "td, th {border: 1px solid #dddddd;text-align: left; padding: 8px;} </style></head>");
    sb.AppendLine("<body>");
    sb.AppendLine("<table>");
    sb.AppendLine("<tr>");
    
    // add one <th> tag for each column for the first row in the table
    // next, iterate the cells and add rows and cells as you are currently doing 
    
    sb.AppendLine("</tr>");
    sb.AppendLine("</table>");
    sb.AppendLine("</body>");


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Kuku5 Saturday, April 15, 2017 2:57 PM
    • Unmarked as answer by Kuku5 Saturday, April 15, 2017 3:20 PM
    • Marked as answer by Kuku5 Saturday, April 15, 2017 3:24 PM
    Saturday, April 15, 2017 1:16 PM
    Moderator

All replies

  • I think that a "brute force" approach could work well in this case. When building the oMsg.HTMLBody, just concatenate all your data with the interspersed html tags to build the table. Something similar to this:

    StringBuilder sb = new StringBuilder();
    sb.Append("<table>");
    foreach (var row in YourList)
    {
        sb.Append("<tr>");
        sb.Append("<td>" + row.YourFirstField + "</td>");
        //Repeat previous line for all of you fields
        sb.Append("</tr>");
    }
    sb.Append("</table>");
    oMsg.HTMLBody = sb.ToString();

     
    • Marked as answer by Kuku5 Saturday, April 15, 2017 2:58 PM
    Saturday, April 15, 2017 11:27 AM
    Moderator
  • @Alberto, this is extracting the information but I can't see any table borders and also the table headers are missing under which the extracted data should be filled.

    Table header information is as under:

    Column 1 - MemoName

    Column 1 - Type

    Column 1 - Ext

    Column 1 - Seller

    Column 1 - Warehouse

    Also the header should be in black color and the extracted details in red.

    Can you please assist me with this?

    Now the code is like this:

    using System;
        using System.Collections.Generic;
        using System.Linq;
        using System.Text;
        using System.IO;
        using System.Data;
        using Excel = Microsoft.Office.Interop.Excel;
        using Outlook = Microsoft.Office.Interop.Outlook;
     
        namespace xlsm
        {
            class New
            {
                static void Main(sting[] args)
                {
                sting st; 
                    long rCnt, cCnt;
                    long rows = 0, columns = 0;
     
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range rng;
     
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(@"F:\Doc_Excel", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets["First Sheet"];
     
    
                rng = xlWorkSheet.UsedRange;
                rows = rng.Rows.Count;
                columns = rng.Columns.Count;
     
                List<Memo> lst = new List<Memo>();
     
                for (rCnt = 1; rCnt < rows; rCnt++)
                {
                    for (cCnt = 1; cCnt < columns; cCnt++)
                    {
     
                        if ((rng.Cells[rCnt, cCnt] as Excel.rng).Value2 != null)
                        {
                            st = (rng.Cells[rCnt, cCnt] as Excel.rng).Value2.Tosting();
                            if (st == "x")
                            {
                                Memo ms = new Memo();
     
                                ms.MemoName = (rng.Cells[rCnt, 1] as Excel.rng).Value2.Tosting();
                                ms.Type = (rng.Cells[rCnt, 2] as Excel.rng).Value2.Tosting();
                                ms.Ext = (rng.Cells[rCnt, 3] as Excel.rng).Value2.Tosting();
                                ms.Seller = (rng.Cells[rCnt, 4] as Excel.rng).Value2.Tosting();
                                ms.Warehouse = (rng.Cells[rCnt, 5] as Excel.rng).Value2.Tosting();
                                lst.Add(ms);  
     
                            }
                        }
     
                    }
                }
     
                try
                {
     
                    Outlook.Application oApp = new Outlook.Application();               
                    Outlook.MailItem oMsg = (Outlook.MailItem)oApp.CreateItem(Outlook.OlItemType.olMailItem);
                    StringBuilder sb = new StringBuilder();
                    sb.Append("<table>");
                    foreach (var row in lst)
                   {
                    sb.Append("<tr>");
                    sb.Append("<td>" + row.MemoName + "</td>");
                    sb.Append("<td>" + row.Type + "</td>");
                    sb.Append("<td>" + row.Ext + "</td>");
                    sb.Append("<td>" + row.Seller + "</td>");
                    sb.Append("<td>" + row.Warehouse + "</td>");
                    sb.Append("</tr>");
                   }
                    sb.Append("</table>");
                    oMsg.HTMLBody = sb.ToString();
                    oMsg.Subject = "Memo contents as required.";               
                    Outlook.Recipients oRecims = (Outlook.Recipients)oMsg.Recipients;               
                    Outlook.Recipient oRecip = (Outlook.Recipient)oRecims.Add("abc@xyz.com");
                    oRecip.Resolve();               
                    oMsg.Send();
     
                    oRecip = null;
                    oRecims = null;
                    oMsg = null;
                    oApp = null;
                }
                catch (Exception ex)
                {
                }
                xlWorkBook.close(true, null, null);
                xlApp.Quit();
     
                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);
            }        
     
        }
        public class Memo
        {
            public string MemoName { get; set; }
            public string Type { get; set; }
            public string Ext { get; set; }
            public string Seller { get; set; }
            public string Warehouse { get; set; }
        }

    • Edited by Kuku5 Saturday, April 15, 2017 12:56 PM
    Saturday, April 15, 2017 12:05 PM
  • I am using C# for my problem.
    I have an Excel file which has many worksheets. From "Fisrt Sheet", I am looking for a character "x" which will be present in some cells of a specific column (occurence of "x" will be in one specific column only, in different cells of that column). I am looking for "x" and extracting the corresponding row's details in a generic list (with naming the headers of extracted field). Now, I have to send this generic list in "tabular format" in body of the mail via Outlook.
    I am getting the output but not in proper format. My code is extracting the information but I can't see any table borders and also the table headers are missing under which the extracted data should be filled.
    Table header information should be like this:
    Column 1 - MemoName
    Column 1 - Type
    Column 1 - Ext
    Column 1 - Seller
    Column 1 - Warehouse
    Also the header should be in black color and the extracted details in red.
    Please help me with my problem

        using System;
        using System.Collections.Generic;
        using System.Linq;
        using System.Text;
        using System.IO;
        using System.Data;
        using Excel = Microsoft.Office.Interop.Excel;
        using Outlook = Microsoft.Office.Interop.Outlook;
     
        namespace xlsm
        {
            class New
            {
                static void Main(sting[] args)
                {
                sting st; 
                    long rCnt, cCnt;
                    long rows = 0, columns = 0;
     
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range rng;
     
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(@"F:\Doc_Excel", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets["First Sheet"];
     
    
                rng = xlWorkSheet.UsedRange;
                rows = rng.Rows.Count;
                columns = rng.Columns.Count;
     
                List<Memo> lst = new List<Memo>();
     
                for (rCnt = 1; rCnt < rows; rCnt++)
                {
                    for (cCnt = 1; cCnt < columns; cCnt++)
                    {
     
                        if ((rng.Cells[rCnt, cCnt] as Excel.rng).Value2 != null)
                        {
                            st = (rng.Cells[rCnt, cCnt] as Excel.rng).Value2.Tosting();
                            if (st == "x")
                            {
                                Memo ms = new Memo();
     
                                ms.MemoName = (rng.Cells[rCnt, 1] as Excel.rng).Value2.Tosting();
                                ms.Type = (rng.Cells[rCnt, 2] as Excel.rng).Value2.Tosting();
                                ms.Ext = (rng.Cells[rCnt, 3] as Excel.rng).Value2.Tosting();
                                ms.Seller = (rng.Cells[rCnt, 4] as Excel.rng).Value2.Tosting();
                                ms.Warehouse = (rng.Cells[rCnt, 5] as Excel.rng).Value2.Tosting();
                                lst.Add(ms);  
     
                            }
                        }
     
                    }
                }
     
                try
                {
     
                    Outlook.Application oApp = new Outlook.Application();               
                    Outlook.MailItem oMsg = (Outlook.MailItem)oApp.CreateItem(Outlook.OlItemType.olMailItem);
                    StringBuilder sb = new StringBuilder();
                    sb.Append("<table>");
                    foreach (var row in lst)
                   {
                    sb.Append("<tr>");
                    sb.Append("<td>" + row.MemoName + "</td>");
                    sb.Append("<td>" + row.Type + "</td>");
                    sb.Append("<td>" + row.Ext + "</td>");
                    sb.Append("<td>" + row.Seller + "</td>");
                    sb.Append("<td>" + row.Warehouse + "</td>");
                    sb.Append("</tr>");
                   }
                    sb.Append("</table>");
                    oMsg.HTMLBody = sb.ToString();
                    oMsg.Subject = "Memo contents as required.";               
                    Outlook.Recipients oRecims = (Outlook.Recipients)oMsg.Recipients;               
                    Outlook.Recipient oRecip = (Outlook.Recipient)oRecims.Add("abc@xyz.com");
                    oRecip.Resolve();               
                    oMsg.Send();
     
                    oRecip = null;
                    oRecims = null;
                    oMsg = null;
                    oApp = null;
                }
                catch (Exception ex)
                {
                }
                xlWorkBook.close(true, null, null);
                xlApp.Quit();
     
                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);
            }        
     
        }
        public class Memo
        {
            public string MemoName { get; set; }
            public string Type { get; set; }
            public string Ext { get; set; }
            public string Seller { get; set; }
            public string Warehouse { get; set; }
        }

    Saturday, April 15, 2017 12:58 PM
  • Conceptually speaking you need to add formatting (css styles) and the proper tags.

    var sb = new StringBuilder();
    
    // create header with css styling
    sb.AppendLine("<head><style> table {font-family: arial, sans-serif; border-collapse: collapse; width: 100%;} " + 
        "td, th {border: 1px solid #dddddd;text-align: left; padding: 8px;} </style></head>");
    sb.AppendLine("<body>");
    sb.AppendLine("<table>");
    sb.AppendLine("<tr>");
    
    // add one <th> tag for each column for the first row in the table
    // next, iterate the cells and add rows and cells as you are currently doing 
    
    sb.AppendLine("</tr>");
    sb.AppendLine("</table>");
    sb.AppendLine("</body>");


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Kuku5 Saturday, April 15, 2017 2:57 PM
    • Unmarked as answer by Kuku5 Saturday, April 15, 2017 3:20 PM
    • Marked as answer by Kuku5 Saturday, April 15, 2017 3:24 PM
    Saturday, April 15, 2017 1:16 PM
    Moderator
  • this is extracting the information but I can't see any table borders and also the table headers are missing

    Just add to the HTML any adornments that you want. For the table borders, use <table border="1"> instead of <table>. For the headers, add a line with <tr><th>FirstHeader</th><th>SecondHeader></th>...</tr>.

    You will probably also want to add to add some styles to make it "pretty", and embed the table within a <html> and <body> like Karen demonstrated in another answer. Just think about the body of your email like a Web Page, and "paint" it using the same kind of tags that you would use if you were creating a web page.

    • Marked as answer by Kuku5 Saturday, April 15, 2017 3:25 PM
    Saturday, April 15, 2017 3:17 PM
    Moderator
  • I am able to make table but not column headers. Please help!
    Saturday, April 15, 2017 3:42 PM
  • I am able to make table but not column headers. Please help!
    Saturday, April 15, 2017 3:43 PM
  • Thank you so much. My work is done
    Saturday, April 15, 2017 4:29 PM