Automate Email Alerts in Excel: 2 Simple Methods for Efficient Deadline Tracking

We will use the following dataset that includes emails of various people, as well as the deadlines they must meet, then set up email alerts based on those dates.

Method 1 – Use Power Automate to Generate Automatic Email Alerts in Excel

Step 1 – Prepare the Dataset

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

• Select any cell of the dataset and press Ctrl + T.
• A dialog box of Create Table is showing.
• The range of the table is the range of the dataset.
• Press OK to generate the table.

• Save your Excel file on OneDrive for Business or SharePoint.
• Go to the Microsoft 365 application.

Step 2 – Launch Power Automate

• Click on the icon on the top left (nine dots in a square).

Note: It doesn’t work with OneDrive Personal accounts, and you need a Microsoft 365 account.

• Click on All apps to see the list of apps of MS 365.

• Select the Power Automate application.

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

• Give a name to the power flow. We have used the name Automated Email Alert.
• We want the alerts to be sent out daily, so we have selected Repeat every 1 Day.
• Click on Create.

Step 3 – Link Power Automate and Excel

• We have a single step of Recurrence.
• Click on New Step to add other steps.

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

• Click on the drop-down menu for Location.
• Select OneDrive for Business.

• Select OneDrive for Document Library.

• Navigate to the file location and select the file.

• Select the table name containing your dataset. We have used Table 1 in Excel.

• Click on Show Advanced Options.

• In the Filter Query field, put: Deadline eq'.
• Click on Add dynamic content and it will open a floating 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.

• End with'.

• Set the DateTime Format to ISO 8601.

Step 4 – Extract the List of Emails

• Select Next step and in the search box type select and choose Select: Data Operation from the list of Actions.

• Click on Add dynamic content and choose Value from the list.

• Click on the Text icon after selecting the Map option.

• Search Email in the search bar and select Email from the list.

Step 5 – Compose the Extracted List of Emails

• Add another step and search Compose in the search bar.
• Select Compose.

• The Compose operation will be shown, so click Add dynamic content.
• In the Expression bar, put the following formula:

union(body('Select'),body('Select'))


• Click OK to proceed.

Step 6 – Add Control to Each Email

• Select Control.

• Select Apply to each from the Actions tab.

• Click on the box below Select an Output from previous steps.
• Select Outputs using the Add dynamic content icon.

Step 7 – Extract Data for Each Email

• Click on Add an action.

• Search filter array in the search bar and select Filter array.

• Select Value from the wizard occurred after clicking on the plus icon on the right.

• Choose Add dynamic content and select Email from there.

• Select the Current item in the third field.

Step 8 – Generate HTML for Email Alerts

• Click on Add an action and search Create HTML Table on the search box.
• Select Create HTML Table.

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

• From the Columns field, select Custom.

• Enter names for the columns that you want in your table names in the Header column.
• In the 1st column of the Header field, write Deadline.
• In the 2nd column, use the following formula in the Expression bar:

formatDateTime(item()['Deadline'],'MMM d,yyyy')

We will format the value of the Deadline column in the definite format.

• Add another column named Topic.
• Set the value of the column by using the following formula in the Expression bar:

Step 9 – Automatically Create Emails in Outlook

• Add another action named Send an email (V2).

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

• Write the subject of the mail in the Subject field.
• In the Body field, click Add dynamic content and select Output from the list.

• You can choose advanced options by clicking on Show advanced options.

• Select Save.
• Your flow is ready. You can test your results by clicking on the Test button.

• Select Manually to manually test the flow.
• Open the Outlook application to check the automated email alerts.

Final Output

• Here’s a sample message on the Outlook application.

Method 2 – Send Automatic Email Alerts from the Excel Worksheet Using VBA

STEPS:

• We will work with the following dataset mentioned before.
• If the deadline is between 1 and 7 days from today, we will generate an automated email alert.

• To open the VBA Macro, press Alt + F11.

• The Microsoft Visual Basic for Application window will pop up.
• From the Insert tab, select the Module option.

• Paste the following VBA code 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.
• You can also run the program by clicking on the Play icon.

• An input message box will pop up.
• Select the column for the deadline. We have selected the D5:D10 range and pressed OK.

• Another input message box will float up.
• Select the column for the mail addresses. We have selected the B5:B10 range and pressed OK.

• The final input message box will float up.
• Select the column for the Topic. We have selected the C5:C10 range and pressed OK.

• All of your emails will be sent as reminder emails before the due dates.
• You can also choose to manually send the emails using the automated program described above. Place an apostrophe (‘) before the (.send) command to disable it, as shown in the image below.

• You will get all the completed drafts in the app.
• By clicking the Send button, select whom you want to send emails to.

Download the Practice Workbook

Related Articles

• How to Create Popup Reminder in Excel
• How to Create Notifications or Reminders in Excel
• How to Create Alerts in Excel
• How to Disable Alerts in Excel VBA
• How to Set Due Date Reminder Formula in Excel
• How to Set Due Date Reminder in Excel

<< Go Back to Alerts in Excel | Learn Excel