Excel VBA error on protected worksheet

[twcpa]

I used the following macro in a workbook (which is attached to a comman
button) to make a copy of the active sheet and e-mail it to
co-worker. My problem is that when I protect the worksheet the macr
no longer works. Is there some way to add the instructions to th
macro to unprotect the worksheet, run the macro, and then re-protec
the worksheet??

Private Sub CommandButton1_Click()
Sub Mail_ActiveSheet_Body()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "Client Change of Address"
.HTMLBody = SheetToHTML(ActiveSheet)
.Send 'or use .Display
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Public Function SheetToHTML(sh As Worksheet)
'Function from Dick Kusleika his site
'http://www.dicks-clicks.com/excel/sheettohtml.htm
'Changed by Ron de Bruin 04-Nov-2003
Dim TempFile As String
Dim Nwb As Workbook
Dim myshape As Shape
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook
For Each myshape In Nwb.Sheets(1).Shapes
myshape.Delete
Next
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss"
& ".htm"
Nwb.SaveAs TempFile, xlHtml
Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
SheetToHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function

Any help would be greatly appreciated!!

Susan M

 

[Casey]

I'm a VBA beginner, however I have fixed a similar problem with the
following.

Sub Your_Procedure

ActiveWorkbook.Sheets("Your sheet name").Unprotect (YourPassword)

YourCode

ActiveWorkbook.Sheets("Your sheet name").Protect (YourPassword)

End Sub