How can I send an Excel file by email?

Question

I have an excel file (Excel 2003 / xls format) and I want to send it by email with c#. My code send it successfully, but when I try to open the response file, it seems to encoded wrongly.

For example here is the response filename:

=_utf-8_B_RWxzesOhbW9sw6FzXzIwMTJfMTBfMTZf.dat

And here is the response file itself:

=?utf-8?B?VEdWdmJIWmhjMkZ1Wk1Pelh6UXlYekZmPz0NCiA9P3V0Zi04P0I/VGtW?=\ \ =?utf-8?B?TlgwZFRXaTU0YkhNPT89?=" Content-Transfer-Encoding: base64 Content-Disposition: attachment

0M8R4KGxGuEAAAAAAAAAAAAAAAAAAAAAPgADAP7/CQAGAAAAAAAAAAAAAAABAAAA AQAAAAAAAAAAEAAAIwAAAAEAAAD+////AAAAAAAAAAD///////////////////// //////////////////////////////////////////////////////////////// ////////////////////////////// ....

Here is my code fragment:

...
var attachment = new Attachment(WriteFileToMemory("fileFullPath"), "fileName.xls");
attachment.ContentType = new ContentType("application/vnd.ms-excel");
attachmentCollection.Add(attachment);
...


private Stream WriteFileToMemory(string filePath)
{
    var memoryStream = new MemoryStream();
    _openedStreams.Add(memoryStream);
    using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
    {
        var bytes = new byte[file.Length];
        file.Read(bytes, 0, (int) file.Length);
        memoryStream.Write(bytes, 0, (int) file.Length);
        file.Close();
    }
    memoryStream.Position = 0;
    return memoryStream;
}

How can I set the attachment encoding type, and which encoding should I use with Excel files?

Please help me solve this problem. Thanks in advance.

Trin

Why don't you do: new Attachment( new FileStream("path", FileMode.Open, FileAccess.Read), "fileName.xls") ? – Hassett

Yes, you're right. Now I use your solution to load the file, but the problem is still the same. It can send the mail, but the encoding of the excel file is wrong. – Trin

Answer 1

I found a solution:

...
var attachment = CreateAttachment(WriteFileToMemory(Common.TempPath + excelName), excelName);
attachmentCollection.Add(attachment);
...

private Stream WriteFileToMemory(string filePath)
{
    var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
    _openedStreams.Add(fileStream);
    return fileStream;
}

public static Attachment CreateAttachment(Stream attachmentFile, string displayName)
{
    var attachment = new Attachment(attachmentFile, displayName);
    attachment.ContentType = new ContentType("application/vnd.ms-excel");
    attachment.TransferEncoding = TransferEncoding.Base64;
    attachment.NameEncoding = Encoding.UTF8;
    string encodedAttachmentName = Convert.ToBase64String(Encoding.UTF8.GetBytes(displayName));
    encodedAttachmentName = SplitEncodedAttachmentName(encodedAttachmentName);
    attachment.Name = encodedAttachmentName;
    return attachment;
}

private static string SplitEncodedAttachmentName(string encoded)
{
    const string encodingtoken = "=?UTF-8?B?";
    const string softbreak = "?=";
    const int maxChunkLength = 30;
    int splitLength = maxChunkLength - encodingtoken.Length - (softbreak.Length * 2);
    IEnumerable<string> parts = SplitByLength(encoded, splitLength);
    string encodedAttachmentName = encodingtoken;
    foreach (var part in parts)
    {
        encodedAttachmentName += part + softbreak + encodingtoken;
    }
    encodedAttachmentName = encodedAttachmentName.Remove(encodedAttachmentName.Length - encodingtoken.Length, encodingtoken.Length);
    return encodedAttachmentName;
}

private static IEnumerable<string> SplitByLength(string stringToSplit, int length)
{
    while (stringToSplit.Length > length)
    {
        yield return stringToSplit.Substring(0, length);
        stringToSplit = stringToSplit.Substring(length);
    }
    if (stringToSplit.Length > 0)
    {
        yield return stringToSplit;
    }
}

Based on this source: http://social.msdn.microsoft.com/Forums/en-US/dotnetframeworkde/thread/b6c764f7-4697-4394-b45f-128a24306d55

Trin

Answer 2

You are making it too complex, as @Magnus points out, new Attachment() can handle FileStreams so just pass a new filestream to the constructor.

...
var attachment = new Attachment(File.Open("fileFullPath", FileMode.Open), "fileName.xls");
attachment.ContentType = new ContentType("application/vnd.ms-excel");
attachmentCollection.Add(attachment);
...

A word of warning though, kind is off topic, you cannot send a mail like that multiple times since the stream will not always reset itself properly.

Hsining

What do you mean by stream will not always reset itself properly? I collect the streams into a collection, and after the SmtpClient sent the mail, I can close the opened streams, so I can delete the temporarily file what I attached to the mail. – Trin

@Trin Just that if you create a mail with an attachment and send it, and for somereason store the mail in case you want to resent it 5 min later the stream will not be reset properly. So create a mail and send it, dont store mails once they are created. – Hsining

To reset a stream, use stream.Position = 0; – Bonaparte

Answer 3

In case you want to generate Excel from a DataTable, and send it by mail, you can attach the excel file as follows:

Workbook theWorkbook = new Workbook();
theWorkbook.SetCurrentFormat(WorkbookFormat.Excel2007);
Worksheet theWorkSheet = theWorkbook.Worksheets.Add("Sheet1");

int iRow = 0;
int iColumn = 0;
theWorkSheet.Rows[0].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;

//Titles
foreach (DataColumn column in DataTable.Columns)
{
    theWorkSheet.Rows[iRow].Cells[iColumn].Value = column.ColumnName;
    iColumn++;
}

//Values
foreach (DataRow row in DataTable.Rows)
{
    iColumn = 0;
    iRow++;

    foreach (var item in row.ItemArray)
    {
        theWorkSheet.Rows[iRow].Cells[iColumn].Value = item.ToString();
        iColumn++;
    }                    
}
System.IO.MemoryStream theStream = new System.IO.MemoryStream();
theWorkbook.Save(theStream);
byte[] byteArr = theStream.ToArray();
System.IO.MemoryStream stream1 = new System.IO.MemoryStream(byteArr, true);
stream1.Write(byteArr, 0, byteArr.Length);
stream1.Position = 0;
message.Attachments.Add(new Attachment(stream1, "filename.xlsx"));

It worked for me at least with .NET framework 4 and Excel 2016.

Regards.

Irving

stream.Position=0; is the line which helped me. without it my attachment was just 504 bytes instead of some kBs – Katsuyama

Thanks it works. If I set theStream instead of stream1: message.Attachments.Add(new Attachment(theStream, "filename.xlsx")); it will attach an excel file but the file cannot be opened? Please could you tell why using theStream doesn't work? – Miscellaneous

Answer 4

An email message attachment name that contains non-ASCII characters and is longer than 41 UTF-8 encoded bytes is encoded two times before transmission in an application that is compiled for the .NET Framework 4. This problem occurs because of an issue in the .NET Framework 4. The SMTP encodings were rewritten to include correct folding per RFC standards of line-length limits. This behavior inserts additional carriage return line feed (CRLF) characters when the name string is too long. These additional control characters cause the attachment name to be encoded again. More you can find here http://support.microsoft.com/kb/2402064

Andrea

Answer 5

I'm resolving the same problem by correcting MailMessage properties:

bool SendEmail(string subject, string message, Attachment attachment)
{
    try
    {
        SmtpClient smtpClient = CreateProductionSMTPClient();
        MailMessage msg = new MailMessage();
        msg.Subject = subject;
        msg.Body = message;
        msg.To.Add(ConfigurationHelper.EmailTo);
        msg.From = new MailAddress(ConfigurationHelper.EmailFrom);
        msg.BodyEncoding = Encoding.UTF8;

        //commented line cause the problem
        // msg.Headers.Add("Content-Type", "text/html");
        //instead of that use next line
        msg.IsBodyHtml = true;

        if (attachment != null)
        {
            msg.Attachments.Add(attachment);
        }

        smtpClient.Send(msg);
        return true;
    }
    catch (Exception ex)
    {
        return false;
    }
}

//Attachment building
Attachment WrapExcelBytesInAttachment(byte[] excelContent)
{
    try
    {
        Stream stream = new MemoryStream(excelContent);
        Attachment attachment = new Attachment(stream, "fileName.xls");
        attachment.ContentType = new ContentType("application/vnd.ms-excel");
        return attachment;
    }
    catch (Exception ex)
    {
        return null;
    }
}

Kassiekassity