Clear Macros and Code Prior To Sending Worksheet to Outlook

loss1003

Board Regular
Joined
Jul 2, 2008
Messages
100
How can I amend the following code to send to outlook rather then send to my documents?

I was using the following code but it does not clear the macros prior to sending the worksheet to outlook.

Code:
  ThisWorkbook.Sheets(1).Copy
  
  With ActiveWorkbook
         .SendMail Recipients:="", _
          Subject:="Loss Control Request Form - " & Sheet1.Range("B3") & " - " & Format(Date, "mmm/dd/yy")
         .Close SaveChanges:=False
     End With




Code:
Sub SaveWithoutMacros()
'Purpose : To save a copy of the active workbook without macros
Dim vFilename As Variant
Dim wbActiveBook As Workbook
Dim oVBComp As Object
Dim oVBComps As Object

On Error GoTo CodeError

'Get a filename to save as
vFilename = Application.GetSaveAsFilename(filefilter:="Microsoft Excel Workbooks,*.xls", _
Title:="Save Copy Without Macros")
If vFilename = False Then Exit Sub 'User chose Cancel

ActiveWorkbook.SaveCopyAs vFilename
Set wbActiveBook = Workbooks.Open(vFilename)

'Now strip all VBA, modules, userforms from the copy
Set oVBComps = wbActiveBook.VBProject.VBComponents
For Each oVBComp In oVBComps
Select Case oVBComp.Type
Case 1, 2, 3 'Standard Module, Class Module, Userform
oVBComps.Remove oVBComp
Case Else
With oVBComp.CodeModule 'Worksheet or workbook code module
.DeleteLines 1, .CountOfLines
End With
End Select
Next oVBComp
wbActiveBook.Save
MsgBox "A copy of your workbook has been created with all VBA code removed.", vbInformation, "Success!"
wbActiveBook.Close
Exit Sub
CodeError:
MsgBox Err.Description, vbExclamation, "An Error Occurred"
End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
How about adding a new workbook and copying sheets to it instead of delete VBA?
 
Upvote 0
How about adding a new workbook and copying sheets to it instead of delete VBA?


Because I want the user to select the print/save style from a small userform box. I have other code behind it that need to be implented prior to printing or saving, etc.
 
Upvote 0

Forum statistics

Threads
1,224,566
Messages
6,179,555
Members
452,928
Latest member
101blockchains

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top