Remove code from a worksheet before emailing

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
 
J

Jacob Skaria

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
 
J

Joel

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
 
P

Per Jessen

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
 
J

Jarek Kujawa

another way:

Sheets("daily hectolitres").Move

will move that sheet directly to a newly created workbook
 
R

Ron de Bruin

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
 
R

Ron de Bruin

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
 
L

LesG

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
 
R

Ron de Bruin

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
 
L

LesG

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
 
E

Ehab Elsayed

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?
 
D

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 1004 paste method of worksheet class failed 3
Remove a date in a cell by code 3
Emailing Worksheet with VBA Code 6
Trying to run excel vba code when cell value changes from empty to entered value. 1
Can Vba workout if a selection is part of a group? 0
Clear a "moving" range offset from Target 31
Code error to mirror data from one table to another 6
emailing from Excel 2

Top