Remove code from a worksheet before emailing

  • Thread starter Thread starter LesG
  • Start date Start date
L

LesG

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
 
Hi

Just before using SaveAs, copy the required sheets to another window like;
for copying sheet1 and sheet2.

Sheets(Array("Sheet1", "Sheet2")).Select
Sheets(Array("Sheet1", "Sheet2")).Copy

and then use SaveAs to save it into a temporary location. Attach that to the
mail and then delete the file from temporary location. Please try and let me
know for further help.

If this post helps click Yes
 
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
 
Assuming the sheet don't have any code which you want to keep, try this:

Sub ExtractHL()
'
' ExtractHL Macro
' Extract and send the HL file to customers
Sheets("daily hectolitres").Copy

' Delete event code in copied sheet
Dim strName As String
strName = ActiveSheet.CodeName
With ThisWorkbook.VBProject.VBComponents(strName).CodeModule
.DeleteLines 1, .CountOfLines
End With

' 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

Regards,
Per
 
another way:

Sheets("daily hectolitres").Move

will move that sheet directly to a newly created workbook
 
Untested but try this if you have code in the sheet module

Sub test()
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String

FileExtStr = ".xls": FileFormatNum = -4143
TempFilePath = Environ$("temp") & "\"
TempFileName = "daily hectolitre"

Set sh = ThisWorkbook.Sheets("daily hectolitres")
Set wb = Workbooks.Add(1)
wb.Sheets(1).Name = "daily hectolitres"
sh.Cells.Copy wb.Sheets(1).Cells(1)

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "", _
"This is the Subject line"
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




LesG said:
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

__________ Information from ESET Smart Security, version of virus signature database 3953 (20090321) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 3953 (20090321) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Look out

This will remove the code from the original workbook

You use ThisWorkbook in the code

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




Per Jessen said:
Assuming the sheet don't have any code which you want to keep, try this:

Sub ExtractHL()
'
' ExtractHL Macro
' Extract and send the HL file to customers
Sheets("daily hectolitres").Copy

' Delete event code in copied sheet
Dim strName As String
strName = ActiveSheet.CodeName
With ThisWorkbook.VBProject.VBComponents(strName).CodeModule
.DeleteLines 1, .CountOfLines
End With

' 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

Regards,
Per




__________ Information from ESET Smart Security, version of virus signature database 3953 (20090321) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 3953 (20090321) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Hi Per,

thanks for your assistance... problem is that i get the following error (and
I do not want to change my security level from medium):
Run-time error '1004'
Programmatic access to Visual Basic project is not trusted

Thanks

Les
 
See my reply

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




LesG said:
Hi Per,

thanks for your assistance... problem is that i get the following error (and
I do not want to change my security level from medium):
Run-time error '1004'
Programmatic access to Visual Basic project is not trusted

Thanks

Les


__________ Information from ESET Smart Security, version of virus signature database 3953 (20090321) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 3953 (20090321) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Hi Ron,

Hi from a warm South African evening... as ever your solution was spot on...
I replaced the code within my "Sub ExtractHL()" with your code and modified
the email code (I personally prefer
"Application.Dialogs(xlDialogSendMail).Show "ABI- Daily Hectolitres", "ABI
daily hectolitres") and it worked a dream...

You are a star

Thanks

Les
 
I understtod the code you wrote to remove the macro from the new sheet but
when the original sheet macro tries to execute this code, I get the following
error message:
"Programatic access to Visual Basic Project is not trusted"

Do anybody knows how can I eliminate this?
 
In xl2003 menus:
Tools|Macro|Security|trusted publisher tab
There's a checkbox near the bottom.

This is a user setting--not a workbook setting. Each user will have to make the
change.

Since it's a security setting, there's no way to change it in your code.
 
Back
Top