epplus - Send excel email attachment c#

Question

I have a method that creates an excel file and sends it as an email's attachment. The sending method works, but the issue is that it converts the excel to a txt file and when you open it says This file has been removed.

I use EPPLUS library to create the excel file and save it in a determined location. I can open the created file without any errors.

What can I do to make sure the excel file is sent without getting "removed"?

Method that creates the excel file

private static string CreateExcel(List<BidModel> inputBids)
{
    var fileName = string.Empty;

    using (ExcelPackage excel = new ExcelPackage())
    {
        excel.Workbook.Worksheets.Add("Worksheet1");

        var headerRow = new List<string[]>()
            {
                new string[] {"Zone", "Branch", "LC Number", "Acct No", "Customers Name", "Amount won"}
            };

        string headerRange = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";
        var worksheet = excel.Workbook.Worksheets["Worksheet1"];
        var stream = excel.Stream;
        var count = inputBids.Count();
        worksheet.Cells[headerRange].Style.Font.Bold = true;
        worksheet.Cells[headerRange].LoadFromArrays(headerRow);
        var fileN = string.Empty;

        for (int i = 2; i <= count + 1;)
        {
            foreach (var item in inputBids)
            {
                fileN = item.Zone;
                worksheet.Cells["A" + i].Value = item.Zone;
                worksheet.Cells["B" + i].Value = item.Branch;
                worksheet.Cells["C" + i].Value = item.LC_Number;
                worksheet.Cells["D" + i].Value = item.Acct_no;
                worksheet.Cells["E" + i].Value = item.Customer_name;
                worksheet.Cells["F" + i].Value = item.Amount_won;
                i++;
            }
        }

        fileName = fileN.Trim().Replace(" ", string.Empty).Replace("/", string.Empty).Replace("-", string.Empty) + DateTime.Now.ToString("yyyy-MM-dd").Replace("-", "_") + ".xlsx";
        var path = ConfigurationManager.AppSettings["FilePath"];
        //FileInfo excelFile = new FileInfo(path + fileName);                
        excel.SaveAs(new FileInfo(path + fileName));
        bool exists = excel.File.Exists;
        //created = true;
    }
    return fileName;
}

Method that I've created to send the file

public static bool SendEmailAttachment(string fileName)
{
    bool status = true;
    string mailTo = ConfigurationManager.AppSettings["ToEmail"];
    string subject = "MESSAGE";
    try
    {
        using (MailMessage mail = new MailMessage(ConfigurationManager.AppSettings["FromEmail"], mailTo))
        {
            mail.Subject = subject;
            mail.Body = "New Message";
            mail.IsBodyHtml = true;
            var path = ConfigurationManager.AppSettings["FilePath"] + fileName;

            FileInfo file = new FileInfo(path);
            ExcelPackage pkg = new ExcelPackage(file);
            var stream = pkg.File.OpenRead();
            //stream.Close();
            Attachment attchment = new Attachment(stream, "name", mediaType: MediaTypeNames.Application.Octet);
            attchment.ContentType = new ContentType("application/vnd.ms-excel");
            //add attach                    
            Attachment data = new Attachment(path);
            data.ContentType = new ContentType("application/vnd.ms-excel");
            //var attachment = CreateAttachment(WriteFileToMemory(path), fileName);
            //mail.Attachments.Add(data);
            mail.Attachments.Add(data);
            SmtpClient smtp = new SmtpClient();
            smtp.Host = ConfigurationManager.AppSettings["Host"];
            smtp.Port = int.Parse(ConfigurationManager.AppSettings["Port"]);
            //ServicePointManager.ServerCertificateValidationCallback =
            //delegate (object s, X509Certificate certificate,
            //X509Chain chain, SslPolicyErrors sslPolicyErrors)
            //{ return true; };
            smtp.Send(mail);
            //Logwriter.WriteErrorLog("Email sent : account_no = " + accountNo);
        }
    }
    catch (Exception ex)
    {
        status = false;
        LogWriter.WriteTolog("Error sending email : " + ex.Message);
    }
    return status;
}

Answer 1

Here is a complete working example of how to attach an EPPlus generated Excel file to an email message as attachment. But you have to use a MemoryStream to attach a file to a message.

//create a new memorystream for the excel file
MemoryStream ms;

//create a new ExcelPackage
using (ExcelPackage package = new ExcelPackage())
{
    //create the WorkSheet
    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet 1");

    //add some dummy data, note that row and column indexes start at 1
    Random rnd = new Random();

    for (int i = 1; i <= 3; i++)
    {
        for (int j = 1; j <= 27; j++)
        {
            worksheet.Cells[i, j].Value = rnd.Next(1, 25);
            worksheet.Cells[1, j].Value = DateTime.Now.ToString();
            worksheet.Cells[4, j].Value = "val " + rnd.Next(1, 25);
        }
    }

    //save the excel to the stream
    ms = new MemoryStream(package.GetAsByteArray());
}

//create a new mail message 
using (SmtpClient client = new SmtpClient())
using (MailMessage mail = new MailMessage())
{
    client.Host = "mail.server.com";
    client.Port = 25;
    client.Timeout = 10000;
    client.EnableSsl = false;
    client.DeliveryMethod = SmtpDeliveryMethod.Network;
    client.UseDefaultCredentials = false;
    client.Credentials = new NetworkCredential("UserName", "PassWord");

    mail.From = new MailAddress("[email protected]", "gbubemi smith");
    mail.To.Add(new MailAddress("[email protected]"));
    mail.Subject = "Send excel email attachment c#";
    mail.IsBodyHtml = true;
    mail.Body = "<html><head></head><body>Attached is the Excel sheet.</body></html>";

    //attach the excel file to the message
    mail.Attachments.Add(new Attachment(ms, "ExcelSheet1.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));

    //send the mail
    try
    {
        client.Send(mail);
    }
    catch (Exception ex)
    {
        //handle error
    }
}

//cleanup the memorystream
ms.Dispose();