Copy & send single sheet with values and no formulas

Wow! Very interesting functionality

I looked at the VBA code – but cannot figure out how to modify the code to do the following:

I have an Excel sheet named C:\Totalresult.xls and this Excel sheet contains 3 different worksheets named “Total”, “Weekly” and Daily. I would like to send an e-mail with the text-information from the cells B1:D100 in the worksheet “Total” in Spread sheet called Totalresult.xls

In other words:
I would like to copy the content in the cells and paste it into the body in an Untitled Message in Outlook – and send it automatically (from my own e-mail account sven@company.com ) . The goal is to send it every day so it would be nice to also have an e-mail Subject text taken from the cell A1 in the same worksheet “Total”. For example the cell A1 contains the text “CompanyTotalResult date: 10/10/2007”.

The mail shall be sent to two specific e-mail addresses person1@company.com and person2@company.com

Please note that the Excel sheet Totalresult.xls contains both formulas and pure text – but the e-mail shall only consist of pure “text” even if the cell is built up with a formula (For example using functions like =CONCATENATE()

I’m using Excel 2003 and Outlook 2003

Thanks a lot,
Sven

Originally Posted by Sven

Wow! Very interesting functionality

I looked at the VBA code – but cannot figure out how to modify the code to do the following:

I have an Excel sheet named C:\Totalresult.xls and this Excel sheet contains 3 different worksheets named “Total”, “Weekly” and Daily. I would like to send an e-mail with the text-information from the cells B1:D100 in the worksheet “Total” in Spread sheet called Totalresult.xls

In other words:
I would like to copy the content in the cells and paste it into the body in an Untitled Message in Outlook – and send it automatically (from my own e-mail account sven@company.com ) . The goal is to send it every day so it would be nice to also have an e-mail Subject text taken from the cell A1 in the same worksheet “Total”. For example the cell A1 contains the text “CompanyTotalResult date: 10/10/2007”.

The mail shall be sent to two specific e-mail addresses person1@company.com and person2@company.com

Please note that the Excel sheet Totalresult.xls contains both formulas and pure text – but the e-mail shall only consist of pure “text” even if the cell is built up with a formula (For example using functions like =CONCATENATE()

I’m using Excel 2003 and Outlook 2003

Thanks a lot,
Sven

Below is the outline of a procedure contained within a loop (not shown here), that I've used for several years to automatically send emails to many addresses held in a table within Excel. Hopefully you should be able to add your variables in at the appropriate places.

e.g .Subject = Worksheets("Total").Range("A1") and
.Body= Worksheets("Total").Range("B1:D100")

Please Login or Register  to view this content.

HTH

Hi, Thank for the reply Richard Ruttrey. I will look at the code and try to modify it to me needs.

I actually thought I was replying to another forum conversation http://www.excelforum.com/showthread.php?t=608877 by Leith Ross and Az-Man. But -it was apperently closed already…

Question to Leith Ross:
The last code you previously wrote in 608877 looks brilliant – and the question is how I can modify that code in order to achieve the things I wrote above.

Thanks a lot
Sven

Hi,
I’m struggling with the VBA code Leith Ross wrote in the link above and I have a few questions:

1.
When I copy paste the code into a module and then open the macro from Tools>Macro>Macros => I only see one macro named “TestEmail”
Question: Why do I not see the other macros like “EmailWorksheet(Email_Address As String, Subject As String)”

2.
When I then run the macro “TestEmail” => I get an Run-time error ‘62’: Input past end of file => And when I step into the code – it complains about line 108 “HTMLcode = HTMLfile.ReadAll”
Question: What Im I doing wrong?

2.
I’m currently only using the raw code from Leith Ross where I only changed the e-mail address on line 150
Question: Do I need to change some other things then just the e-mail address?

3.
If I instead make sure that the Excel worksheet is formatted exacley as I want to send it in the e-mail => Do I have to use the HTML stuff in the code anyway? The only thing I would like to do is to copy the content in the active worksheet – and paste it into an e-mail – and send it.


Thanks a lot,
Sven