Mail the selection using VBA in Microsoft 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

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube