I have a macro that will e-mail a worksheet to coworkers. Is there a way that I can lock out this e-mail so recipients can only read the sheet and not make any changes?
I have a macro that will e-mail a worksheet to coworkers. Is there a way that I can lock out this e-mail so recipients can only read the sheet and not make any changes?
same protection you have anywhere else.
lock all the cells and protect the sheet.
locking cells is done through format=>cells, protection tab
protecting the sheet is done through tools=>Protection=>Protect sheet. If
you have excel 2002 or later, you can also uncheck the first two options so
the user can't select in the sheet.
Then save the copy of the workbook.
--
Regards,
Tom Ogilvy
"parteegolfer" wrote:
>
> I have a macro that will e-mail a worksheet to coworkers. Is there a way
> that I can lock out this e-mail so recipients can only read the sheet
> and not make any changes?
>
>
> --
> parteegolfer
> ------------------------------------------------------------------------
> parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
> View this thread: http://www.excelforum.com/showthread...hreadid=523633
>
>
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 HERE:
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks