Send email with dynamic generated excel as attachment using C# in ASP.Net

Last Reply on Aug 28, 2016 05:40 AM By dharmendr

Posted on Aug 28, 2016 05:39 AM

 

int count = 0;
string connectionstring = "Server=(local);initial catalog=Test;Trusted_Connection=True";
SqlConnection sqlConnection = new SqlConnection(connectionstring);
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
DataSet ds = new DataSet();
cmd.CommandText = "select * from Empdetails";
cmd.CommandText += " where shifttype  = @par ";
cmd.Parameters.Add("@par", SqlDbType.Int).Value = j;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
sqlConnection.Open();
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
    System.IO.StreamWriter sw_In = new System.IO.StreamWriter(@"C:\Users\God\Desktop\DataDump\" + j + "Excel.xls");
    while (reader.Read())
    {
        if (count == 0)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                MailMessage mis = new MailMessage();
                SmtpClient smtpserver = new SmtpClient("smtp.gmail.com");
                smtpserver.Credentials = new System.Net.NetworkCredential("rajesh@gmail.com", "12345");
                smtpserver.Host = "smtp.gmail.com";
                smtpserver.Port = 587;
                smtpserver.EnableSsl = true;
                mis.From = new MailAddress("rajesh@gmail.com", "Report");
                mis.IsBodyHtml = true;
                mis.To.Add("rajesh@gmail.com");
                mis.CC.Add(new MailAddress("rajesh@gmail.com"));
                mis.Subject = "Data Dump Report";
                smtpserver.Send(mis);

                sw_In.AutoFlush = true;
                sw_In.Write(reader.GetName(i) + "\t");
            }
            sw_In.Write("\n");
            count = 1;
        }
        for (int i = 0; i < reader.FieldCount; i++)
        {
            sw_In.AutoFlush = true;
            sw_In.Write(reader[i].ToString() + "\t");
        }
        sw_In.Write("\n");
    }
}
sqlConnection.Close();
reader.Close();

in desktop under the name of Data Dump folder excel file will be download. i am sending that excel file to mail. for that sending mail, i written the code above.

but when i run the above code, in mail i getting only subject as Data Dump Report. The excel file is i am not getting in mail. please help me what is the mistake in my above code What I have tried: i tried several times to send excel file to mail using c# But when i run the above code, in mail i getting only subject as Data Dump Report. The excel file is i am not getting in mail. please help me what is the mistake in my above code

 

Posted on Aug 28, 2016 05:40 AM

Hi narasiman,

Change your code with the below code.

int count = 0;
string connectionstring = "Server=(local);initial catalog=Test;Trusted_Connection=True";
SqlConnection sqlConnection = new SqlConnection(connectionstring);
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
DataSet ds = new DataSet();
cmd.CommandText = "select * from Empdetails";
cmd.CommandText += " where shifttype  = @par ";
cmd.Parameters.Add("@par", SqlDbType.Int).Value = j;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
sqlConnection.Open();
reader = cmd.ExecuteReader();
string filePath = @"C:\Users\God\Desktop\DataDump\" + j + "Excel.xls";
System.IO.StreamWriter sw_In = new System.IO.StreamWriter(filePath);
if (reader.HasRows)
{
    while (reader.Read())
    {
        if (count == 0)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                sw_In.AutoFlush = true;
                sw_In.Write(reader.GetName(i) + "\t");
            }
            sw_In.Write("\n");
            count = 1;
        }
        for (int i = 0; i < reader.FieldCount; i++)
        {
            sw_In.AutoFlush = true;
            sw_In.Write(reader[i].ToString() + "\t");
        }
        sw_In.Write("\n");
    }
    sw_In.Dispose();
}
reader.Close();
sqlConnection.Close();
MailMessage mis = new MailMessage();
mis.From = new MailAddress("rajesh@gmail.com", "Report");
mis.IsBodyHtml = true;
mis.To.Add("rajesh@gmail.com");
mis.CC.Add(new MailAddress("rajesh@gmail.com"));
mis.Subject = "Data Dump Report";
mis.Attachments.Add(new Attachment(filePath, "application/vnd.ms-excel"));
SmtpClient smtpserver = new SmtpClient("smtp.gmail.com");
smtpserver.Credentials = new System.Net.NetworkCredential("rajesh@gmail.com", "12345");
smtpserver.Host = "smtp.gmail.com";
smtpserver.Port = 587;
smtpserver.EnableSsl = true;
smtpserver.Send(mis);