Copy paste excel range in outlook 2013 mail body

Question

0.00/5 (No votes)

See more:

Hello All,

I have an excel file with data in 3 sheets.
I need to copy data from sheet 1 (which is a formatted table) and paste it into mail body in outlook.

I have tried the following code which I found from net:

C#

Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
                   Microsoft.Office.Interop.Excel.Workbook wbk = xlapp.Workbooks.Open(@"D:\\" + file_name);
                   Microsoft.Office.Interop.Excel.Worksheet wsht = (Microsoft.Office.Interop.Excel.Worksheet)wbk.Worksheets.get_Item(1);
                   

                   MemoryStream ms=new MemoryStream();
                  
                   var temporaryFilepath = Path.ChangeExtension(Path.GetTempFileName(), ".html");
                   wbk.SaveAs(temporaryFilepath, Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml);

                   var result = File.ReadAllText(temporaryFilepath);

                   ms.Position=0;
                   StreamReader sr=new StreamReader(ms);
                   string summary=sr.ReadToEnd();

                   body=body+"<p>" + summary + "</p>";

Error is in this line:

C#

var result = File.ReadAllText(temporaryFilepath);

It says "the process cannot access the file in C:\\....tempfile.html because it is being used by another process."
Also the data I want to paste is in sheet 1 but here where do we mention that??

Thank you...!!

What I have tried:

i have tried sample code which is not working.

Posted 31-Mar-16 21:11pm

planetz

Updated 3-Apr-16 23:30pm

Add a Solution

Comments

ZurdoDev 1-Apr-16 8:24am

The error means you already have the excel file open. Make sure it is closed when you run the code.

FARONO 1-Apr-16 8:50am

Tried to post as Solution, but website gives "busy" error:

After wbk.SaveAs(..) you have to close it (wbk.Close) and dispose of the Excel App.

2 solutions

Solution 1

After wbk.SaveAs(..) you have to close it (wbk.Close) and dispose of the Excel App.

Posted 1-Apr-16 2:49am

FARONO

Comments

planetz 4-Apr-16 0:03am

Thank you Farono....!! It was an error I should have understood..!! Now it's gone but anyway the excel range is not getting pasted yet. So I am not marking your solution as answer because it may mislead users, since question was to paste excel range in outlook mail body.

FARONO 4-Apr-16 3:36am

How is the "result" ending up in the MemoryStream? You create a new MemoryStream (which makes 'Position=0' irrelevant), you read the contents of the converted html in 'result', but from there you start reading the memorystream 'ReadToEnd' but the ms is not defined..

You should put the html in the Stream.. (I'm not going to tell all of it, figure it out..) ;-)

planetz 4-Apr-16 5:24am

For now, I have thought of and implemented a second way and it is working fine. I just looped through the excel rows and created html table rows accordingly on the fly. I will post the code, if it helps someone. And I will surely go through your hint. Thanks once again.

FARONO 4-Apr-16 5:26am

That's even better, gives you control over the Css also.. :-)

planetz 4-Apr-16 5:32am

Yes, it does... :-)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

planetz · Accepted Answer · 2016-04-03T23:30:00

C#

Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
                   Microsoft.Office.Interop.Excel.Workbook wbk = xlapp.Workbooks.Open(@"D:\\" + file_name);
                   Microsoft.Office.Interop.Excel.Worksheet wsht = wbk.Worksheets[1];
                   Microsoft.Office.Interop.Excel.Range source = wsht.UsedRange;

                   int lastrow = source.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;

                   body=body+"<table border='1' bordercolor='solid black'>";
                   for (int i = 1; i <= lastrow;i++)
                   {
                       string bgcolor = "", fontcolor="";

                       if(i==1)    //blue
                       {
                           bgcolor = "#1885CB";
                           fontcolor = "#FDFDFD";
                       }
                       else if (i ==lastrow)   //orange
                       {
                           bgcolor = "#E85815";
                           fontcolor = "#020000";
                       }
                       else   //white
                       {
                           bgcolor = "#FDFDFD";
                           fontcolor = "#020000";
                       }
                       string col1=wsht.Range["A" + i].Text.ToString();
                       string col2 = wsht.Range["B" + i].Text.ToString();
                       body = body + "<tr><td align='center' bgcolor='" + bgcolor + "' width='100px'><font color='" + fontcolor + "' face='Calibri'>" + col1 + "</font></td><td align='center' bgcolor='" + bgcolor + "' width='100px'><font color='" + fontcolor + "' face='Calibri'>" + col2 + "</font></td></tr>";
                   }
                   body = body + "</table></br>";

                   wbk.Close();
                   wbk = null;
                   xlapp.Quit();
                   xlapp = null;

Since the body of the mail also changes on condition.