sending only one sheet from a workbook
 

I have a workbook with a main sheet and several additional sheets linked to
the main sheet.

Those additional sheets have a button. Clicking this button this active
sheet should be send as an attachment.
I'm doing it by using the following code:
Sub Mail_with_outlook(Str_strSheetname)
Dim bStarted As Boolean
Dim OutApp As Object
Dim OutMail As Object
Dim myAttachments As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
Dim strSheetName As String

Set OutApp = GetObject(, "Outlook.Application")
If Err < 0 Then
'Outlook wasn't running, start it from code
Set OutApp = CreateObject("Outlook.Application")
bStarted = True
End If

Set OutMail = OutApp.CreateItem(0)

Set myAttachments = OutMail.Attachments
myAttachments.Add "C:\...\xlsFile", _
olByValue, 1, "FileName"
..............

Everything is fine with this sending. But, the attachment is the whole
workbook while I need to send only the one sheet where the button was clicked.

So, it should be with the same format but without any links, only values,
and without that button on that sheet.

I'm thinking that may be I should copy this workbook to a temporary place.
Then I should delete all links from the needed sheet (but I don't know how to
do it from VB - on the actual sheet I would press the top left corner and
click copy and click past value), then delete the button (again I don't know
how I could do it in VB), then delete all other spreadsheets. After that, I
would save it and send as an attachment and then I would delete this file.

I would appreciate if anybody could advise how I could actually do it.

Thanks

sending only one sheet from a workbook
 

Hi guys,
I've managed with coping the spreadsheet, deleting links and the button from
the spreadsheet by using VB (I've created macroses and looked at their code).
I think, I'll be able to delete all not needed to be send spreadsheets as
well.

However, I'm just wondering whether it's the best way of sending the sheet.

Thanks

"Alex" wrote:

I have a workbook with a main sheet and several additional sheets linked to
the main sheet.

Those additional sheets have a button. Clicking this button this active
sheet should be send as an attachment.
I'm doing it by using the following code:
Sub Mail_with_outlook(Str_strSheetname)
Dim bStarted As Boolean
Dim OutApp As Object
Dim OutMail As Object
Dim myAttachments As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
Dim strSheetName As String

Set OutApp = GetObject(, "Outlook.Application")
If Err < 0 Then
'Outlook wasn't running, start it from code
Set OutApp = CreateObject("Outlook.Application")
bStarted = True
End If

Set OutMail = OutApp.CreateItem(0)

Set myAttachments = OutMail.Attachments
myAttachments.Add "C:\...\xlsFile", _
olByValue, 1, "FileName"
.............

Everything is fine with this sending. But, the attachment is the whole
workbook while I need to send only the one sheet where the button was clicked.

So, it should be with the same format but without any links, only values,
and without that button on that sheet.

I'm thinking that may be I should copy this workbook to a temporary place.
Then I should delete all links from the needed sheet (but I don't know how to
do it from VB - on the actual sheet I would press the top left corner and
click copy and click past value), then delete the button (again I don't know
how I could do it in VB), then delete all other spreadsheets. After that, I
would save it and send as an attachment and then I would delete this file.

I would appreciate if anybody could advise how I could actually do it.

Thanks