rashmi bisht

rashmi bisht

  • NA
  • 2
  • 18.4k

bulk email get email fom excel heet using microsoft excel reference

Nov 20 2010 1:01 AM
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
using System.Net.Mail;

namespace Bulk_Data
{
    public partial class Form2 : Form
    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        DataColumn dc;
        string to1; string sub; string body;
        object rowindex = 1;
        int index = 1;
        object rowindex1 = 2;
        Microsoft.Office.Interop.Excel.ApplicationClass app;
        Microsoft.Office.Interop.Excel.Workbook workBook;
        Microsoft.Office.Interop.Excel.Worksheet workSheet;
        public Form2()
        {
            InitializeComponent();
                      
        }
        private void Form2_Load(object sender, EventArgs e)
        {

        }
                  
        private void upload_Click(object sender, EventArgs e)
        {
            dt.Clear();
            
            FileopenDialog1 = new OpenFileDialog();
            FileopenDialog1.Filter = "excel files (*.xls)|*.xls";
            FileopenDialog1.Title = "Select Excel File";
            if (FileopenDialog1.ShowDialog() == DialogResult.OK)
            {
                txtsmtpserver.Text = FileopenDialog1.FileName.ToString();
                string path = txtsmtpserver.Text;
                app = new Microsoft.Office.Interop.Excel.ApplicationClass();
                workBook = app.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
                while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowindex, index]).Value2 != null)
                {
                    string cname = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowindex, index]).Value2);
                    dc = new DataColumn(cname, typeof(string));
                    dt.Columns.Add(dc);
                    index++;
                }
                
                object rowindex1 = 2;
                int index1 = 0;
                int k = 2;
                while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowindex1, 1]).Value2 != null)
                {
                    rowindex1 = 2 + index1;

                    if (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowindex1, 1]).Value2.ToString() == "END")
                    {
                        break;
                    }
                    else
                    {
                        int l = 0;
                        int j = 1;
                       DataRow dr = dt.NewRow();
                       while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[k, j]).Value2 != null)
                        {

                            dr[l] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[k, j]).Value2.ToString());
                            j++;
                        }
                        k++;
                        dt.Rows.Add(dr);
                        index1++;

                    }

                }
                app.Workbooks.Close();
                dataGridView1.DataSource = dt.DefaultView;
                }
        }
        
        


        private void btnsend_Click(object sender, EventArgs e)
        {
            ds.Tables.Add(dt); //here i m getting error data set already belong to data table.
            if (ds.Tables[0].Rows.Count > 0)
            {
                int count=ds.Tables[0].Rows.Count;
                for (int i = 0; i < ds.Tables[0].Rows.Count - 1;i++ )
                {
                    
                    mailmsg(ds.Tables[0].Rows[0][i].ToString(),txtsub.Text.Trim(),txtbody.Text.Trim());
                    
                }                
            }
       }
        public void mailmsg(string to1, string sub, string body)
        {
           
            MailMessage msg = new MailMessage();
            SmtpClient emailclient = new SmtpClient("smtp.gmail.com");
            emailclient.UseDefaultCredentials = true;
            emailclient.Port = 587;
            MailAddress mfrom = new MailAddress("[email protected]", "9627796345");
            emailclient.EnableSsl = true;

            msg.To = to1;
            msg.From = mfrom;
            msg.Body = body;
            msg.Subject = sub;

            emailclient.Send(msg);

            MessageBox.Show("Message send");
        }
               
            }


        }