Tip Printed from ExcelTip.com
Mail Sheet Array using VBA in Microsoft Excel

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



Sub Mail_SheetsArray()
    Dim strDate As String
    Sheets(Array("Sheet1", "Sheet3")).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