How to Send Automatic Email from Excel to Outlook (4 Methods)

We have Employee Restructured Salary data in Excel, and we want to send automatic emails using Outlook.

Opening Microsoft Visual Basic and Inserting Code in Module

Opening the VBA Window:

• Using Keyboard Shortcuts: Press Alt + F11.
• Using the Developer Tab: In an Excel worksheet, go to the Developer Tab and select Visual Basic.

• Using the Worksheet Tab: Go to any worksheet, right-click on its name and choose View Code from the Context Menu.

Inserting a Module in Microsoft Visual Basic:

• Using Sheet Options: Select a Worksheet, then right-click on it, select Insert from the Context Menu, then choose Module.

• Using the Toolbar: Select Insert from the Toolbar and choose Module.

Method 1 – Using Excel VBA Macro to Automatically Send Email from Outlook to the Selected Recipients

Steps:

• Go to the Insert tab.
• Select Shapes.
• Select any of the offered shapes (i.e., Rectangular: Rounded Corners).

• Drag the Plus Icon wherever you want to insert the Shape as shown in the image below.

• Choose a preferred Shape Fill and Outline Color, then right-click on the shape.
• Click on Edit Text to insert text.

• Open Microsoft Visual Basic and insert a 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

1 – starts the macro procedure by declaring the variables as Object and String.

2 – runs a VBA FOR loop for each row in the selection to assign Email’s Send To, Subject, and Body using row entries.

3 – assigns variables.

4 – performs 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 – finishes the VBA FOR loop.

• Return to the worksheet.
• Right-click on the Shape and select Assign Macro from the Context Menu options.

• Select the Macro ExcelToOutlookSR under the Macro name and choose the Macro in option as This Workbook.
• Click on OK.

• In the worksheet, select one or multiple employees, then click on the Shape.

• Excel prompts Outlook to launch and creates or sends emails to the selected employees. Since we selected two employees, Outlook generated two different email drafts ready to be sent.

• Use the Send command to automatically send emails from Excel to Outlook using cell entries.

Read More: How to Send Excel File to Email Automatically

Method 2 – Sending Email Automatically from Excel to Outlook Depending on Specific Cell Value

We have Quarterly Sales Data as depicted below. After achieving a target (i.e., Sales > 2,000), we will automatically prompt Outlook to send an email from Excel to an assigned email.

• Use the following macro code in a VBA 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

1 – assigns 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 – declares variable types and assign them to populate Outlook’s entries.

3 – performs 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 – clears certain variables.

• Use the F5 key to run the macro.
• Excel fetches Outlook with a draft email created automatically as shown.
• You can click on Send or auto-send using the Send command in the macro.

Read More: Automatically Send Emails from Excel Based on Cell Content

Method 3 – Creating VBA Macro to Send Email with the Active Worksheet from Excel by Outlook

• Insert the following macro in a 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

1 – declares and set the variables.

2 – assigns the commands using the VBA With statement. Use the Display or Send command for reviewing or direct sending emails respectively.

3 – clears the previously set variables.

4 – assigns the VBA With commands with texts.

5 – executes the VBA Custom Function.

• Press F5, and you’ll get a draft email to review similar to the below image.

Read More: How to Send Multiple Emails from Excel Spreadsheet

Method 4 – Sending an Automatic Email from Excel to Outlook Using the HYPERLINK Function

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

• Hit Enter to paste the link.
• Click on the link.

• Excel takes you to Outlook. You’ll see all the Outlook entries are filled with assigned data from Excel.
• Click on Send.

• Drag the Fill Handle to apply the formula to other cells and repeat sending.

Read More: How to Send an Editable Excel Spreadsheet by Email

Download the Excel Workbook

Related Articles

• How to Send Bulk Email from Outlook Using Excel
• How to Send Email Automatically When Condition Met in Excel
• How to Send Email If Conditions Met in Excel
• How to Automatically Send Email from Excel Based on Date

<< Go Back To Send Email from Excel | Learn Excel