Macro deletion through VBA


Board Regular
Oct 10, 2006
Here is the scenario:
I have a template (.xlt) that is used to create an Excel form. Users fill it out and then submit it by clicking on a button, which triggers the execution of a code that does the following:
- Step 1: Save the form (active workbook) in a shared folder on the network
- Step 2: Create a temp file
- Step 3: Create an Outlook e-mail object and attach the temp file to it
- Step 4: Delete the temp file
- Step 5: Display the e-mail item (user just needs to click on send)
- Step 6: Close the active workbook
Problem is the recipient of the e-mail has installed a new anti-virus and the code contained in the attachment is now causing our e-mails to be placed in quarantine. What I am trying to do is remove the code in the temp file before attaching it to the e-mail object, as the recipient does not need the code anyway. By the way I do need to keep the code in the file that we save in step 1. I found a procedure on this forum to delete code through VBA which I tried to adapt to my situation, but apparently there is something wrong with the syntax or the logic that I am using and I get stuck at the following line:

<font face=Courier New><SPAN style="color:#00007F">With</SPAN> Workbooks(TempFilePath & TempFileName & FileExtStr).VBProject</FONT>

Here is the code (wb1 is the active workbook):

<font face=Courier New><SPAN style="color:#007F00">'SAVE FILE AS</SPAN><br><SPAN style="color:#007F00">'Path depends on Immediate vs. future date (use cell value)</SPAN><br>    dater = Now()<br>    Time = Now()<br><br>    strdate = Format(dater, "mmm.dd.yyyy")<br>    strtime = Format(Time, "")<br><br>    ActiveWorkbook.SaveAS Filename:="\\tom\grp-vol1\PickUp_Request" & "\" & Range("L17").Value & "\" & Range("H17").Value & "\" & Range("F23").Value & "_" & Range("E11").Value & "_" & strdate & "_" & strtime & ".xls"<br>    <br><SPAN style="color:#007F00">'E-MAIL FILE (only if Immediate pick-up needed)</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Range("D17").Value = 1 And Range("A17").Value = 1 <SPAN style="color:#00007F">Then</SPAN><br> <br><SPAN style="color:#007F00">'Create temp file</SPAN><br>TempFilePath = Environ$("temp") & "\"<br>TempFileName = "TEST" & "_" & Range("F23").Value & "_" & Range("E11").Value & "_" & strdate & "_" & strtime & ""<br>FileExtStr = ".xls"<br><br>wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr<br><br><SPAN style="color:#007F00">'Application.EnableEvents = True</SPAN><br><br><SPAN style="color:#007F00">'Remove all code in TempFile</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">With</SPAN> Workbooks(TempFilePath & TempFileName & FileExtStr).VBProject<br>    <SPAN style="color:#00007F">For</SPAN> x = .VBComponents.Count <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1<br>            .VBComponents.Remove .VBComponents(x)<br>    <SPAN style="color:#00007F">Next</SPAN> x<br>    <SPAN style="color:#00007F">For</SPAN> x = .VBComponents.Count <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1<br>            .VBComponents(x).CodeModule.DeleteLines _<br>            1, .VBComponents(x).CodeModule.CountOfLines<br>    <SPAN style="color:#00007F">Next</SPAN> x<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br><br><SPAN style="color:#007F00">'Create mail object (attach TempFile)</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> objMail <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> objclient <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>   <br><SPAN style="color:#00007F">Set</SPAN> objMail = CreateObject("Outlook.application")<br><SPAN style="color:#00007F">Set</SPAN> objclient = objMail.createitem(0)<br>   <br><SPAN style="color:#00007F">With</SPAN> objclient<br>    .Subject = "TEST ONLY - Pick-up request" & "   Ref_" & Range("E11").Value<br>    .To = ""<br>    .CC = " "<br>    .Attachments.Add TempFilePath & TempFileName & FileExtStr<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  <br><SPAN style="color:#007F00">'Delete the temp file</SPAN><br>Kill TempFilePath & TempFileName & FileExtStr<br>        <br><SPAN style="color:#00007F">With</SPAN> Application<br>    .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    .EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br><SPAN style="color:#007F00">'Display e-mail item (user will just need to click on Send button)</SPAN><br>objclient.Display<br> <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>         <br><SPAN style="color:#007F00">'CLOSE WORKBOOK</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ThisWorkbook<br>        <SPAN style="color:#007F00">'.Saved = True</SPAN><br>        .Close<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Any help to find a solution would be greatly appreciated.

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi Plix,
Generally speaking it's better to generate the workbook without the code than try to delete the code. The VBEIDE module (as you have noticed) just doesn't play nice with a lot of AV software, and on occasion rapid manipulation of code within a project can cause corruption.
Upvote 0
If the code is not worksheet event code, you could simply copy the worksheets to a new workbook and mail that.
Upvote 0

Thank you for the input, I will try to play with the solution that you proposed as well.

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back