Prompt to enter a sheet to send to recipients

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
Hi All

I have following code:

Code:
Sub Mail_ActiveSheet()
    Dim strdate As String
    ActiveSheet.Copy
    strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
    ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
                        & " " & strdate & ".xls"
    ActiveWorkbook.SendMail "andrew.poole@knowlsley.gov.uk", _
                            "Sickness & Absence return"
    ActiveWorkbook.ChangeFileAccess xlReadOnly
    Kill ActiveWorkbook.FullName
    ActiveWorkbook.Close False
End Sub

The code emails the active sheet to the recipients shown. Is there any way to insert a msg box to ask what sheet you want to send? Also a outlook message appears when sending the sheet saying outlook is sending an email on and may contain a virus do you want to send? Is there a way to disable this message?

Thanks in advance

Andrew
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
I already started here... Looking for some code which would prompt me for what tab I would like to copy instead of the activesheet.

Any ideas?
 

Forum statistics

Threads
1,136,927
Messages
5,678,621
Members
419,776
Latest member
mikelowski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top