Password protect workbook 1

[columbo1977]

Hi All

I have code that copies selected sheets to a new workbook then emails that file to a specific address. I want to them password protect that workbook. but I cnnot specify the locaion it will be saved as it is attached to an email? I just need it saving with a password in the email for security purposes. please see below for the code and what I am trying to do.

Sub Send1Sheet_ActiveWorkbook()

    'ActiveSheet.Copy
    Sheets(Array("1", "2", "3", "4", "5", "6")).Copy
    
    For Each Worksheet In Sheets(Array("1", "2", "3", "4", "5", "6"))
        Worksheet.Protect Password:="ORANGE"
    Next Worksheet
    
    ActiveWorkbook.SaveAs Filename:=LocalFileName, Password:="12"
    
    
   
       
    'ActiveWorkbook.SaveAs Filename:="test.xls", FileFormat:= _
    'xlNormal, Password:="1234", WriteResPassword:="x", ReadOnlyRecommended:= _
    'True, CreateBackup:=False
    Workbook.Protect Password:="test"
    
    
    With ActiveWorkbook
         .SendMail Recipients:="XXX@XXX.uk", _
          Subject:="Test"
         .Close SaveChanges:=False
    End With
    
End Sub

 

[ClulessChris]

Save it too C:Temp first, do what you have to, and then email it.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[columbo1977]

The code is in a spreadsheet that is used by a team of 23 and it is all accessed by a button to make it easy so I need this to happen in the VBA code automattically.

Columbo

 

[ClulessChris]

Yes the first action of your program saves a copy of the file to the c:temp, then do as you will.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[columbo1977]

It comes up with an error? not sure what path it saves into but these are work computers and so are very restricted?

code is below.

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

'ActiveSheet.Copy
Sheets(Array("CM400-1", "00-2", "00-3", "00-4", "00-5", "00-6")).Copy

For Each Worksheet In Sheets(Array("00-1", "00-2", "00-3", "00-4", "00-5", "00-6"))
Worksheet.Protect Password:="ORANGE"
Next Worksheet

ActiveWorkbook.SaveAs Filename:="c:\temp\test.xls", FileFormat:= _
xlNormal, Password:="1234", WriteResPassword:="x", ReadOnlyRecommended:= _
True, CreateBackup:=False


With ActiveWorkbook
.SendMail Recipients:="x@x.com", _
Subject:="Test"
.Close SaveChanges:=False
End With

End Sub

Thanks

 

[ClulessChris]

It comes up with an error?

???
Where does it break?
What is the error?

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[columbo1977]

Error 1004 cannot access c:\temp

it errors when saving

ActiveWorkbook.SaveAs Filename:="c:\temp\test.xls", FileFormat:= _
xlNormal, Password:="1234", WriteResPassword:="x", ReadOnlyRecommended:= _
True, CreateBackup:=False

They are restricted workstations

 

[PHV]

Does the workstation have a c:\temp folder ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[columbo1977]

No that is what i was saying before I dont know where it saves, and I cannot really specify a folder as too many people will be using the sheet.

That is why I wanted something that password protects the document when created so when it is attached to the email it is secure ( as can be)

Thanks

 

[fredericofonseca]

try this

dim sTemp as string
sTemp = Environ("TEMP")

ActiveWorkbook.SaveAs Filename:= sTemp & "\test.xls", FileFormat:= _
    xlNormal, Password:="1234", WriteResPassword:="x", ReadOnlyRecommended:= _
    True, CreateBackup:=False

unless your user setup's is completely messed up they should have access to their TEMP directory. This may differ from user to user, so using the above code will retrieve the correct pathname regardless of the user.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[columbo1977]

That is excellent works like a dream. Thanks for that.

If I wanted to send the entire workbook and not selective sheets what would I put in

ActiveWorkbook.copy

doesnt seem to work?