» Mail the selection using VBA in Microsoft Excel
VBA macro tip contributed by Ron de Bruin, Microsoft MVP - Excel
CATEGORY - Mail - Send and Receive in VBA
VERSION - All Microsoft Excel Versions
- Create a new workbook with the Selection.
- Save the workbook before mailing it.
- Delete the file from your hard disk after it is sent.
All empty cells outside the selection are hidden (see screenshot)
This way the selection is always on the top of your sheet
Sub Mail_Selection()
Dim strDate As String
Dim Addr As String
Dim rng As Range
If ActiveWindow.SelectedSheets.Count > 1 Or Selection.Areas.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Addr = Selection.Address
ActiveSheet.Copy
ActiveSheet.Pictures.Delete
With Cells
.EntireColumn.Hidden = False
.EntireRow.Hidden = False
End With
Range(Addr).Select
Set rng = Selection
Application.GoTo rng, True
With rng.EntireColumn
.Hidden = True
rng(1).EntireRow.SpecialCells(xlVisible).EntireColumn.Clear
rng(1).EntireRow.SpecialCells(xlVisible).EntireColumn.Hidden = True
.Hidden = False
End With
With rng.EntireRow
.Hidden = True
rng(1).EntireColumn.SpecialCells(xlVisible).EntireRow.Clear
rng(1).EntireColumn.SpecialCells(xlVisible).EntireRow.Hidden = True
.Hidden = False
End With
Application.GoTo rng, True
rng.Cells(1).Select
strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strDate & ".xls"
ActiveWorkbook.SendMail "ron@debruin.nl", _
"This is the Subject line"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
Application.ScreenUpdating = True
End Sub
Book Store:
Recommended Books:
- East of Eden (Oprah's Book Club)
- Quantitative Methods in Derivatives Pricing: An Introduction to Computational Finance
- What the IRS Doesn't Want You to Know: A Cpa Reveals the Tricks of the Trade
- Analysis of Financial Statements
- The 22 Immutable Laws of Marketing : Exposed and Explained by the World's Two
- Fish! A Remarkable Way to Boost Morale and Improve Results
No comments have been submitted.

