As is usually the case, I need to regularly mail a summary to my Manager, he he.

Me being lazy as it is found one excellent way to mail almost any object that I wanted.

Mail excel from Outlook –  Ron de Bruin Excel Automation

This person is simply amazing, and I use the code more often than not.

We will start with Add Summary – VBA Excel file and assume that summary is already created, Now I need to send this as a mail to my manager.


Let’s borrow Ron’s code for sending a range or selection example 2.


Sub Mail_Selection()

‘ You can make changes here and the code will run and display the mail in your outlook

myMangerID = “”
CCMailID = “”
BCCMailID = “”
SubjectText = “This is the Subject line”
BodyText = ” This is body”
‘ this code selects the summary , recrded by macro recorder

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
‘ Code below is from Ron

‘Working in Excel 2000-2016
‘For Tips see:
Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim OutApp As Object
Dim OutMail As Object

Set Source = Nothing
On Error Resume Next
Set Source = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Source Is Nothing Then
MsgBox “The source is not a range or the sheet is protected, please correct and try again.”, vbOKOnly
Exit Sub
End If

If ActiveWindow.SelectedSheets.Count > 1 Or _
Selection.Cells.Count = 1 Or _
Selection.Areas.Count > 1 Then
MsgBox “An Error occurred :” & vbNewLine & vbNewLine & _
“You have more than one sheet selected.” & vbNewLine & _
“You only selected one cell.” & vbNewLine & _
“You selected more than one area.” & vbNewLine & vbNewLine & _
“Please correct and try again.”, vbOKOnly
Exit Sub
End If

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

Set wb = ActiveWorkbook
Set Dest = Workbooks.Add(xlWBATWorksheet)

With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With

TempFilePath = Environ$(“temp”) & “\”
TempFileName = “Selection of ” & wb.Name & ” ” & Format(Now, “dd-mmm-yy h-mm-ss”)

If Val(Application.Version) < 12 Then
‘You use Excel 97-2003
FileExtStr = “.xls”: FileFormatNum = -4143
‘You use Excel 2007-2016
FileExtStr = “.xlsx”: FileFormatNum = 51
End If

Set OutApp = CreateObject(“Outlook.Application”)
Set OutMail = OutApp.CreateItem(0)

With Dest
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.to = myMangerID
.CC = CCMailID
.Subject = SubjectText
.Body = BodyText
.Attachments.Add Dest.FullName
‘You can add other files also like this
‘.Attachments.Add (“C:\test.txt”)
‘.Send ‘or use
End With
On Error GoTo 0
.Close savechanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

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



As you see the mail is drafted in my outlook , I can verify and send or if you are feeling great just uncomment the Send in above code in Red and comment Display. The mail will be automatically be sent.

By modifying text in green you can customize the report name too.

Attached is the file with source code.

Make Summary Mail- VBA

As you will see from Ron’s site that you can send charts, full WBs anything you want. This eases a lot of effort and helps get through the mundande tasks.

I am curious if there is any task somebody is stuck at that I can point to on how to automate. Please let me know in the comments.