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.
Sub Mailwb()
Set wb=ActiveWorkbook
Application.DisplayAlerts=False
On Error Resume Next 'Error Trap for unforseen issues.
Application.DisplayAlerts=True
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.
Sub EmailOneSheet()
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
.Subject="One Page Activesheet v2"
.Attachments.Add wb.FullName
.send
wb.Close False
Application.ScreenUpdating=True
Set oMail=Nothing
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"
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.
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
.Subject="Ops Report"
.Attachments.Add ActiveWorkbook.FullName
.Send
On Error GoTo 0
MsgBox "Email Sent."
Set OutMail =Nothing
Set OutApp =Nothing
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.
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 = Left(str, Len(str) - 1)
Set OutApp=Createobject("Outlook.Application")
OutApp.Session.Logon
Set OutMail=OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.Subject = "Ops Report"
.Attachments.Add ActiveWorkbook.FullName
.Send
On Error GoTo 0
MsgBox "Email Sent."
Set OutMail =Nothing
Set OutApp =Nothing