Email several sheets in one workbook password protected

[columbo1977]

Hi All

I have code that will email the active worksheet when a button is pressed.

What I need to do with this is attach several worksheets from the same workbook to this email instead of just one. and the workbook needs to be password protected when emailed as there is confidential information in it.

please see code below as an example of what I have been trying to do.

Sub Send1Sheet_ActiveWorkbook()
'Create a new Workbook from the sheet and then emails _
it to the address below

'ActiveSheet.Copy
Sheets("CM400-1", "CM400-2", "CM400-3", "CM400-4", "CM400-5", "CM400-6").Select ?????? this is what isnt working.

ActiveSheet.Protect Password:="???" ????? this and code above origionally was only used for one sheet.
With ActiveWorkbook
.SendMail Recipients:="???@???.com", _
Subject:="Test of sheet Protect"
.Close SaveChanges:=False
End With

End Sub

Private Sub CommandButton3_Click()

' This puts a date / time stamp in K13 so that you know when the form was sent

ActiveSheet.Unprotect Password:="???"
Range("P13").Select
ActiveCell.Formula = Now()

Range("B1:G70").Select
Selection.NumberFormat = "General"
Selection.Locked = True
Selection.FormulaHidden = False
Range("A1").Select

ActiveSheet.Protect Password:="???"

Call Send1Sheet_ActiveWorkbook


Thanks

Columbo1977

[Simon Lloyd]

Firstly this doesnt let Excel know which sheet(s) to work withSheets("CM400-1", "CM400-2", "CM400-3", "CM400-4", "CM400-5", "CM400-6").Select it should look like this:

Sheets(Array("CM400-1", "CM400-2", "CM400-3", "CM400-4", "CM400-5", "CM400-6").Select
then to protect the sheets (as long as no password is present!) something like:

For Each Worksheet in Sheets(Array("CM400-1", "CM400-2", "CM400-3", "CM400-4", "CM400-5", "CM400-6")
worksheet.protect password:= "???"
Next Worksheet

[Simon Lloyd]

I just noticed this:


password protected when emailed as there is confidential information in it.

password protecting a worksheet does not prevent the data being seen!, you are only protecting it so that it cannot be changed.

[columbo1977]

Thanks for that Simon, it comes up with an error on

Worksheet.Protect Password:="???"

Application defined or object defined error

also it is the workbook that they are copied into that i would liek to password protect so that it can only be opened by password.

in the past I have just protected the sheet but security required anything sent by email to be passworded

Thanks so far

[Simon Lloyd]

Ok password protect a workbook like this:
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\User\My Documents\Book1.xls", FileFormat:= _
xlNormal, Password:="1234", WriteResPassword:="", ReadOnlyRecommended:= _
True, CreateBackup:=False

[columbo1977]

Ok password protect a workbook like this:
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\User\My Documents\Book1.xls", FileFormat:= _
xlNormal, Password:="1234", WriteResPassword:="", ReadOnlyRecommended:= _
True, CreateBackup:=False

How do I do this without specifying save path as this will goto different people and I cannot specify the path for all of them, can it not just password protect the file as it attaches to an email afetr this.


Worksheet.Protect Password:="???"

Application defined or object defined error


also do you know why the origional one isnt working as I still need to protect the sheet.

[Simon Lloyd]

no file path, have a play around with it it's a good learning curve!
ActiveWorkbook.SaveAs Filename:="Book1.xls", FileFormat:= _
xlNormal, Password:="1234", WriteResPassword:="", ReadOnlyRecommended:= _
True, CreateBackup:=False
this worked perfect for me:

For Each Worksheet In Sheets(Array("Sheet1", "sheet2", "sheet3"))
Worksheet.Protect Password:="???"
Next Worksheet

[columbo1977]

Hi

All seems to work now apart from password protecting the file.

also is there a bit of code to break the links as when I opened the emailed copy it was asking me if I would like to update as it is linked to the origional file?

Thanks so far.

[Simon Lloyd]

Record your actions using the macro recorder when using Edit, Links to break links!