Protecting mailed worksheet
 

Is there a way to do this with code to lock the sheet that is create
here which is to e-mailed in this macro? I know I van protect th
original however I would like the e-mailed copy only protected.


PROTECTION SOMEWHERE HE
Sub Mail_Range()
Dim source As Range
Dim dest As Workbook
Dim strdate As String

Set source = Nothing
On Error Resume Next
Set source = Range("A1:J100").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "The source is not a range or the sheet is protect, pleas
correct and try again.", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False
Set dest = Workbooks.Add(xlWBATWorksheet)
source.Copy
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
' If you use Excel 97 use the other example
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

strdate = Format(Now, "dd-mm-yy h-mm-ss")
With dest
.SaveAs "Selection of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail "", "This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Su

--
parteegolfe
-----------------------------------------------------------------------
parteegolfer's Profile: http://www.excelforum.com/member.php...fo&userid=3195
View this thread: http://www.excelforum.com/showthread.php?threadid=52399

Protecting mailed worksheet
 

hi parteegolfer

Before you save it protect the worksheet

With dest
..Sheets(1).Protect "Ron"
..SaveAs "Selection of " & ThisWorkbook.Name _
& " " & strdate & ".xls"


--
Regards Ron de Bruin
http://www.rondebruin.nl


"parteegolfer" wrote in message
news:parteegolfer.24wawa_1142735701.1207@excelforu m-nospam.com...

Is there a way to do this with code to lock the sheet that is created
here which is to e-mailed in this macro? I know I van protect the
original however I would like the e-mailed copy only protected.


PROTECTION SOMEWHERE HE
Sub Mail_Range()
Dim source As Range
Dim dest As Workbook
Dim strdate As String

Set source = Nothing
On Error Resume Next
Set source = Range("A1:J100").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "The source is not a range or the sheet is protect, please
correct and try again.", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False
Set dest = Workbooks.Add(xlWBATWorksheet)
source.Copy
With dest.Sheets(1)
Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
' If you use Excel 97 use the other example
Cells(1).PasteSpecial xlPasteValues, , False, False
Cells(1).PasteSpecial xlPasteFormats, , False, False
Cells(1).Select
Application.CutCopyMode = False
End With

strdate = Format(Now, "dd-mm-yy h-mm-ss")
With dest
SaveAs "Selection of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
SendMail "", "This is the Subject line"
ChangeFileAccess xlReadOnly
Kill .FullName
Close False
End With
Application.ScreenUpdating = True
End Sub


--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=523999

Protecting mailed worksheet
 

Thanks Ron,

This worked out well, I was putting the code in the wrong place but
did have the right idea. I was placing the code higher in th
application.

Thanks Again

--
parteegolfe
-----------------------------------------------------------------------
parteegolfer's Profile: http://www.excelforum.com/member.php...fo&userid=3195
View this thread: http://www.excelforum.com/showthread.php?threadid=52399