How to Generate Automatic Email Alerts in Excel (2 Easy Ways)

In this article, we will learn how to generate automatic email alerts in Excel. In the banking or financial system, we have to deal with a lot of information. And, we also need to inform clients about important deadlines. But, it is not possible to send emails manually as there are a lot of customers. So, it is important to generate automatic email alerts. In Excel, we can do this task easily. So, without any delay, let’s start the discussion.


Download Practice Workbook

To practice by yourself, download the following workbook.


2 Easy Ways of Generating Automatic Email Alerts in Excel

To demonstrate the method, we will use the following dataset that includes emails of various people, as well as the deadlines they must meet for various reasons. We will show you 2 easy ways of generating automatic email alerts in Excel.

excel automatic email alerts


1. Use Power Automate to Generate Automatic Email Alerts in Excel

We can generate automatic email alerts by using the Power Automate application of Microsoft 365. Let’s see the steps to learn the method.


STEP 1: Make Dataset Ready

  • We will work with the following dataset mentioned before.
  • Here, we will send email alerts automatically according to the deadline.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • Firstly, you need to make a table from your dataset.
  • So, select any cell of the dataset and press Ctrl + T.
  • Instantly, a dialog box of Create Table is showing.
  • Here, automatically the range of the table is selected as the range of the dataset.
  • Now, press OK to generate the table.

  • As a result, a table is formed.
  • Save your Excel file on OneDrive for Business or SharePoint.
  • After saving, go to the Microsoft 365 application.


STEP 2: Launch Power Automate Application

  • In the following step, click on the following icon to open Power Automate.

Use Power Automate to Generate Automatic Email Alerts in Excel

Note: It doesn’t work with OneDrive Personal accounts, and you need a Microsoft 365 account.
  • In the following step, we will open up the Power Automate application.
  • So, click on All apps to see the list of apps of MS 365.

  • After that, select the Power Automate application.

  • In Power Automate, click on Create and then choose Scheduled Cloud Flow.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • After that, give a name to the power flow.
  • Here, we have given the name Automated Email Alert.
  • We want the alerts to send out daily.
  • That’s why we have selected Repeat every 1 Day.
  • Then, click on Create to proceed.


STEP 3: Link Between Power Automate and Excel

  • Now, we have a single step of Recurrence.
  • After that, we need to add other steps below that.
  • Therefore, click on New Step to add other steps.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • After that, choose List rows present in a table from the Actions tab.
  • If you do not see this option, you can also search in the search box.

  • After that, to select Location, click on the drop-down menu of the Location.
  • Then, select OneDrive For Business from there.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • Similarly, select OneDrive for Document Library.

  • Next, navigate to the file location and select the file.
  • We have saved our Excel file in the Softeko folder.
  • That’s why we have selected it.

  • Now, select the table name containing your dataset.
  • Before, we have named the table as Table 1 in Excel.
  • Therefore, we have selected Table 1.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • In the following step, click on Show Advanced Options to choose advanced options.

  • After opening the Advanced Options, type in the Filter Query field: <span style="font-size: 12pt;">Deadline eq'</span>.
  • Then, click on ‘Add dynamic content’ and it will open a floating wizard.
  • From the wizard, go to the Expression tab and enter this formula in the formula bar:
formateDateTime(convertFromUTC(utcNow(),'Central America Standard Time'),'yyyy-MM-dd')

Use Power Automate to Generate Automatic Email Alerts in Excel

Here, we have formatted the Deadline column of the Excel file in the standard format of Central America Standard Time.
  • After writing the formula end with '.

  • After that, set the DateTime Format to ISO 8601.

Use Power Automate to Generate Automatic Email Alerts in Excel


STEP 4: Extract List of Emails

  • In the next step, we need to get a list of emails so that each person only gets one email.
  • Therefore, select Next step and in the search box type ‘select’ and choose it from the list of Actions.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • After that, click on Add dynamic content and choose Value from the list.

  • Then, click on the Text icon after selecting the Map option.

  • Now, search Email in the search bar and select Email from the list.


STEP 5: Compose Extracted List of Emails

  • Similarly, add another step and search Compose in the search bar.
  • Then, select Compose from there.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • Instantly, Compose operation will be shown and click Add dynamic content.
  • Then, in the Expression bar write the following formula:
union(body(‘Select’),body(‘Select’))
  • Then, click OK to proceed.


STEP 6: Add Control to Each Email

  • In the next step, we will add a Control operation in the flow.
  • So, select Control.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • Then, select Apply to each from the Actions tab.

  • Then, click on the box below Select an Output from previous steps.
  • After that, select Outputs using Add dynamic content icon.


STEP 7: Extract Data for Each Email

  • Now, we will add actions under Apply to each action.
  • So, click on Add an action.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • Now, we will add the Filter array action.
  • So, search filter array in the search bar and select Filter array.

  • After that, select Value from the wizard occurred after clicking on the following icon.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • In the next field, choose to Add dynamic content icon and select ‘Email’ from there.

  • Similarly, select the Current item in the following field.


STEP 8: Generate HTML for Sending Email Alerts

  • In the following step, we will generate HTML for Email.
  • Therefore, click on Add an action and search Create HTML Table on the search box.
  • Then, select Create HTML Table from there.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • In the From field, click on the box and then press on add dynamic content icon.
  • From the floating wizard, select Body.
  • After that, click on Show advanced options. 

  • Now, more fields can be visible.
  • From the Columns field, select Custom to select the custom column of the table.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • Now, we will enter names for the columns that we want in your table names in the Header column.
  • Therefore, in the 1st column of the Header field, write Deadline.
  • And, in the 2nd column, write the following formula in the Expression bar:
formatDateTime(item()[‘Deadline’],’MMM d,yyyy’)

Here, we will format the value of the Deadline column in the definite format.

  • Similarly, we will add another column named Topic.
  • And, we will set the value of the column by writing the following formula in the Expression bar:
item()[‘Topic’]


STEP 9: Automatically Create Email in Outlook Application

  • Similarly, we will add another action named Send an email (V2).

Use Power Automate to Generate Automatic Email Alerts in Excel

  • In the following step, click on the To field and click on Add dynamic content icon.
  • From the floating wizard, select the Current item from the list.

  • After that, write the subject of the mail in the Subject field.
  • Then, in the Body field, press on Add dynamic content icon and select Output from the list.

  • Moreover, you can choose advanced options by clicking on Show advanced options.

Use Power Automate to Generate Automatic Email Alerts in Excel

  • Now, select Save.
  • Finally, your flow is ready.
  • You can test your results by clicking on the Test button.

  • So, select Manually to manually test the flow.
  • Finally, open the Outlook application to observe the automated sent email alerts.


Final Output

  • Here, you can observe a sample message on the Outlook application.

Use Power Automate to Generate Automatic Email Alerts in Excel


2. Send Automatic Email Alerts from Excel Worksheet Using VBA

We can generate automatic email alerts by using VBA Code. Let’s see the steps to learn the method.

STEPS:

  • We will work with the following dataset mentioned before.
  • Here, we will send email alerts automatically according to the deadline.
  • If the deadline is between 1 and 7 days, we will generate an automated email alert.

Send Automatic Email Alerts from Excel Worksheet Using VBA

  • Firstly, to open the VBA Macro, press Alt + F11.
  • Alternatively, click on the Developer tab.
  • Then, select Visual Basic from there.

  • Instantly, Microsoft Visual Basic for Application window will be shown up.
  • From the Insert tab, select the Module option.

  • To send reminder emails automatically, paste the following VBA codes in the module:
Public Sub SendReminderMail()
'Declare the variables
    Dim XDueDate As Range
    Dim XRcptsEmail As Range
    Dim xMailContent As Range
    Dim xRngDn As Range
    Dim xCrtOut As Object
    Dim xValDateRng As String
    Dim xValSendRng As String
    Dim k As Long
    Dim xMailSections As Object
    Dim xFinalRw As Long
    Dim CrVbLf As String
    Dim xMsg As String
    Dim xSubEmail As String
    On Error Resume Next
    'To select the date column insert a input box
    Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
    If XDueDate Is Nothing Then Exit Sub
    'Insert a input box for selecting the recipients
    Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
    If XRcptsEmail Is Nothing Then Exit Sub
    'To enter the text mail, insert a input box
    Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
    If xMailContent Is Nothing Then Exit Sub
    'Count rows for the due dates
    xFinalRw = XDueDate.Rows.Count
    Set XDueDate = XDueDate(1)
    Set XRcptsEmail = XRcptsEmail(1)
    Set xMailContent = xMailContent(1)
    'Set command to open MS Outlook Application
    Set xCrtOut = CreateObject("Outlook.Application")
    'Apply For loop to conduct the operation in each row one by one
    For k = 1 To xFinalRw
        xValDateRng = ""
        xValDateRng = XDueDate.Offset(k - 1).Value
        'Apply If condition for the Due Date values
        If xValDateRng <> "" Then
        'Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
        'Means 1 < X< 7, X = Due Date - Current Date
        If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
            xValSendRng = XRcptsEmail.Offset(k - 1).Value
            'Create the subject, body and text contents with the required variables
            xSubEmail = xMailContent.Offset(k - 1).Value & " on " & xValDateRng
            CrVbLf = "<br><br>"
            xMsg = "<HTML><BODY>"
            xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
            xMsg = xMsg & "Text : " & xMailContent.Offset(k - 1).Value & CrVbLf
            xMsg = xMsg & "</BODY></HTML>"
            'Create the email
            Set xMailSections = xCrtOut.CreateItem(0)
            'Define the position to place the Subject, Body and Recipients Address
            With xMailSections
                .Subject = xSubEmail
                .To = xValSendRng
                .HTMLBody = xMsg
                .Display
                .Send
            End With
            Set xMailSections = Nothing
        End If
    End If
    Next
    Set xCrtOut = Nothing
End Sub

Send Automatic Email Alerts from Excel Worksheet Using VBA

  • After saving, press F5 to run the program.
  • Alternatively, you can also run the program by clicking on the following icon.

  • After running, an input message box will float up.
  • So, we have to select the column of the deadline.
  • Therefore, we have selected the D5:D10 range and pressed OK.

  • After that, another input message box will float up.
  • Then, we have to select the column of the mail address.
  • Therefore, we have selected the B5:B10 range and pressed OK.

Send Automatic Email Alerts from Excel Worksheet Using VBA

  • Now, the final input message box will float up.
  • So, select the column of the Topic.
  • Therefore, we have selected the C5:C10 range and pressed OK.

  • As a result, all of your emails will be sent as reminder emails before the due dates.
  • Moreover, you can also choose to manually send the emails using the automated program described above.
  • To do so, simply place an apostrophe (‘) before the (.send) command to disable it, as shown in the image below.

Send Automatic Email Alerts from Excel Worksheet Using VBA

  • As a result, you will receive all completed drafts prior to sending them.
  • Then, by simply clicking the Send button, select whom you want to send emails to.

Read More: How to Set Due Date Reminder Formula in Excel (2 Easy Methods)


Conclusion

In this article, we have demonstrated how to generate Excel automatic email alerts. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out the ExcelDemy website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.


Related Articles

Sudipta Chandra Sarker

Sudipta Chandra Sarker

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel. Here I will be posting articles related to this. My educational degree is BSc in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, Bangladesh. I have a great interest in research and development. I always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo