On an Excel (2003) sheet, I have a command button that executes the
following code to send an email (Outlook 2003) with the Excel workbook
attached:

================================================== =========
Private Sub CommandButton1_Click()
SendMail
End Sub
================================================== =========

Public Sub SendMail()
Dim ol As Object, myItem As Object
Dim myAtts As Outlook.Attachments

ActiveSheet.Shapes("CommandButton1").Delete

Set ol = CreateObject("outlook.application")
Set myItem = ol.CreateItem(olMailItem)
myItem.To = "Lu"
myItem.Subject = "Approval Request"
myItem.Body = "Today's numbers are attached."

Set myAtts = myItem.Attachments
myAtts.Add ActiveWorkbook.FullName

myItem.display

Set ol = Nothing
Set myItem = Nothing
Application.DisplayAlerts = False
ActiveWorkbook.Close
End Sub
================================================== =========

The command - ActiveSheet.Shapes("CommandButton1").Delete - works. The
button disappears if you set a "stop" at the next line.

However, the Excel file that is actually sent has the button still
displayed.

How can I delete the button from the Excel attachment - it will not
work for the receipient so I want it deleted.

This file is used twice a week, so I don't want to delete the button
and then save the file before sending.

Thanks,
Mike

Am 27 Apr 2006 09:51:31 -0700 schrieb White Horse:

Because you attach the saved file, no not saved changes could be considered.
Maybe it works if you use the Worksheet.MailEnvelope function?

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
-- www.vbOffice.net --


On an Excel (2003) sheet, I have a command button that executes the
following code to send an email (Outlook 2003) with the Excel workbook
attached:

================================================== =========
Private Sub CommandButton1_Click()
SendMail
End Sub
================================================== =========

Public Sub SendMail()
Dim ol As Object, myItem As Object
Dim myAtts As Outlook.Attachments

ActiveSheet.Shapes("CommandButton1").Delete

Set ol = CreateObject("outlook.application")
Set myItem = ol.CreateItem(olMailItem)
myItem.To = "Lu"
myItem.Subject = "Approval Request"
myItem.Body = "Today's numbers are attached."

Set myAtts = myItem.Attachments
myAtts.Add ActiveWorkbook.FullName

myItem.display

Set ol = Nothing
Set myItem = Nothing
Application.DisplayAlerts = False
ActiveWorkbook.Close
End Sub
================================================== =========

The command - ActiveSheet.Shapes("CommandButton1").Delete - works. The
button disappears if you set a "stop" at the next line.

However, the Excel file that is actually sent has the button still
displayed.

How can I delete the button from the Excel attachment - it will not
work for the receipient so I want it deleted.

This file is used twice a week, so I don't want to delete the button
and then save the file before sending.

Thanks,
Mike