Send Excel Email with VBA

The following will email the activeWorkbook to a mail recipient via Microsoft Outlook. It works by creating a new workbook comprised of the Activesheet, saving it, then emailing the saved file alone to a recipient(s).  The following will work with Microsoft Outlook email.  Suffice to say you need Outlook to enable the email coding to run smoothly.

You will notice the Recipient and the Subject are hard coded into the VBA.  In the coming articles we will make this more flexible so the workbook can be sent to the people within a list of people.

Option Explicit
Sub Mailwb()
Dim wb As Workbook
Set wb=ActiveWorkbook

Application.DisplayAlerts=False
On Error Resume Next 'Error Trap for unforseen issues.
wb.SendMail "Marcus.LastName@YourData.com.au", "Subject line"
On Error GoTo 0
Application.DisplayAlerts=True
End Sub

Email Excel Sheet via Outlook with VBA

The following will mail the Activesheet to a recipient.  It works by creating a new workbook comprised of the Activesheet, saving it, then emailing the saved file alone. There needs to be a C drive for the procedure to work effectively.

Option Explicit


Sub EmailOneSheet()
Dim oApp As Object
Dim oMail As Object
Dim wb As Workbook
Const fname="C:\Temp2.xls"

'Turn off screen updating
Application.ScreenUpdating = False
'Make a copy of the active sheet and save it to a temporary file
ActiveSheet.Copy
Cells.Copy
Cells.PasteSpecial xlPasteValues

Set wb=ActiveWorkbook
wb.SaveAs fname

'Create and show the outlook mail item
Set oMail=Createobject("Outlook.Application").CreateItem(0)
With oMail 'Add a recipient
.To="Marcus.Small@YourEmail.com.au"
.Subject="One Page Activesheet v2"
.Attachments.Add wb.FullName
.send
End With

wb.Close False
Application.ScreenUpdating=True
Set oMail=Nothing
End Sub

If you are customising the above procedure the things to look out for are the file Name. If you want to save the file to a particular directory you will need to get the path correct. The following is the example used in the code, this is a very basic path and you might want something a bit more reflective. From this;

"C:\Temp2.xls"

To this for example;

"C:\MailDocs\ExcelFiles\SalesPL.xls"

Email file Excel

The above example will email the Sales data by sending it to a new workbook, saving it as Temp2 and emailing this document.  Below is the file.  The code will need to be run in conjunction with Microsoft Outlook.

Send Excel Sheet via Outlook with VBA to Multiple Recipients

One of the main advantages of Excel is you can run multiple reports from a single data source.  These reports can be saved to many different mediums. As such the reports can be emailed to multiple recipients.  We will put the email addresses into a dedicated place in the file so we do not have to change the email addresses in VBA.

Sub EmailOpFile() 'Excel VBA to send mail.
Dim OutApp As Object
Dim OutMail As Object
Dim str1 As String
Dim str2 As String

str1=[F3]
str2=[F4]

Set OutApp=Createobject("Outlook.Application")
OutApp.Session.Logon
Set OutMail=OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.TO=str1 & ";" & str2
.Subject="Ops Report"
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
On Error GoTo 0
MsgBox "Email Sent."
Set OutMail =Nothing
Set OutApp =Nothing
End Sub

The email addresses are in cells F3 & F4. The above will attach the full workbook.

If you want to add more people to the list without the hassle of adding new String then the following will add the information in Column F and put them in a String variable called str.

Sub EmailOpFile2() 'Excel VBA add further recipients to mail.
Dim OutApp As Object
Dim OutMail As Object
Dim i As Integer
Dim var As Variant
Dim str As String

var = Range("F3", Range("F" & Rows.Count).End(xlUp))

For i = 1 To Range("F" & Rows.Count).End(xlUp).Row - 2
str = str & var(i, 1) & ";"
Next i

str = Left(str, Len(str) - 1)
Set OutApp=Createobject("Outlook.Application")
OutApp.Session.Logon
Set OutMail=OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = str
.Subject = "Ops Report"
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
On Error GoTo 0
MsgBox "Email Sent."
Set OutMail =Nothing
Set OutApp =Nothing
End Sub