Code to send just 1 worksheet in a workbook.

[Deirdre]

Good afternoon~

After completing a search on these boards for an answer to my question, I am still without an answer or direction so I shall hopefully get new information.

I have a workbook which contains many sheets. These sheets contain salary and bonus information. I would like to be able to send the sheets to the appropriate people without having to send the entire workbook as it would be very inappropriate for others to view this personal information. The email program we use is Outlook on a Microsoft Exchange server.

The sheets are named for the person it concerns, so there is no "Sheet1" etc.

Your time and assistance is very much appreciated.

 

[Juan Pablo González]

One way to go, is to copy the chosen sheet to a new workbook, and send *that* new workbook.

 

[Deirdre]

Yes I had thought of that, and that is currently what I do. I was just hoping to cut out those steps with some code if possible.

Thanks!

 

[Juan Pablo González]

In code, I *feel* that thas is the shortest code you'll get... because the other version would imply, probably, the creation of an HTML version of the sheet, which could take longer.

 

[Jim North]

Hi,

You would have to save the worksheet as a file and then send that as an attachment... and we have discussed that recently... try the search function.

Here's a start on the sending part:


Dim ol As Outlook.Application
Dim olMail As MailItem

Set ol = CreateObject("outlook.application")
Set olMail = ol.CreateItem(olMailItem)
olMail.To = "ray"
olMail.Subject = "Prod Sch"
olMail.Body = "Last Friday's Production Schedule"
olMail.Importance = olNormal
olMail.Sensitivity = olNormal
olMail.Attachments.Add ("G:ExcelFormsProd Sch")
olMail.Send

 

[Juan Pablo González]

On 2002-08-27 12:57, Jim North wrote:
You would have to save the worksheet as a file and then send that as an attachment... and we have discussed that recently... try the search function.

In this very same thread !!! :wink:

 

[Jim North]

But not in the same way...

 

[Juan Pablo González]

mhmmm... Jim... did you look at my first suggestion ?

 

[Jim North]

I've never used the smilies and stuff before... sorry it is at your expense Deirdre
!
I saw it

 

[Deirdre]

:: laughs ::