send pass protectected worksheet

sjdoss

New Member
Joined
Feb 26, 2004
Messages
1
i've been around and around the board...
does anybody know how i can send an entire worksheet via email?
the tricky part is, it has to unlock and lock the password protection.

thanks,
-Steve
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
Hi Steve, Welcome to the Board,

If we are talking about worksheet protected (not workbook protected), then:

Rightclick the sheet tab, leftclick Move or Copy...,
Select Make a Copy (box near bottom)
In the dropdown box select (New Book)

You can then email this single worksheet as a new book. It will still be protected and any event macros associated with the sheet should still work provided they only relate to that sheet. Macros associated with standard macro modules won't work as they are integral to the whole workbook. In that case you would have to email the whole workbook and get the recipient to delete unwanted worksheets.

If you need to remove the sheet protection (eg to change formulas that refer to data on other sheets to values, remove macro buttons from the copy etc) you need to know the password. Assuming that, then you can create a macro button on the master sheet and assign it to a standard module with this code:

Sub EMAIL()
ActiveSheet.Copy
ActiveSheet.Unprotect "YOUR PASSWORD HERE"
ActiveSheet.DrawingObjects.Select
Selection.Delete
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
ActiveSheet.Protect "YOUR PASSWORD HERE"
MsgBox "Email this workbook. It will then close without saving"
Application.Dialogs(xlDialogSendMail).Show
Application.DisplayAlerts = False
ActiveWorkbook.Close
End Sub

Everywhere the code says YOUR PASSWORD HERE replace it with your own password (within the quote marks)

If you do not know the password you will need a password ******* and no doubt someone on this site could provide that as worksheet passwords are not very secure. Afraid I cannot help you if you are talking about workbook protected files, someone else may know.

regards
Derek
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,896
Messages
5,766,992
Members
425,392
Latest member
Booknerd

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