Excel Tip: Mail one Sheet using VBA in Microsoft Excel / Microsoft Excel Tips / ExcelTip.com

Tip Printed from ExcelTip.com

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