Remove code from a worksheet before emailing
another way:
Sheets("daily hectolitres").Move
will move that sheet directly to a newly created workbook
Użytkownik "Joel" napisał w wiadomości
...
Have you checked to see if the macro is actually in the workbook that gets
mails. I think not!
The following line creates a newworkbook with only one worksheet and no
macro.
Sheets("daily hectolitres").Copy
When you perform a COPY of a worksheet and you don't specify either AFTER
or
BEFORE a new workbook gets created. This new workbook is what gets
e-mailed.
This new workbook is what gets placed in the E-Mail
"LesG" wrote:
I have code in a workbook that copies a worksheet to send by email... The
code works perfectly, but the sheet I am copiyng has code that I want to
remove before emailing...
The code in the workbook module is as follows:
Sub ExtractHL()
'
' ExtractHL Macro
' Extract and send the HL file to customers
Sheets("daily hectolitres").Copy
' Open email and attach file to email
ActiveWorkbook.SaveAs Filename:="daily hectolitre.xls"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Application.Dialogs(xlDialogSendMail).Show "ABI- Daily Hectolitres",
"ABI daily hectolitres"
ActiveWorkbook.Close False
Kill "daily hectolitre.xls"
Sheets("daily procedure").Select
Range("C3").Select
End Sub
The code that I want to remove from the copied workbook is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("I4:I34")) Is Nothing Then
ActiveCell.Offset(-1, 10).Range("A1").Select
End If
If Not Intersect(Target, Me.Range("S4:S34")) Is Nothing Then
ActiveCell.Offset(0, -10).Range("A1").Select
Sheet15.Activate
End If
End Sub
Any suggestions please
|