Hi,
I am using Aspose to generate Excel files and send emails. I want to send 1st sheet of an excel file as an HTML body in the email. How do I do that? I tried reading a html file into a string and setting message.HTMLBody or message.TextBody but it doesnt work
Hi,
Thank you for using Aspose.Email.
You can send an excel file as an HTML body in the email. Please refer to the following code for your reference. It reads a sample Excel file (containing a single worksheet) stores it in memory stream as MHTML and then loads the MailMessage with this memory stream. Aspose.Email automatically sets the Excel sheet as body of the MSG file. I have attached the input Excel file and output MSG file here for your reference.
Aspose.Cells.License licCells = new Aspose.Cells.License();
licCells.SetLicense("K://Aspose.Total.Product.Family.lic");
Aspose.Email.License licEmail = new Aspose.Email.License();
licEmail.SetLicense("K://Aspose.Total.Product.Family.lic");
// Load the desired workbook from disk and save to stream as MHTML
Workbook workbook = new Workbook("K://sample.xlsx");
MemoryStream mhtmlStream = new MemoryStream();
workbook.Save(mhtmlStream, SaveFormat.MHtml);
// Load the MHTML in MailMessage
mhtmlStream.Position = 0;
MailMessage message = MailMessage.Load(mhtmlStream, MessageFormat.Mht);
message.Subject = "Testing Excel In Message";
message.From = "sender@gmail.com";
message.To = "recipient@gmail.com";
message.Save("K://TestMSG.msg",MailMessageSaveType.OutlookMessageFormatUnicode);
Thanks for your quick response. I tried your solution. First of all I had to get the latest version of Aspose.Cells which you released last week (http://www.aspose.com/community/files/51/.net-components/aspose.cells-for-.net/entry396793.aspx ) which Supports Reading and Writing MHT files
However, I get this error when reading the email in Outlook:
<!–[if gte mso 9]>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
<m:mathPr>
<m:mathFont m:val=“Cambria Math”/>
<m:brkBin m:val=“before”/>
<m:brkBinSub m:val=“–”/>
<m:smallFrac m:val=“off”/>
<m:dispDef/>
<m:lMargin m:val=“0”/>
<m:rMargin m:val=“0”/>
<m:defJc m:val=“centerGroup”/>
<m:wrapIndent m:val=“1440”/>
<m:intLim m:val=“subSup”/>
<m:naryLim m:val=“undOvr”/>
</m:mathPr></w:WordDocument>
<![endif]–><!–[if gte mso 10]>
<![endif]–>
This page uses frames, but your browser doesn't support them.
I dont want to have this problem since users’s outlook and browsers could have different settings. Instead I tried using Html. When I save the msg, the .msg file has raw html (it didnt render correctly). When I saved the output to a .htm file and opened in browser it opened fine.
Also, when I sent this as email via Aspose, it didnt send the html, just blank email. Need someone to help urgently as working on a deadline. Anyone I can reach out to in New York hours?
My code is below:
Aspose.Email.License license = new Aspose.Email.License();
license.SetLicense(@“Aspose.Total.lic”);
String filepath = @“C:\projects\XMSExcel\trunk\output\Test.xls”;
Workbook workbook = new Workbook(filepath);
MemoryStream mhtmlStream = new MemoryStream();
workbook.Save(mhtmlStream, SaveFormat.Html);
// Load the MHTML in MailMessage
mhtmlStream.Position = 0;
MailMessage message = MailMessage.Load(mhtmlStream, MessageFormat.Mht);
message.From = “xx@xx.com”;
message.To = “xx@xx.com”;
message.Subject = “Test”;
message.Attachments.Add(new Attachment(filepath));
message.Save(@“C:\projects\XMSExcel\trunk\output\test.msg”,MailMessageSaveType.OutlookMessageFormatUnicode);
SmtpClient client = new SmtpClient();
client.Host = “xxxxx”;
//Specify your mail user name
client.Username = “xxxxxxxx”;
//Specify your mail password
client.Password = “xxxxxxxxxx”;
//Specify your Port #
client.Port = 25;
client.Send(message);
Console.WriteLine(“Message sent”);
Hi,
Thank you for the feedback.
Well, I tried again my earlier method and found it working with Outlook and other browser based mail clients. For your requirements, please have a look at the following code. It saves the source excel file as HTML and sets the HTML body of the message to the saved HTML of the workbook. Finally, the email is sent using SMTP. I have also attached the screenshot of email message opened in outlook for your reference here.
// Load the desired workbook from disk and save to HTML file
Workbook workbook = new Workbook(@"source.xls");
workbook.Save("Test.html", SaveFormat.Html);
//Load the saved HTML now into a string variable
string strHtmlBody = System.IO.File.ReadAllText("Test.html");
//Define a new Message object and set its HtmlBody
MailMessage message = new MailMessage();
message.HtmlBody = strHtmlBody;
message.Subject = "Inline Excel Message";
message.From = "Username@gmail.com";
message.To = "Username@xyz.com";
message.IsBodyHtml = true;
SmtpClient client = new SmtpClient();
client.Host = "smtp.gmail.com";
//Specify your mail user name
client.Username = "Username";
//Specify your mail password
client.Password = "Password";
//Specify your Port #
client.Port = 587;
client.EnableSsl = true;
client.SecurityMode = SmtpSslSecurityMode.Explicit;
client.Send(message);
Console.WriteLine("Message sent");
<span style=“font-size:
10.0pt;font-family:“Courier New”;color:blue;mso-no-proof:yes”>string<span style=“font-size:10.0pt;font-family:“Courier New”;mso-no-proof:yes”>
strHtmlBody = System.IO.File.ReadAllText(“Test.html”);
Above seems to be working. Not sure why reading from Memory stream (html format) didnt work. Neither did mhtml work.
I see that it attaches a license.txt file. Is it because somehow its pointing to evaluation version? Aspose.Cells doesnt create extra sheet with evaluation notice. Is there separate licenses for the two? I thought it was the same license file.
Hi,
Thank you for the feedback.
You can use Memory stream as well instead of an html file written to the disk. Please have a look at the following code for your reference. It saves the Excel workbook in the memory stream and then sets the HtmlBody of the Message from this MemoryStream object.
// Load the desired workbook from disk and save it to MemoryStream
Workbook workbook = new Workbook(@“K:/source.xls”);
MemoryStream ms = new MemoryStream();
workbook.Save(ms, SaveFormat.Html);
//Seek MemoryStream position to 0
ms.Position = 0;
//Define a StreamReader object with MemoryStream
StreamReader sr = new StreamReader(ms);
//Load the saved HTML from StreamReader now into a string variable
string strHtmlBody = sr.ReadToEnd();
//Define a new Message object and set its HtmlBody
MailMessage message = new MailMessage();
message.HtmlBody = strHtmlBody;
message.Subject = “Inline Excel Message”;
message.From = “sender@gmail.com”;
message.To = “receiver@xyz.com”;
message.IsBodyHtml = true;
//SMTP code goes here
jjjain:
string strHtmlBody =
I see that it attaches a license.txt file. Is it because somehow its pointing to evaluation version? Aspose.Cells doesnt create extra sheet with evaluation notice. Is there separate licenses for the two? I thought it was the same license file.
Yes, you are right. The license.txt file you are seeing is due to the evaluation version of Aspose.Email. Aspose.Email is a separate component than Aspose.Cells and requires its own license to avoid this evaluation file attachment. You can contact our Sales Team for purchasing Aspose.Email license by visiting this LINK…
Thank you. That worked. I have two more issues.
1.
I have an excel with a cell formatted to shrink to fit. However, when I use above technique, it doesnt shrink to fit certain numbers and shows ##### instead of actual numbers.
I even tried to do explicit shrink to fit on all cells before converting to html but it didnt work.
2.
When I put an incorrect recepient (email id has valid format but doesnt exist), there is no way to know if email was succesfully sent. is there way to catch this Exception?
Any ideas?
Hi,
Thank you for using Aspose.Email.
jjjain:
- I have an excel with a cell formatted to shrink to fit. However, when I use above technique, it doesnt shrink to fit certain numbers and shows ##### instead of actual numbers.
I even tried to do explicit shrink to fit on all cells before converting to html but it didnt work.
I tried to reproduce this issue at my end with the “source.xls” file as attached here, and was not able to observe the issue. I have attached the screenshot of the source file and the MS Outlook where the email is received. As you can see, the #### in excel file are automatically converted to proper values in the output email received.
You can also try auto fit columns option of the workbook as follow and see if it resolves the issue.
workbook.Worksheets[0].AutoFitColumns();
If the issue still persists, please provide us your sample source excel file. We will look into it and assist you further.
jjjain:
- When I put an incorrect recepient (email id has valid format but doesnt exist), there is no way to know if email was succesfully sent. is there way to catch this Exception?
Any ideas?
Well you can get notifications about the successful delivery/failure of the email in case of invalid emails by using the DeliveryNotificationOptions property of MailMessage as follow:
message.DeliveryNotificationOptions=DeliveryNotificationOptions.OnFailure;
I hope this addresses your requirements.
See attached excel. Cell C15. I am also attaching in email as part of the process. Attachment is fine. Email body is not Code is below:
Aspose.Email.License license = new Aspose.Email.License();
license.SetLicense(@“Aspose.Total.lic”);
String filepath = @“C:\test\test.xls”;
Workbook workbook = new Workbook(filepath);
//Make all sheets invisible except first worksheet
MemoryStream mhtmlStream = new MemoryStream();
workbook.Save(mhtmlStream, SaveFormat.Html);
mhtmlStream.Position = 0;
StreamReader f = new StreamReader(mhtmlStream);
string htmlBody = f.ReadToEnd();
MailMessage message = new MailMessage();
message.HtmlBody = htmlBody;
// Load the MHTML in MailMessage
message.From = “xxx”;
message.To = “xxx”;
message.Subject = “Test”;
//message.TextBody = “Test mail”;
message.IsBodyHtml = true;
message.Attachments.Add(new Attachment(filepath));
message.Save(@“C:\test\test.msg”, MailMessageSaveType.OutlookMessageFormatUnicode);
SmtpClient client = new SmtpClient();
client.Host = “XXXXXXXX”;
//Specify your mail user name
client.Username = “XXXXX”;
//Specify your mail password
client.Password = “XXXXX”;
//Specify your Port #
client.Port = 25;
client.Send(message);
Console.WriteLine(“Message sent”);
Hi,
Thank you for the feedback.
Please download and use this latest version Aspose.Cells for .NET v7.3.0.4 and modify your code as follow to make it working. I have tested it and it is working fine with this latest version of Aspose.Cells as you can see in the attached screenshot. Please test it and let us know your feedback.
Workbook workbook = new Workbook("test.xls");
AutoFitterOptions optns = new AutoFitterOptions();
optns.IgnoreHidden = true;
workbook.Worksheets[0].AutoFitColumns(optns);
// Remaining code of saving workbook to Memory stream and sending email goes here
Is this a patch release? Does it have any other fixes or changes? I am worried I have to test everthing else too?
Hi,
Thank you for using Aspose products.
These are basically minor releases that are published weekly/bi-weekly for the Aspose.Cells product, and include additional features/bug fixes requested/reported by our valuable customers. These are also equally stable to be used in production environments, which means these are tested for any new features/bug fixes as well.
This doesnt seem like an elegant solution. Its Auto fitting the whole sheet (meaning increasing sheet size in the email instead of shrinking numbers to fit the cell (whatever width I desire for cell). That makes it ready email very unfriendly, you have to scroll to the right.
You should work on fixing the auto shrink value to fit column width. You can try the attached sheet.
Btw, it didnt need latest dll for this. 7.3.0.0 worked fine too
Also note, this is a bug only while saving in html format. Saving to .xls seems to be fine.
for both cases below:
workbook.Save(mhtmlStream, SaveFormat.Html);
workbook.Save(@“C:\test\test.htm”, SaveFormat.Html);
attaching the correct input excel (shrink to fit set on columns A-G)
Hi,
Thanks for the feedback.
Aspose.Cells do provide the facility of shrinking a cell’s contents to the column width using the ShrinkToFit property of the cell’s Style object. You can apply it to a cell or range of cells as follow:
Workbook workbook = new Workbook("test.xls");
Cell cellA3 = workbook.Worksheets[0].Cells["A3"];
Style style = cellA3.GetStyle();
style.ShrinkToFit = true;
StyleFlag sf = new StyleFlag();
sf.All = true;
cellA3.SetStyle(style, sf);
workbook.Save("test1.xls");
workbook.Save("test.html", SaveFormat.Html);
You can see that in the output excel file, test1.xls, the contents of cell A3 are shrinked to the width of the column. However, in case of HTML, it’s effect is not visible and that is why the “####” appear in the email. This seems to be a possible issue at the Aspose.Cells product end and you can report it at our Cells forum HERE with the above sample code and test files. Our Aspose.Cells staff will assist you further in this regard as soon as possible.
Thanks, I have started the other thread for Aspose.Cells html issue
<!–[if gte mso 9]>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val=“Cambria Math”/>
<m:brkBin m:val=“before”/>
<m:brkBinSub m:val="–"/>
<m:smallFrac m:val=“off”/>
<m:dispDef/>
<m:lMargin m:val=“0”/>
<m:rMargin m:val=“0”/>
<m:defJc m:val=“centerGroup”/>
<m:wrapIndent m:val=“1440”/>
<m:intLim m:val=“subSup”/>
<m:naryLim m:val=“undOvr”/>
</m:mathPr></w:WordDocument>
<![endif]–><!–[if gte mso 10]>
<![endif]–><span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA”>https://forum.aspose.com/t/106841
Regarding Aspose.Email, I have noticed that I am not able to do View Source on email with excel embedded as html in Outlook 2010. Same email I can do it in Outlook 2003. Is there some setting you are missing when sending the email or is it and Outlook 2010 issue?
Hi,
Thanks for the feedback.
I tried to observe the difference between Outlook 2003 and Outlook 2010 for the “View Source” issue, but couldn’t get to it. However, one thing that I could note is that in Outlook 2010, the option of View Source doesn’t appear if you click inside the table, as you can see in the attached “Outlook2010_InsideTable.PNG” screenshot. If you click outside the table, then the “View Source” option is visible to you, as you can see in the attached “Outlook2010_OutsideTable.PNG” screenshot.
I hope this helps you in getting your requirements. Please feel free to contact us if you have any additional query. We will try to assist you to our level best.
That worked. Thanks!
Hi,
You are welcome and have a good day!