How To Send Email From Excel

The Microsoft Outlook Object Model: A Quick Introduction

Send Emails from Excel – Automatically and Manually (Macro & Non-Macro Solution)

My focus in Power Spreadsheets is Microsoft Excel. Therefore, I’ve previously created a comprehensive tutorial about Excel’s object model. You can find this here. I’ve also written other blog posts that explain different object models, such as that of the Ribbon Commander.

However, here’s the deal:

Each application has its own object model. When working with Visual Basic for Applications, you’re manipulating the objects from the relevant application.

Therefore, you need to have a good understanding of the object model of the application you’re working with. For this reason, I provide a quick introduction to the Outlook object model in this section.

In any case, several of the more general explanations I provide in the blog post about Excel’s object model to which I link to above apply to the Outlook object model.

Let’s start by reviewing:

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

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

Why Use An Excel Spreadsheet To Send Mass Emails

Two of the biggest reasons to use an excel spreadsheet for mass emailing are that it is free and offers much more flexibility than other email marketing software.

Whether you want to send a quick message or a newsletter, having the ability to add attachments, change your ‘from’ and ‘subject line’, and send different types of emails makes excel a great option.

Don’t Miss: How Do I Restore My Gmail Account

Getting Data From Excel To The Flow

After we trigger our flow with the button, were going to want it to do something. These will be the actions we add in the next steps of our flow process.

The first action we want to do is get the data from our Excel table.

Click on the New step button to add the next step to the flow. Then select Add an action from the menu.

Search for Excel to narrow down the list of connections then select the Excel Get rows action. This action will get all the rows of data from the specified table in a given workbook.

Now we can select the File name from our OneDrive using the file icon and then select the appropriate Table name from the drop down menu, our table was named Emails.

Inserting Line Break In Email Body Content

4 Quick Ways to Send Emails from Your Excel (Part II)

As we saw in the previous section, the line break that we entered in the excel cell did not turn up here in the outlook email body area. This is because Alt+Enter is not a function in the outlook email application.

However, using the combination of special characters, you can achieve this.

The %0A is a line break in Outlook application.

Enter the %0A at relevant place in the body text reference cell where you want to insert a line break in outlook. Below image is self-explanatory.

Read Also: What Does Cc Mean When Your Sending An Email

Saving And Naming The Flow

Now that weve finished building our flow process, we need to save it. Click on the Save icon in the upper right hand corner of the web app.

Well also want to give the new flow a descriptive name. Click on the default name found in the top left hand corner of the web app then rename it appropriately.

Draft Your Email Template In A Microsoft Word Document

Now that we have our mailing list together, its time to put together our source document in Word. The best way to draft your message is to get started without worrying about your personalization fields. We will add them later. Focus on your email content first and foremost the quality of your email will have the most significant impact on its success. Make sure to save when youre finished.

You May Like: How To Print Emails

How Do Mail Merges Help You

The mail merging process automatically creates separate, personalized documents for each recipient. This way, you save tons of time as youre not wasting hours manually creating a new customized document for each person.

Imagine manually composing 100 different emails for 100 different recipients! You can use mail merges to enter recipient data quickly for sending different documents such as:

  • Mass emails

Enter The Following Formula To Calculate Column J Single Send Link:

How to Send Emails from an Excel Spreadsheet Using VBA Scripts

=HYPERLINK

Explanation: Creates a hyperlink you can click if you want to automatically compose an email to a single recipient without using VBA. By clicking the Hyperlink, the formula creates an email is automatically composed.

This creates a hyperlink that you can click if you want to automatically compose an email to a single recipient without using VBA. By clicking the Hyperlink, the formula creates an email that is automatically composed.

You now have a fully populated table:

Also Check: Remove Duplicate Emails Outlook 2013

How To Send Email From Excel List

When you need to send a mass email to a large group of people, youll need an automated process that can handle repetitive tasks quickly. Creating an Excel file with a list of emails is the most common way to send mass emails. So, in this tutorial, we will show you how to send email from an Excel list automatically to a large number of people.

Send Email.xlsm

Vba Code To Send Email From Excel Using Early Binding

The following sample macro sends an email message with the active workbook as an attachment. This VBA Sub procedureuses early binding. You can find the equivalent version below.

If you use early binding , you must create a reference to the Microsoft Outlook Object Library by following the indications I provide above.

In order to understand what this macro does exactly, let’s take a look at each of the lines of code:

Line #1: Dim outlookApp As Outlook.Application

The purpose of this line of code is to create a variable that represents the Outlook application.

Line #2: Dim outlookMail As Outlook.MailItem

The main purpose of this line of code is to create a variable that represents the email message that the sample macro creates and sends.

Line #3: Set outlookApp = New Outlook.Application

Uses the Set statement to assign a new instance of the Outlook application to the outlookApp object variable.

In practical terms, this line of code #3 does 2 things:

  • #1: Creates a new instance of Outlook. This is achieved by New Outlook.Application.
  • #2: Assigns this new instance to the outlookApp object variable. For these purposes, the equal sign acts as an assignment operator. In other words, it assigns the expression to its right side to the variable to its left side .

Line #4: Set outlookMail = outlookApp.CreateItem

Uses the Set statement to assign a new email item to the outlookMail variable. The new email item is created by using the CreateItem method.

Line #16: .Send

Read Also: How Can I Find My Icloud Email Address

How Do I Add Recipients To The Email Template

To add recipients to the email template, follow these steps:

1. In Excel, select the mail merge tool.

2. Click on the “Mail Merge Recipients” button and then choose “Add From List.”

3. From there, select the list of recipients that you have previously created in one or more worksheets in your workbook.

Creating The Supporting Formulas

How to Send Email From Excel Automatically

To achieve this behavior, we need to create some helper columns that will contribute to the construction of the final hyperlink.

The first helper column called Exceeded Goal will use an IF function to determine if the sales representative met their sales goal.

=IF

If the representatives sales are greater than or equal to their goal, we calculate the difference . If not, we display nothing in the cell .

NOTE: For more information on the IF function, check out this post.

The second helper column called Subject will also use an IF function to determine which subject to place in the Subject field of the email.

=IF)))

This formula uses some of Excels text manipulation functions, such as LEFT, TRIM, and SEARCH, to extract the representatives first name from column A and concatenate it with pre-defined text.

NOTE: An alternate version of the formula using only the LEFT and SEARCH functions is as follows:

=IF - 1))

The third helper column called Body will use an IF function to create the custom message that will be placed in the Body field of the email.

=IF

No thats not a typo. The .%0A will be discussed later in this post.

The final helper column called Send Email will use the IF and HYPERLINK functions to generate the clickable link.

=IF, HYPERLINK)

You May Like: How To Start A Email To A Professor

How To Send Email From Excel

Details

You might have Gmail or Yahoo accounts for sending and receiving emails. But, Excel offers the option to send emails directly from Excel itself. Suppose, you are busy with some Excel application and want to send an email to your manager or team member attaching the same Excel document you are working on. You can complete it in seconds without opening your Yahoo or Gmail inbox.

Email A Document From Microsoft Office

If you have Outlook installed as part of Microsoft Office, and set as your default email app, you can send your document as an email attachment. If you are using Excel, Publisher, or Word, you can also send your file as the body of an email message.

If Outlook is not your default email application you’ll need to configure it as the default in order to use this feature. For more information see: Make Outlook the default program for email, contacts, and calendar.

  • Click File> , and then choose one of the following options:

    Note: The options you see will vary depending on the Office program youre using.

  • Upload to OneDrive Uploads your file to your OneDrive account where you can then share through a direct link or an email message.

  • Attach a copy instead Choose to attach a the file to an email, or attach a PDF of the file.

  • Enter the recipients aliases, edit the subject line and message body as necessary, and then click Send.

  • You May Like: Place To Print Email

    Add Attachments Cc And Bcc In Excel

    Similarly, you can test with other important properties such, CC, BCC, Attachments etc.

    ð Here’s another exmaple, I am sure you will like. How to send emails to Multiple recipients from your Excel workbook using VBA and Outlook.

    Now you know how to send emails from an Excel file using VBA and Outlook. Here we also learned how to add Attachments, Blind Carbon Copy or BCC etc., using VBA. It is very simple. Try working with other properties too.

    Thanks for reading.

    The Wonder Of The Hyperlink Function

    Send Emails from Excel with Power Automate

    The HYPERLINK function in Excel allows us to use cell references to create an email. The referenced cells can contain other functions, like IF and XLOOKUP, which allows us to create incredibly dynamic messages.

    In our sample below, we have a list of sales representatives, their email addresses, current sales, and their sales goals.

    We will create a dynamic HYPERLINK that will send either a motivational email if they missed their goal, or a congratulatory email if they achieved their goal.

    • Sales representatives email address in the TO: field
    • Custom subject line
    • Custom messaging in the body text area

    Recommended Reading: What Does Cc In Emails Mean

    Why Send Email From Excel

    There are a lot of reasons why you might want to send an email from inside Microsoft Excel.

    Maybe you have staff that updates documents or spreadsheets on a weekly basis, and you’d like to receive an email notification of when those updates are done. Or you might have a spreadsheet of contacts and you want to send one email to all of them at once.

    You’re probably thinking that scripting an email broadcast from Excel is going to be complicated. That’s not the case at all.

    The technique in this article will make use of a feature that’s been available in Excel VBA for a long time, Collaboration Data Objects .

    CDO is a messaging component used in Windows since very early generations of the OS. It used to be called CDONTS, and then with the advent of Windows 2000 and XP, was replaced with “CDO for Windows 2000”. This component is already included in your VBA installation within Microsoft Word or Excel and is ready for use.

    Using the component makes sending emails from within Windows products with VBA extremely easy. In this example, you’ll use the CDO component in Excel to send out an email that will deliver the results from a specific Excel cell.

    How To Send Personalized Mass Emails To A List From Excel Via Outlook

    For example, I have the following data range in a worksheet which contains Name, Email Address, Registration Code columns, and now, I want to send a message with a personalized greeting and their own Registration Code to the separated Email Addresses in column A. To solve this problem, the following methods may help you.

    Send personalized mass emails to a list from Excel with Mail Merge function

    With the Words Mail Merge function, you can finish this job quickly and easily, please do with the following step by step:

    1. Launch a new blank Word document, and then click Mailings> Select Recipients> Use an Existing List, see screenshot:

    2. In the Select Data Source window, choose the workbook which includes the data range that you want to use, and click Open button, see screenshot:

    3. In the coming Select Table dialog box, please choose the worksheet with the data range you need, and then click OK button, see screenshot:

    4. And the E-mail message main document and your address list have been connected together, now, you can edit the text message and add placeholders that indicate where the unique information will appear in each message.

    To insert their individual greeting name, click Mailings> Insert Merge Field> Name, the personalized name has been inserted into the message, and the field name is surrounded by « ».

    Go on typing your message and insert the Registration Code into the place where you need, see screenshot:

    You can type the subject into the Subject line text box

    Don’t Miss: Find All Email Accounts

    Popular Articles

    Related Stories

    Stay on top - Get the daily news in your inbox