» Mail one Sheet using VBA in Microsoft Excel

VBA macro tip contributed by Ron de Bruin, Microsoft MVP - Excel You can also use the following line if you know the sheet you want to mail :

Sheets("Sheet5").Copy 

It doesn't have to be the active sheet used at that time.

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

Sub Mail_ActiveSheet()
    Dim strDate As String
    ActiveSheet.Copy
    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
End Sub