When users send emails to their customers or bosses it’s quite hard to keep track. In that case, Excel comes in handy. Therefore, “send automatic email from Excel to Outlook” has been proven to be a time-saving approach. Excel VBA Macros and the HYPERLINK function can send automatic emails or create a draft using Excel entries.
Let’s say we have Employee Restructured Salary data in Excel and we want to send automatic email using Outlook.
In this article, we demonstrate multiple variants of VBA Macros and the HYPERLINK function to send automatic email from Excel to Outlook.
Download Excel Workbook
⧭ Opening Microsoft Visual Basic and Inserting Code in Module
Before proceeding to demonstrate any methods, it’s necessary to know the ways to open and insert a Module in the Microsoft Visual Basic in Excel.
🔄 Opening Microsoft Visual Basic: There are mainly 3 ways to open Microsoft Visual Basic window.
1. Using Keyboard Shortcuts: Press ALT+F11 altogether to open Microsoft Visual Basic window.
2. Using Developer Tab: In an Excel worksheet, Go to Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.
3. Using Worksheet Tab: Go to any worksheet, Right-Click on it > Choose View Code (from the Context Menu).
🔄 Inserting a Module in Microsoft Visual Basic: There are 2 ways to insert a Module in Microsoft Visual Basic window,
1. Using Sheet’s Options: After opening the Microsoft Visual Basic window, select a Worksheet > Right-Click on it > Select Insert (from the Context Menu) > then choose Module.
2. Using Toolbar: You can also do it by selecting Insert (from the Toolbar) > then choosing Module.
4 Easy Ways to Send Automatic Email from Excel to Outlook
Method 1: Using VBA Macro to Automatically Send Email Using Outlook to Selected Recipients
We want to create a Macro execution Button by which we can simply send mail to selected recipients with just a click.
Step 1: Go to the Insert tab > Shapes > Select any of the offered shapes (i.e., Rectangular: Rounded Corners).
Step 2: Drag the Plus Icon wherever you want to insert the Shape as shown in the image below.
Step 3: Choose a preferred Shape Fill and Outline Color then right-click on it. Click on Edit Text to insert text.
Step 4: Use the instruction to open Microsoft Visual Basic and insert Module. Paste the following Macro in the Module.
Sub ExcelToOutlookSR() Dim mApp As Object Dim mMail As Object Dim SendToMail As String Dim MailSubject As String Dim mMailBody As String For Each r In Selection SendToMail = Range("C" & r.Row) MailSubject = Range("F" & r.Row) mMailBody = Range("G" & r.Row) Set mApp = CreateObject("Outlook.Application") Set mMail = mApp.CreateItem(0) With mMail .To = SendToMail .Subject = MailSubject .Body = mMailBody .Display ' You can use .Send End With Next r End Sub
➤ in the code,
1 – start the macro procedure by declaring the variables as Object and String.
2 – run a VBA FOR loop for each row in the selection to assign Email’s Send To, Subject, and Body using row entries.
3 – assign variables.
4 – perform the VBA With statement to populate Outlook items such as Send To, Mail Subject, etc. Here the macro only executes the Display command to bring out Outlook with an email draft. However, if Send command is used in place or after Display, Outlook will send the created email to the selected recipients.
5 – finish the VBA FOR loop.
Step 5: Return to the Worksheet. Right-click on the Shape then select Assign Macro from the Context Menu options.
Step 6: Select the Macro (i.e., ExcelToOutlookSR) under the Macro name and choose the Macro in option as This Workbook. Click on OK.
Step 7: Now, in the worksheet, select one or multiple employees then click on the Shape Button.
Step 8: Excel prompts Outlook to lunch and creates or sends emails to the selected employees. As you select two of the employees, Outlook generates two different email drafts ready to be sent.
As the macro only provides the Display command, Outlook just displays the email draft without sending it. Use the Send command to automatically send emails from Excel to Outlook using cell entries.
Read More: Excel Macro to Send Email Automatically (3 Suitable Examples)
Method 2: Sending Email Automatically from Excel to Outlook Depending on Specific Cell Value
What if we want to send automatic email after achieving targets from Excel to Outlook? A Macro code can do this job with ease.
Suppose, we have Quarterly Sales Data as depicted below, after achieving a target (i.e., Sales> 2000) will automatically prompt Outlook to send an email from Excel to an assigned email id.
Step 1: Type the following macro code in any Module.
Option Explicit Dim Rng As Range Sub Worksheet_Change(ByVal mRng As Range) On Error Resume Next If mRng.Cells.Count > 1 Then Exit Sub Set Rng = Intersect(Range("F17"), mRng) If Rng Is Nothing Then Exit Sub If IsNumeric(mRng.Value) And Target.Value > 2000 Then Call ExcelToOutlook End If End Sub Sub ExcelToOutlook() Dim mApp As Object Dim mMail As Object Dim mMailBody As String Set mApp = CreateObject("Outlook.Application") Set mMail = mApp.CreateItem(0) mMailBody = "Greetings Sir" & vbNewLine & vbNewLine & _ "Our outlet has quarterly Sales more than the target." & vbNewLine & _ "It's a confirmation mail." & vbNewLine & vbNewLine & _ "Regards" & vbNewLine & _ "Outlet Team" On Error Resume Next With mMail .To = "[email protected]" .CC = "" .BCC = "" .Subject = "Notification on Achieving Sales Target" .Body = mMailBody .Display 'or you can use .Send End With On Error GoTo 0 Set mMail = Nothing Set mApp = Nothing End Sub
➤ From the above image, in the sections,
1 – assign a cell (i.e., F17) within a range to execute the VBA IF statement. If the statement results in True, the macro calls another macro for execution.
2 – declare variable types and assign them to populate Outlook’s entries.
3 – perform VBA With statement to assign variables to email entries. Use the Send command instead of Display in case you directly want to send emails without reviewing them. The recipient email is inserted within the macro. Use alternative methods in case you want an automatic insertion of the recipient’s email id.
4 – clear certain variables from assignation.
Step 2: Use the F5 key to run the macro. In a moment, Excel fetches Outlook with a draft email created automatically as shown in the followings. You can click on Send or auto-send using Send command in the macro.
Read More: Automatically Send Emails from Excel Based on Cell Content (2 Methods)
- How to See Who Is in a Shared Excel File (With Quick Steps)
- Enable Share Workbook in Excel
- How to Share Excel File for Multiple Users
- How to Apply Macro to Send Email from Excel with Attachment
Method 3: Using VBA Macro to Send Email with Active Worksheet from Excel by Outlook
Alternatively, there may be instances where we need to send an entire Active Sheet to an assigned email address. In that case, we can use a VBA Custom Function to be called within a macro.
Step 1: Insert the below macro in the Module.
Function ExcelOutlook(mTo, mSub As String, Optional mCC As String, Optional mBd As String) As Boolean On Error Resume Next Dim mApp As Object Dim rItem As Object Set mApp = CreateObject("Outlook.Application") Set rItem = mApp.CreateItem(0) With rItem .To = mTo .CC = "" .Subject = mSub .Body = mBd .Attachments.Add ActiveWorkbook.FullName .Display 'or you can use .Send End With Set rItem = Nothing Set mApp = Nothing End Function Sub OutlookMail() Dim mTo As String Dim mSub As String Dim mBd As String mTo = "[email protected]" mSub = "Quarterly Sales Data" mBd = "Greetings Sir" & vbNewLine & vbNewLine & _ "Kindly find Outlet's Quarterly Sales data attached with this mail." & vbNewLine & _ "It's a notification mail." & vbNewLine & vbNewLine & _ "Regards" & vbNewLine & _ "Outlet Team" If ExcelOutlook(mTo, mSub, , mBd) = True Then MsgBox "Successfully created the Mail draft or Sent" End If End Sub
➤ From the above image, the code’s sections,
1 – declare and set the variables.
2 – assign the commands using the VBA With statement. Use the Display or Send command for reviewing or direct sending emails respectively.
3 – clear the previously set variables.
4 – assign the VBA With commands with texts.
5 – execute the VBA Custom Function.
Step 2: To execute the macro press F5, and instantly Excel brings out the Outlook with a draft email to review similar to the below image. Afterward, you are good to send it.
Read More: How to Send Bulk Email from Outlook Using Excel (3 Ways)
Method 4: Sending Automatic Email from Excel to Outlook Using HYPERLINK Function
The HYPERLINK function generates a clickable link in Excel cells to bring Outlook as a medium to send automatic emails from Excel.
Step 1: Type the following formula in cell H5.
The HYPERLINK function takes “MailTo:”&C5&”?Subject=”&F5&”&cc=”&$D$2&”&body=”&G5 as link_location, and “Click Here” as friendly_name.
Step 2: Hit ENTER to paste the link. Then click on the link.
Step 3: Excel takes you to Outlook. And you see all the Outlook entries are filled with assigned data from Excel. Click on Send.
Step 4: Drag the Fill Handle to apply the formula to other cells.
Read More: How to Send Email Automatically When Condition Met in Excel
VBA Macro Variants and HYPERLINK function can be helpful while sending automatic emails from Excel to Outlook. Hope you find your preferred method within the above-described ones. Comment, if you have further inquiries or have anything to add.
- Send Reminder Email Automatically from an Excel Worksheet Using VBA
- How to Send an Editable Excel Spreadsheet by Email (3 Quick Methods)
- Macro to Send Email from Excel with Body (3 Useful Cases)
- How to Automatically Send Email from Excel Based on Date
- How to Send Email from Excel with Body Using a Macro (with Easy Steps)
Is it a way to include attached files using the hyperlink function?
Greetings Julian Chen,
Sadly, the HYPERLINK function doesn’t support any attachment links in its arguments. You have to use Other Means to attach files. You can use Method 3 of this article to include an attachment.
Md. Maruf Islam (Exceldemy Team)
I hope your doing well
Firstly, thanks for the explicit explanation.
Secondly, I would like to ask how can we include more than one row in the body.
As I want to send a table within the body mail A1:L2 for example.
Thanks in advance again.
Thank you for your comment. If you want to send a table within the email body for any range, then you have to create a table in any worksheet. For this, you will follow our Method No.3 where we have used a VBA code to send an active sheet within the email body. Firstly, you can create a table in this active sheet and send this sheet with your table within the email body. Can you please share your Excel file with us? We will customize the file according to your requirements. Email address [email protected].
Bishawajit Chakraborty (Exceldemy Team)
I am trying to create a hyperlink to outlook which can be clicked to send an email with various cell entries. So the cells before the link will have name, number, dates etc, with a first line in the email “Please see below booking” etc and a line at the end “any problems please let me know” etc. When i use the hyperlink function there aren’t enough characters for everything I need in the email, and it puts all the info on one line with no spaces. Is there a way around this? This is the hyperlink formula I have used =HYPERLINK(“Mailto:”&AD5&”?Subject=”&AE5&”&cc=”&AF5&AG5&”&Bcc=”&I4&”&Body=”&AI5&T4&G4&H4&Q4&S4,”EMAIL”)
The VBA method 2 you have shown looks similar to what I need but without the “IF >2000” addition.
I have also never attempted VBA before, so I was going to try and copy and paste your VBA and edit it to what I need but I am not that talented just yet!
Thanks for your help
Hello Danielle D, Thank you for your question. For the first question, you can add a line break using the CHAR function. CHAR(10) to be more specific. There are some feature limitations in the HYPERLINK method. You need to use the other methods to do more advanced stuff.
Then, for the second question, if you send your Excel file to [email protected], we will try to modify the VBA code according to your needs.
Is it possible to send images from excel via e-mail at a specific time every day automatically not manually?
Hello Ahmed, thank you for reaching out. You can send email in a certain time of a day automatically, but you have to keep the Excel file open. One more thing, you cannot send email automatically by Excel everyday. You have to set up the time and then run the Macro. The code is given below.
In the picture, I’m providing you some direction regarding where you may need to change the code element.
Note: If the code doesn’t work, make sure you have an Outlook account and open the Microsoft Office App.
Thanks a lot for your appreciation.
I would like to know the VBA or Macro to use on a spreadsheet where I want to email my client but want the email to be one of my Outlook Templates already created. So, if I click on the client’s email address from the spreadsheet or click on a Macro button in the clients contact information row, it launches my Outlook Template filling in the client’s email address and inserts their name at the beginning of the body message.
Is this possible? I’m struggling to find a video or web pages to show how this is done.
Thanks a lot for posting your query in Exceldemy
Below I am giving you a VBA code using which you can open the outlook template saved on your pc and then replace them with desired text from the Excel sheet.
the sample template file that we are using is given below.
The Name, in the beginning, is going to be replaced by the desired name and the recipient will be the selected mail address.
In the code, two separate things must be taken seriously, firstly the location of the template file. The location of the template mail has to be specified correctly in the code(Marked as 1), as shown in the image. So please rewrite the location of the file correctly.
secondly, f you need to specify which term to replace with names inside the code(Marked as 2).
the location of the template file in your pc can be found in the properties tab as shown below.
finally, you are ready to execute the code.For this, you have to first select the email address to which you want to send the email. Then Run the Macro by pressing the Macro button placed just right side of the information.
Then you can see that the a new email has been composed with the name placed in the beginning with the selected mail address as the recipient.
You can download the sample macro file from the links below
Sample Macro File: https://www.exceldemy.com/wp-content/uploads/2023/02/Send-Automatic-Email-outlook-template.xlsm\
In case you need to template file, please contact us through email.
How to add Attachement in Method 1
Hello SANJAY DANGI,
Thank you for reaching out. You can add attachment to the email in the first method easily. Follow these steps to do it.
Sub ExcelToOutlookSR() Dim mApp As Object Dim mMail As Object Dim SendToMail As String Dim MailSubject As String Dim MailBody As String Dim FileName As String Dim Path As String 'Declare variable for file path Path = "D:\Exceldemy\" 'Set file path For Each r In Selection SendToMail = Range("C" & r.Row) MailSubject = Range("F" & r.Row) MailBody = Range("G" & r.Row) FileName = Range("H" & r.Row) 'Get file name from H column Set mApp = CreateObject("Outlook.Application") Set mMail = mApp.CreateItem(0) With mMail .To = SendToMail .Subject = MailSubject .Body = MailBody .Display .Attachments.Add (Path & FileName) 'Add attachment End With Next r Set mMail = Nothing Set mApp = Nothing End Sub