Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sending workbook/sheet to recipient list | Excel Discussion (Misc queries) | |||
Auto sending workbook or sheet as an attachment using Outlook | Excel Discussion (Misc queries) | |||
Sending a workbook with a message | Excel Programming | |||
Sending data to a different workbook | Excel Programming | |||
Sending data to a different workbook | Excel Programming |