|  

» 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

 


Rate This Tip
12 34 5
Rating: 3.46     Views: 33512
No comments have been submitted.
Click here to post comment
For Registered Users
Name
Comment Title
Comments