Have Questions? 
help@excelvbaisfun.com
Excel Ninja Pro
All Access

August 9, 2022

5 Ways To Send Emails In Excel

Introduction

Sending emails from Excel can be a challenge, especially if you don't know how to use VBA. For example, you may have a list of customers that you want to send an email to in order to send them some information about their account. Fortunately for those who need to send emails in Excel, there are several different ways that this can be done!

You Can Send Emails In Excel?

Yes, you can send emails using Excel. What’s more, Excel has some pretty nifty features that make it easy to send email from your spreadsheet. Here are three different methods for sending emails from Excel:

  • Using a hyperlink formula
  • Using mail merge
  • Using hidden buttons in the Quick Access Toolbar (QAT)
    • The "Email" button
    • The new "Send" button
    • The legacy "Send to Mail Recipient" (newly renamed "Mail Recipient") button
  • Using VBA to send Email is also an amazing option because the options are nearly limitless

Send Emails From Excel Using Hyperlink Formula

You can send email from Excel using the hyperlink formula. This is an important tool to know because it lets you easily send emails from your spreadsheet without having to copy and paste the same email over and over.

Email Magic in Excel

Here’s how it works:

  • First, type in the cell where you want your hyperlink formula to go. In this example we are going to use cell A1 for our example.
  • Type an email address into cell A2
  • Next, type this formula into A1 (you can copy and paste if you like): =hyperlink("mailto:"&A2,"Send Email")
  • Now there's a Send Email link in cell A1 you can click and it will open a new email to the address in cell A2.

Use The Mail Merge Feature In Word To Send Email From Excel

If you use Word, you can use the mail merge feature to send email from Excel. This is useful if you're sending out a lot of emails at once, because Word will handle all of the formatting for you. To do this:

  • Open up your document in Microsoft Word (or whatever word processor you prefer).
  • Click on "Mailings" at the top of the screen and choose "Start Mail Merge."
  • You'll see a dialog box pop up with options for different types of documents that can be merged together—you'll want to select "Normal Word Document" as your type.
  • Click on "Select Recipients" ->Using an Existing List to browse for your Excel file.
  • Click where you want a field to appear somewhere in Word and select "Insert Merge Field" and select your field, such as "First name" or whatever fields your workbook has.
  • Click the "Preview Results" toggle button to see how the record will look. Click the forward and backward buttons to see other records.
  • When finished, click "Finish & Merge" ->"Send Email Messages" and select the field that contains email addresses.

The Elusive Send Email Buttons in The Quick Access Toolbar

The Quick Access Toolbar is found in the upper left-hand corner of your screen, and it's where you'll find the buttons for New Workbook, Save As, Undo and Redo. It also contains a variety of other useful commands like Cut, Copy, Paste and so on. Here are the 3 Quick Access Tool buttons that will help you email quickly:

The "Email" Button:

  • The easiest QAT button for emailing is found by clicking the QAT dropdown and selecting "Email". This feature only attaches the current workbook to a new email in its entirety and opens the new email for you.

The New "Send" Button:

  • The new "Send" button also sends a copy of the current workbook as an attachment, but it has several new features built in, such as the ability to:
    • Send the workbook as an attachment
    • Email the workbook as a PDF attachment
    • Email the workbook as an XPS attachment
    • or even Use an Internet Fax service to send the workbook!
New "Send" button

To get the "Send" button, use the QAT dropdown button and select "More Commands". Select "All Commands" and find the option called "Send".

The Legacy "Send to Mail Recipients" Button:

  • The legacy "Send to Mail Recipients" is now called "Mail Recipient" and is also found in the "More Commands" -> "All Commands" section of the QAT setup menu. This tool allows you to:
    • Send only selected sheets
    • Send only selected cells
    • Send entire workbook as an attachment

Wow! Lots of options! Which is your favorite so far? The best is yet to come...

Use VBA To Send Email From Excel

This is the most advanced way to send emails from Excel, but it's also the simplest and most reliable.

It's perfect if you're familiar with VBA code or have a friend who can help you out. Here's how:

  • Open your VBA Editor by clicking on the Developer tab and then selecting Visual Basic. An easier way is to just press Alt+F11. Make a new code module with Insert - > Module.
  • Either type or paste the following code into your module:
Sub Send_Email()
Dim olNewMail As Outlook.MailItem
Set olNewMail= CreateObject("Outlook.Application").CreateItem(0) 'new mail item

With olNewMail
.To = "test123@someemail.com;"
.Subject = "This is our test email Subject"
.Body = "Hello Dan," & vbCrLf & vbCrLf & "This is my email!"
.Send 'sends now
End With
End Sub

Conclusion

Sending emails in Excel is super easy! You can do it with a hyperlink formula, the mail merge feature in Word or VBA. You have so many options when it comes to sending email from Excel.

What's your favorite method of automating Emails using Excel? Comment below and join the discussion!

2 comments on “5 Ways To Send Emails In Excel”

Excel VBA Is Fun

We believe Excel is one of the most versatile programs out there - and learning to program and automate it makes you very valuable!

You can prevent wasted time and errors, speed up tasks and become indispensable to your organization. 

See how our trainees have gotten raises, promotions and bonuses using their Excel Automation skills while building real problem-solving programs in Excel!
LEARN MORE
Copyright © 2024 ExcelVbaIsFun
X

Forgot Password?

Join Us

0
    0
    Your Cart
    Your cart is emptyReturn to Shop
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram