» Mail the selection using VBA in Microsoft Excel

VBA macro tip contributed by Ron de Bruin, Microsoft MVP - Excel

• Create a new workbook with the Selection.
• Save the workbook before mailing it.
• Delete the file from your hard disk after it is sent.

It will unhide hidden rows and columns in the selection
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