Sudipta Chandra Sarker
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.
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.
- 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.
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.
- 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.
- 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.
- 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.
- 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')
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.
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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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).
- 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.
- 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.
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.
- 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
- 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.
- 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.
- 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.
