Automatically sending an excel file to someone via an email, can be accomplished easily and with ease. Following the brief guide below you will be able to do this in no time at all. From the basics to a more complex approach, here we have provided all the information you need to start sending files directly to email. While most people won’t ever use this, it is better to know it and not need it than to need it and not know it.
There are different approaches for this same purpose, we take pride in presenting the easiest and fastest one. For the guide below we will be using the Outlook platform and we’ll take the VBA approach. Without further ado we present.
How to send excel file to email automatically.
Let’s begin with the basics, first you want to make a separate sheet for the following process.
In the new sheet you want to use different columns for different fields of information. In this example column A will be destined for the name of the recipient, column B for their email addresses and column C will be used for the file names. In this case instead of using file names you want to use the route of the file.
Now this is the main part, you want to open the VBA editor by using the shortcut Alt + F11. Then you can use Ctrl + r to open the project explorer. After that, you need to insert a module in your project before you can use it for the code. Click on Insert and then on Module.
Now you should click in the project explorer and then press F7, or simply double click on the newly created module. A new window should open up, this is where the interesting happens. Now you want copy and paste the code below.
'Working for Excel 2000-2016
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range
Dim FileCell As Range
Dim rng As Range
.EnableEvents = False
.ScreenUpdating = False
Set sh = Sheets("Sheet1")
Set OutApp = CreateObject("Outlook.Application")
For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
'Enter the path/file names in the C:Z column in each row
Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)
.to = cell.Value
.Subject = "Testfile"
.Body = "Hi " & cell.Offset(0, -1).Value
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Send 'Or use .Display
Set OutMail = Nothing
Set OutApp = Nothing
.EnableEvents = True
.ScreenUpdating = True
After That’s done you want to use Alt + q to go back to your Excel sheet. When that's done, you can use Alt + F8 to run the newly created macro.
Do be sure to inspect the code we have provided for you and change it in any way you deem necessary. Remember that this should be done to suit your needs. Also, leep present that in order for this macro to run you need to have your Outlook client active or minimized, but open.
With this brief process you’re able to automatize the process of sending files to emails. All that it’s needed for this is to run the macro we have provided for you. You can make your work a lot easier and faster with this option, this is likely to increase your workflow just by knowing how to best take advantage of your Microsoft Excel software and Outlook client. If you like this kind of content and want to see more, please be sure to visit us at our blog.