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.
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