Thank you very much Ron - that gives me a great start!

"Ron de Bruin" wrote:

Hi Ben

See this page for examples
http://www.rondebruin.nl/sendmail.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ben in CA" wrote in message ...
Hi,

I'd like a way that I can place a form button on a worksheet in Excel, that
when pressed, just exports the current sheet to a new Excel file, (preferably
with a message prompt to give it a file name - .xls added automatically),
saves it to the desktop, and then automatically attaches it to a new email in
Outlook. (Similar to Send to mail recipient as attachment, except only with
that sheet in the work book.)

Does anyone know if this is possible with macros? I would expect many people
would find it useful.

Any replies are appreciated!

Here's some code I found in the community that might be able to be modified.
(It's supposed to export the sheet into a different existing file.)

section 'Change Here'):
Sub Macro2()

Dim wshO As Worksheet, nameO As String 'Origin sheet
Dim wshD As Worksheet, WbkD As Workbook, nameD As String 'Destination
variables
Dim count As Long


' Set variables
'------ CHANGE HERE ------------
Set wshO = ActiveSheet
Set WbkD = Workbooks(2)
'-------------------------------
nameO = wshO.Name
count = WbkD.Sheets.count

'Get name from user
nameD = Application.InputBox("Enter new name", "New Sheet Name")
If nameD = "False" Then Exit Sub 'Cancelled by user

'Copy sheet
wshO.Copy After:=Workbooks(2).Sheets(count)
Set wshD = WbkD.Sheets(count + 1) 'new sheet is last one

'Rename
On Error Resume Next
wshD.Name = nameD
If Err < 0 Then
MsgBox "The provided name '" & nameD & "' is not valie (invalid or
already exist)" & _
vbNewLine & "Please, set it manually."
End If
End Sub

--
Regards,
Sébastien



Special Thanks in advance!

Ben