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:

Sub CreateMail()

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)

With ActiveSheet

Set rngTo = .Range("B1")

Set rngSub = .Range("B2")

Set rngMessage = .Range("B3")

Set rngAttachment = .Range("B4")

End With

With objMail

.To = rngTo.Value

.Subject = rngSub.Value

.Body = rngMessage.Value

.Attachments.Add rngAttachment.Value

.Display 'Instead of .Display, you can use .Send to send the email _

or .Save to save a copy in the drafts folder

End With

Set objOutlook = Nothing

Set objMail = Nothing

Set rngTo = Nothing

Set rngSub = Nothing

Set rngMessage = Nothing

Set rngAttachment = Nothing

End Sub

  • 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.