Send Only The Active Sheet you are on as an Attachment

[DoctorV]

I have a button on a final Output sheet which is one of many sheets i
an Excel Workbook. What I would like the code to do is to convert th
whole worksheet to values only,

Remove all worksheets but the Active sheet they are working on,

Invoke the Application.Dialogs(xlDialogSendMail).Show so that it ca
load just that sheet as an attachment into an e-mail so they can pick
name and send it to someone

How can I do tha

 

[JE McGimpsey]

Take a look at Ron deBruin's site:


http://www.rondebruin.nl/sendmail.htm

 

[DoctorV]

Thanks JE
Any way to adjust this code so that they could pick the names of who t
send this to? TxtEmail_Name is the current text box where they typ
the e-mail address. I wanted this to load the attachment into an emai
and then allow the user to pick who to send it to before actuall
sending it off. Thanks

Private Sub CmdSendActive_Click()

Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail TxtEmail_Name, _
"Single Sheet Send Example"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Su

 

[Ron de Bruin]

Hi DoctorV

See
http://www.rondebruin.nl/sendmail.htm#Tips

You must use this

If you use this line you can choose a address in the address book or type one yourself.
And also you can type some text in the body.
.SendMail "", "This is the Subject line"