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!

Advertisements