We can send emails directly from our Excel spreadsheet to multiple recipients in only a matter of minutes. In this article, we will learn how to send emails from Excel using the in-built Email application (Outlook) and VBA macro codes.
Figure 1 – How to send emails from excel
Enable How to Email an Excel Spreadsheet
- We will save our Excel file using the Ctrl + S shortcut. (But we don’t have to close the workbook)
- We will go to the Quick Access Toolbar at the top of the Excel worksheet. Next, we will click on the arrow next to the Save file box.
Figure 2 – Emailing Excel Spreadsheets
- In the drop-down menu, we will mark the Email option
Figure 3 – How to email an excel file
- The Email icon will appear in the Quick Access Toolbar.
Figure 4 – How to send excel file to an email
How to Email An Excel Spreadsheet
- We will click on the Email icon. This will open a mini Microsoft Outlook window, which is the default Microsoft Email program.
- The Subject of our Email will be automatically filled as the name of the Excel file we want to send.
Figure 5 – Send auto email from excel
- We can change the address for sending the Email in the From Text Box menu
- Next, we will enter the email address of the recipient in the To Text Box. We can add as many recipients as we want.
Figure 6 – How to email from excel
- We can attach more files by using the Attach File button in the Message Tab
Figure 7 – Send sheet by email
- If we wish to add a short note, memo or letter, we can do so in the blank area specified in the Outlook area.
Figure 8 – How to send an automated email from excel
- When we are done, we can click Send to send the file.
How to automatically send Emails from Excel using VBA
- We will enter data into a blank Excel spreadsheet as shown in the figure below
Figure 9 – Send auto email from excel
- In Cell B1, we will enter an address, followed by a comma, if we want to send to more than one recipient
- The Subject of our Email will be entered in Cell B2
- The Message to be sent alongside file will be entered in Cell B3
- The full part to the attachment will be entered in Cell B4 (We can get the full pathway for our attachment, by going through folders and once we click on the document, we will go to the Home Tab and select copy path
Figure 10 – Sending emails from excel with attachments
- We will go back to the Excel sheet and use Ctrl + V to paste the file pathway.
Figure 11 – How to email an excel spreadsheet
- We will press ALT + F11 to open the VBA Editor.
- In the Blank Module, we will enter the code below:
Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSub As Range
Dim rngMessage As Range
Dim rngAttachment As Range
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
Set rngTo = .Range("B1")
Set rngSub = .Range("B2")
Set rngMessage = .Range("B3")
Set rngAttachment = .Range("B4")
.To = rngTo.Value
.Subject = rngSub.Value
.Body = rngMessage.Value
.Display 'Instead of .Display, you can use .Send to send the email _
or .Save to save a copy in the drafts folder
Set objOutlook = Nothing
Set objMail = Nothing
Set rngTo = Nothing
Set rngSub = Nothing
Set rngMessage = Nothing
Set rngAttachment = Nothing
- Lastly, we will click on Save to Create Mail Macro.
Figure 12 – How to send auto email from excel
- Whenever we run the macro code, our emails will be automatically sent using the details we specified in Figure 6.