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!