VBA to Send an Email with Outlook and Attach Your Current Spreadsheet

If you want some simple VBA code that sends an email using outlook and has your current excel spreadsheet attached, look no further….

Sub sendEmail()

Dim Email As Variant

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = "email@address1.com;email@address2.com" 
.cc = "email@address3.com;email@address4.com"
.Subject = "Subject Line of Your email goes here"
.Body = "Hello," & vbLf & vbLf & "Please find attached the latest amazing report." & vbLf & vbLf & "Kind Regards," & vbLf & vbLf & "Excel Teacher"
.Attachments.Add ActiveWorkbook.FullName
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

You can adjust the contents between the inverted commas after the .To, .cc, .Subject and .Body parameters.

Note that email addresses are separated using a semi-colon.

You may notice that we have split our email body text used the vbLf (Line Feed) code. You could also use vbCr (Carriage Return) or vbCrLf (Carriage Return Line Feed)This makes the subsequent text drop down a line. 2 of these together creates a blank row to make the email look nicer.


5/5 (1)

Please rate this

2 comments on “VBA to Send an Email with Outlook and Attach Your Current SpreadsheetAdd yours →

Leave a Reply

Your email address will not be published. Required fields are marked *