Macro deletion through VBA

[plix]

Hi,
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, "hh.mm.ss")<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 = "xxx@yyy.com"<br>    .CC = "sss@ttt.com "<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.
Plix

 

[Oorang]

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.

 

[plix]

Hi Oorang,

I found a solution which seems to work nicely so far.

http://www.cpearson.com/excel/vbe.aspx

Thanks anyway,

Plix

 

[RoryA]

If the code is not worksheet event code, you could simply copy the worksheets to a new workbook and mail that.

 

[plix]

Rorya,

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

Plix