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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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,171,280
Messages
5,874,816
Members
433,075
Latest member
DRubio

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