Remove all code and modules from the active workbook

Hello,

I am trying to use vba to remove the modules behind the active workbook. I modified code I found from an existing source http://www.cpearson.com. (The full code had additional parts I didn't need so I removed any parts that had nothing to do with stripping the VBA)

Sub SaveWithoutMacros()

'PLEASE NOTE : You must choose Tools, References and select the
'Microsoft Visual Basic for Applications Extensibility library

Dim wbActiveBook As Workbook
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents


On Error GoTo CodeError


'Now strip all VBA, modules, userforms from the copy
'This code is from Chip Pearson's website http://www.cpearson.com

Set VBComps = wbActiveBook.VBProject.VBComponents

For Each VBComp In VBComps
   Select Case VBComp.Type
      Case vbext_ct_StdModule, vbext_ct_MSForm, _
            vbext_ct_ClassModule
         VBComps.Remove VBComp
      Case Else
         With VBComp.CodeModule
            .DeleteLines 1, .CountOfLines
         End With
   End Select
Next VBComp

wbActiveBook.Save

Exit Sub

CodeError:
MsgBox Err.Description, vbExclamation, "An Error Occurred"

End Sub

When stepping through the code, it reaches the following line and I get an error message that says "Object Variable or With block variable not set.":

Set VBComps = wbActiveBook.VBProject.VBComponents

Thank you in advance for your help.

Best,
Matt

I would prefer the programatic approach. Leith, your code works if the project isn't protected. To give you some context. My spreadsheet is a form with a submit button. When the user clicks the submit button, it sends an email to an address through the user's Microsoft Outlook with a time stamped copy of the excel form saved as an attachment. Since the original form is protected, so is the saved copy. The reason I want to remove the code in the saved copy is so that the submit button in the attachment becomes dead. In otherwords, they wont be able to execute the Submit button from the copy.

Long story short, is there was way to modify what you have sent so that it can first unprotect the vba project to be deleted? I am getting a Run-time error message '50289' that says "Can't perform operation since the project is protected".

My sincere apologies if this is not clear.

Below is the code for the main macro. When it opens up a copy of the new excel under a time stamped name. When that workbook is active, I want to remove the macros right before I send it in the email. I execute the code you supplied at that point. The problem that I stated before is that I protect the code in the original workbook so the copied workbook prevents me from deleting the code due to that protection.

Sub SendEmail()

Sheets("EFRP Form").Select

'Working in 2000-2010
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim rng As Range

    
    
    Set rng = Nothing
    Set rng = Sheets("EFRP Form").Range("A1:O4")

    If Application.WorksheetFunction.CountBlank(Range("A3:N3")) > 0 Then
    MsgBox "You Left a required field in the form blank. Please fill in all required fields"
    GoTo FinishAtEnd
    End If
    
    Range("R4").Select
    ThisWorkbook.Save
    If IsEmpty(ActiveCell) = True Then
    MsgBox "Please put at least your own email in the CC field"
    GoTo FinishAtEnd
    End If

    Set wb1 = ActiveWorkbook

    If Val(Application.Version) >= 12 Then
        If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
                   "be no VBA code in the file you send. Save the" & vbNewLine & _
                   "file first as xlsm and then try the macro again.", vbInformation
            Exit Sub
        End If
    End If

    With Application
        .ScreenUpdating = True
        .EnableEvents = False
    End With

    'Make a copy of the file/Open it/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    TempFileName = wb1.Name & " Sent " & Format(Now, "dd-mmm-yy hh-mm-ss AM/PM")
    FileExtStr = "." & LCase(Right(wb1.Name, _
                                   Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    Application.Run "RemoveAllMacros"

    On Error Resume Next
    With OutMail
        .To = Range("R2").Value
        .CC = Range("R4").Value
        .Subject = Range("R3").Value
        .HTMLBody = RangetoHTML(rng)
        .Attachments.Add ActiveWorkbook.FullName
        'You can add other files also like this
        .Attachments.Add (Range("R5").Value)
        .Send   'or use .Display
    End With
    On Error GoTo 0

    wb2.Close SaveChanges:=False

    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    Range("A3:O3").ClearContents
    Range("R4:R5").ClearContents
    Range("M3").Value = Date
    MsgBox "The Form has been submitted. Please check your sent box in outlook to verify that it went through properly.", vbExclamation, "Verify Sent Email"
    
    ThisWorkbook.Save
    
    
    
    
    
FinishAtEnd:

End Sub

'Written: March 08, 2008
'Author:  Leith Ross
'Summary: Removes all macro code and modules from the Active Workbook.


Sub RemoveAllMacros()

  Dim VBcomp As Object
  Dim VBproj As Object
  
    Set VBproj = Application.VBE.ActiveVBProject
    
      For Each VBcomp In VBproj.VBComponents
        Select Case VBcomp.Type
          Case Is = 1, 2, 3 'vbext_ct_StdModule, vbext_ct_ClassModule, vbext_ct_MSForm
            VBproj.VBComponents.Remove VBcomp
          Case Is = 100     'vbext_ct_Document
            With VBcomp.CodeModule
              .DeleteLines 1, .CountOfLines
            End With
        End Select
     Next VBcomp
     
End Sub