
Using Excel to email worksheets and workbooks has been one of the best functions I have found Excel useful for. It’s enabled me to create all sorts of automated processes such as creating and sending dashboards and KPIs, as well as emailing parsed out data meant for a certain supplier.
This block of VBA code is easy to use, easy to understand – and extremely effective.
Sub EmailSheet()
'This sub procedure will email a section of a worksheet
'Written by JamesCanCode
'www.jamescancode.com
Declare Variables
Dim OutApp As Object
Dim OutMail As Object
Set Variables
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'The selection below here is what you plan to send
'So change this accordingly
Sheets("Purchase_Summary").Select
Range("A1:F50").Copy
ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
.item.to = "Email@Email.com" 'Change this
.item.Subject = "Purchase Summary Report" 'Change this
.item.Send
End With
DoEvents
Application.Wait (Now + TimeValue("0:00:05"))
Set OutMail = Nothing
Set OutApp = Nothing
Range("A1").Select
End Sub
It’s that simple!