I have a macro that emails the ActiveSheet to people in a certain range on
another sheet. The sheet being emailed has a code to name the sheet the value
of cell A3. The sheet is copied into another workbook before it emails. Is
there a way to have the code to name the sheet deleted and the sheet be named
what it is when copied? I dont want anyone to be able to accidentally
change the sheet name when they receive it.

The code I use is:

Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Application.CutCopyMode = False
Set wb = ActiveWorkbook
With wb
.SaveAs ActiveSheet.Name
Dim MyArr As Variant
MyArr = ThisWorkbook.Sheets("Email").Range("b16:b31")
.SendMail MyArr, ActiveSheet.Name & " " & "2005" & " " & "-" & " " &
"Offshore P&A Activity Report" & " " & "****CONFIDENTIAL****"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub



Thanks,
Chance

Chance,

This will delete the code in Sheet2 class module

Dim VBComp As Object

Set VBComp = ThisWorkbook.VBProject.vbcomponents("Sheet2").code module
VBComp.DeleteLines 1, VBComp.CountOfLines

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chance224" wrote in message
...
I have a macro that emails the ActiveSheet to people in a certain range on
another sheet. The sheet being emailed has a code to name the sheet the
value
of cell A3. The sheet is copied into another workbook before it emails.
Is
there a way to have the code to name the sheet deleted and the sheet be
named
what it is when copied? I don't want anyone to be able to accidentally
change the sheet name when they receive it.

The code I use is:

Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Application.CutCopyMode = False
Set wb = ActiveWorkbook
With wb
.SaveAs ActiveSheet.Name
Dim MyArr As Variant
MyArr = ThisWorkbook.Sheets("Email").Range("b16:b31")
.SendMail MyArr, ActiveSheet.Name & " " & "2005" & " " & "-" & " "
&
"Offshore P&A Activity Report" & " " & "****CONFIDENTIAL****"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub



Thanks,
Chance

Thanks for the help, but Im not sure where I need to insert the code. The
sheet that is emailed is copied from the previous day. This report is done
daily so the sheet name always changes.

Chance

"Bob Phillips" wrote:

Chance,

This will delete the code in Sheet2 class module

Dim VBComp As Object

Set VBComp = ThisWorkbook.VBProject.vbcomponents("Sheet2").code module
VBComp.DeleteLines 1, VBComp.CountOfLines

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chance224" wrote in message
...
I have a macro that emails the ActiveSheet to people in a certain range on
another sheet. The sheet being emailed has a code to name the sheet the
value
of cell A3. The sheet is copied into another workbook before it emails.
Is
there a way to have the code to name the sheet deleted and the sheet be
named
what it is when copied? I don't want anyone to be able to accidentally
change the sheet name when they receive it.

The code I use is:

Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Application.CutCopyMode = False
Set wb = ActiveWorkbook
With wb
.SaveAs ActiveSheet.Name
Dim MyArr As Variant
MyArr = ThisWorkbook.Sheets("Email").Range("b16:b31")
.SendMail MyArr, ActiveSheet.Name & " " & "2005" & " " & "-" & " "
&
"Offshore P&A Activity Report" & " " & "****CONFIDENTIAL****"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub



Thanks,
Chance

I would put it in a standard code module in your Personal.xls workbook, and
assign it to a toolbar button.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chance224" wrote in message
...
Thanks for the help, but I'm not sure where I need to insert the code.
The
sheet that is emailed is copied from the previous day. This report is
done
daily so the sheet name always changes.

Chance

"Bob Phillips" wrote:

Chance,

This will delete the code in Sheet2 class module

Dim VBComp As Object

Set VBComp =
ThisWorkbook.VBProject.vbcomponents("Sheet2").code module
VBComp.DeleteLines 1, VBComp.CountOfLines

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chance224" wrote in message
...
I have a macro that emails the ActiveSheet to people in a certain
range on
another sheet. The sheet being emailed has a code to name the sheet
the
value
of cell A3. The sheet is copied into another workbook before it
emails.
Is
there a way to have the code to name the sheet deleted and the sheet
be
named
what it is when copied? I don't want anyone to be able to
accidentally
change the sheet name when they receive it.

The code I use is:

Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Application.CutCopyMode = False
Set wb = ActiveWorkbook
With wb
.SaveAs ActiveSheet.Name
Dim MyArr As Variant
MyArr = ThisWorkbook.Sheets("Email").Range("b16:b31")
.SendMail MyArr, ActiveSheet.Name & " " & "2005" & " " & "-" &
" "
&
"Offshore P&A Activity Report" & " " & "****CONFIDENTIAL****"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub



Thanks,
Chance