VBA - Sending Active Sheet by email

YannDV 

try replacing your whole code with this

Sub Button2()
'Variable declaration
'Outlook application
Dim oApp As Object
'Outlook MailItem
Dim oMail As Object
Dim WB As Workbook
Dim WS As Worksheet
Dim FileName As String
Dim SendSheetOnly As Boolean
'Turn off screen updating
Application.ScreenUpdating = False
'Ask the user what to send
Select Case MsgBox( _
Prompt:="Do you want to send the active sheet only ?", _
Buttons:=vbYesNoCancel)
Case vbYes
'Yes, make a copy of the active sheet.
SendSheetOnly = True
ActiveSheet.Copy
Set WB = ActiveWorkbook
'Save without formulas ?
Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
'Clear the clipboard
Application.CutCopyMode = False
FileName = ".xls"

'Continue when error occurs
On Error Resume Next
Kill "e:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:=Range("G7").value & FileName
Case vbNo
If Len(ActiveWorkbook.Path) = 0 Then
'File hasn't been saved before
MsgBox "Can't send an unsaved workbook", vbCritical
GoTo exiting
End If
SendSheetOnly = False
Set WB = ActiveWorkbook
Case vbCancel
'olDiscard = 1
oMail.Close SaveMode:=1
GoTo exiting
End Select

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0) 'olMailItem = 0
With oMail
'User input To property
.To = Range("D5").value
'User input CC property
.CC = Range("H1").value
'Hard code Read Receipt Requested property
'.ReadReceiptRequested = True
'User input Read Receipt Requested property
'.ReadReceiptRequested = Range("B3").Value
'User input Subject property
.Subject = Range("A2").value
'User input Body property
.Body = Range("e5").value
'Hard code Sensitivity property
.Sensitivity = 3 'olConfidential = 3
'Set attachment
.Attachments.Add WB.FullName
'Hard code importance
''olImportanceHigh = 2
.Importance = 2
'Send directly (remove apostrophe on line below to activate)
'.Send
'Display it
.Display
End With
If SendSheetOnly Then
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False
End If
exiting:
'Restore screen updating
Application.ScreenUpdating = True
'destroy variables and restore memory
Set oMail = Nothing
Set oApp = Nothing

End Sub