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

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.

excel automatic email alerts


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.

Use Power Automate to Generate Automatic Email Alerts in Excel

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

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

Use Power Automate to Generate Automatic Email Alerts in Excel

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

Use Power Automate to Generate Automatic Email Alerts in Excel

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

Use Power Automate to Generate Automatic Email Alerts in Excel

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

Use Power Automate to Generate Automatic Email Alerts in Excel

  • Click on Show Advanced Options.

  • In the Filter Query field, put: <span style="font-size: 12pt;">Deadline eq'</span>.
  • 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')

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.
  • End with'.

  • Set the DateTime Format to ISO 8601.

Use Power Automate to Generate Automatic Email Alerts in Excel


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.

Use Power Automate to Generate Automatic Email Alerts in Excel

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

Use Power Automate to Generate Automatic Email Alerts in Excel

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

Use Power Automate to Generate Automatic Email Alerts in Excel

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

Use Power Automate to Generate Automatic Email Alerts in Excel

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

Use Power Automate to Generate Automatic Email Alerts in Excel

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

Use Power Automate to Generate Automatic Email Alerts in Excel

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

Use Power Automate to Generate Automatic Email Alerts in Excel

  • 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:
item()[‘Topic’]


Step 9 – Automatically Create Emails in Outlook

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

Use Power Automate to Generate Automatic Email Alerts in Excel

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

Use Power Automate to Generate Automatic Email Alerts in Excel

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

Use Power Automate to Generate Automatic Email Alerts in Excel


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.

Send Automatic Email Alerts from Excel Worksheet Using VBA

  • 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

Send Automatic Email Alerts from Excel Worksheet Using VBA

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

Send Automatic Email Alerts from Excel Worksheet Using VBA

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

Send Automatic Email Alerts from Excel Worksheet Using VBA

  • 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


<< Go Back to Alerts in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sudipta Chandra Sarker
Sudipta Chandra Sarker

Sudipta Chandra Sarker, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked on the ExcelDemy project for over a year. For ExcelDemy, he has authored 42 articles and reviewed over ten articles. He is employed as a junior software developer at the moment. He aims to create various useful Microsoft Office Add-ins, extending the functionality of Office programs. His interests span Microsoft Office Suites, Data Science, VBA, VB.NET, ASP.NET, C#, Excel, and Desktop... Read Full Bio

25 Comments
  1. Hi,
    I have tried to use the VBA code and it almost works perfectly.
    However, I don’t receive an email every day as I should (I have made my own document that has consecutive dates). But when I run the VBA code then I receive all the emails that I should have received automatically.

    Also, I have tried to copy the VBA code in Module1 (does not work) and also the Tabel1 sheet.

    Is it meant to receive emails automatically?

    Kind regards
    Dennis

    • Thank you DENNIS for your comment.
      The code is automatic since whenever you run it, you don’t need to check which deadlines are 1 to 7 days away from the current date. Also, the code creates a draft automatically. However, if you want to automate the whole process, you will have to modify the code slightly and create a task scheduler. Copy the following code and paste it into the VBA module:

      
      Private Sub Workbook_Open()
          ' Call the SendReminderMailAutomatically subroutine when the workbook is opened
          SendReminderMailAutomatically
      End Sub
      
      Public Sub SendReminderMailAutomatically()
          Public Sub SendReminderMailAutomatically()
          ' Declare the variables
          Dim ws As Worksheet
          Dim XDueDate As Range
          Dim XRcptsEmail As Range
          Dim xMailContent As Range
          Dim xCrtOut As Object
          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
          
          ' Set the worksheet
          Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace "YourSheetName" with the actual sheet name
          
          ' Define the ranges from the worksheet
          Set XDueDate = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) ' Assuming due dates are in column A starting from row 2
          Set XRcptsEmail = ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row) ' Assuming email addresses are in column B starting from row 2
          Set xMailContent = ws.Range("C2:C" & ws.Cells(ws.Rows.Count, "C").End(xlUp).Row) ' Assuming email content is in column C starting from row 2
          
          ' Count rows for the due dates
          xFinalRw = XDueDate.Rows.Count
          
          ' 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
              ' Apply If condition for the Due Date values
              If XDueDate.Cells(k).Value  "" 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(XDueDate.Cells(k).Value) - Date  0 Then
                      ' Create the subject, body, and text contents with the required variables
                      xValSendRng = XRcptsEmail.Cells(k).Value
                      xSubEmail = xMailContent.Cells(k).Value & " on " & XDueDate.Cells(k).Value
                      CrVbLf = vbCrLf
                      xMsg = "Dear " & xValSendRng & CrVbLf
                      xMsg = xMsg & "Text : " & xMailContent.Cells(k).Value & CrVbLf
                      
                      ' 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
                          .Body = xMsg
                          .Send
                      End With
                      
                      Set xMailSections = Nothing
                  End If
              End If
          Next
          
          Set xCrtOut = Nothing
      End Sub
          MsgBox "Reminder emails have been sent.", vbInformation
      End Sub
      

      Create a task-scheduler:
      Now, follow the steps below to create a task scheduler:
      1. Type “Task Scheduler” in the Windows search bar and press “Enter”.
      2. In the right-hand Actions pane, click on “Create Basic Task”. Set a name and description for your task.
      3. Choose Daily in the “Trigger” option and Start a Program in the “Action” part.
      4. In the Program/Script box, give the directory of “excel.exe” file.
      5. In the “Add arguments” field, specify the full path to your Excel file.
      6. Click “Finish.”

      Hopefully, following the steps above, you will be able to perform your desired task.

      Regards
      Md Junaed Ar Rahman

  2. good day, I have tried this code, and it does not work. the first code worked great, except it does not send automatically. how can i get it qutomaticly?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Mar 31, 2024 at 4:27 PM

      Hello Isaac Chavez

      Thanks for visiting our blog and sharing your problem. I have developed an Excel VBA Sub-procedure that automatically sends emails.

      SOLUTION Overview:

      All you need to do is to click on Allow when the Microsoft Outlook dialog box appears.

      Excel VBA Sub-procedure:

      Sub SendEmails()
      
          Dim deadlineRange As Range
          Dim emailRange As Range
          Dim topicRange As Range
          Dim ws As Worksheet
          Dim outlookApp As Object
          Dim outlookMail As Object
          Dim i As Long
          
          On Error Resume Next
          Set deadlineRange = Application.InputBox("Select the range of deadlines:", Type:=8)
          On Error GoTo 0
          If deadlineRange Is Nothing Then Exit Sub
          
          On Error Resume Next
          Set emailRange = Application.InputBox("Select the range of emails:", Type:=8)
          On Error GoTo 0
          If emailRange Is Nothing Then Exit Sub
          
          On Error Resume Next
          Set topicRange = Application.InputBox("Select the range of topics:", Type:=8)
          On Error GoTo 0
          If topicRange Is Nothing Then Exit Sub
          
          Set ws = ThisWorkbook.ActiveSheet
          
          Set outlookApp = CreateObject("Outlook.Application")
          
          For i = 1 To emailRange.Rows.Count
      
              Set outlookMail = outlookApp.CreateItem(0)
              
              With outlookMail
                  .To = emailRange.Cells(i, 1).Value
                  .Subject = topicRange.Cells(i, 1).Value & " " & Format(deadlineRange.Cells(i, 1).Value, "dd mmmm yyyy")
                  .Body = "Dear," & vbCrLf & vbCrLf & _
                          "TEXT: " & topicRange.Cells(i, 1).Value & vbCrLf & _
                          "DEADLINE: " & Format(deadlineRange.Cells(i, 1).Value, "dd mmmm yyyy") & "." & vbCrLf & vbCrLf & _
                          "Kind regards,"
                  .Display
                  .Send
              End With
              
              Set outlookMail = Nothing
          Next i
          
          Set outlookApp = Nothing
          Set ws = Nothing
          Set topicRange = Nothing
          Set emailRange = Nothing
          Set deadlineRange = Nothing
      
      End Sub

      When testing the sub-procedure, I accidentally emailed you by pressing Allow in the Microsoft Outlook dialog box. Please ignore that. I hope the sub-procedure will fulfil your goal; good luck.

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  3. Everything works great for 2. Send Automatic Email Alerts from Excel Worksheet Using VBA.
    Now how do I get it to generate automatically daily without having to physically go into excel and manually run macro.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 3, 2024 at 4:47 PM

      Dear Issac

      Thanks for letting us know that the previously provided SendEmails sub-procedure worked on your side. Now, you want to automate this task daily. To achieve this, you create a Workbook_Open event and call the SendEmails sub-procedure. Later, you must use the Task Scheduler to open the workbook daily at a particular time. I have improved the SendEmails sub-procedure and created a solution to your problem using the mentioned idea.

      Follow these steps:

      1. Press ALt+F11, click on Insert followed by Module.
      2. Insert the following code in the module1 and save it:
        Sub SendEmails()
        
            Dim deadlineRange As Range
            Dim emailRange As Range
            Dim topicRange As Range
            Dim ws As Worksheet
            Dim outlookApp As Object
            Dim outlookMail As Object
            Dim i As Long
            
            On Error Resume Next
            Set deadlineRange = Application.InputBox("Select the range of deadlines:", Type:=8)
            On Error GoTo 0
            If deadlineRange Is Nothing Then Exit Sub
            
            On Error Resume Next
            Set emailRange = Application.InputBox("Select the range of emails:", Type:=8)
            On Error GoTo 0
            If emailRange Is Nothing Then Exit Sub
            
            On Error Resume Next
            Set topicRange = Application.InputBox("Select the range of topics:", Type:=8)
            On Error GoTo 0
            If topicRange Is Nothing Then Exit Sub
            
            Set ws = ThisWorkbook.ActiveSheet
            
            Set outlookApp = CreateObject("Outlook.Application")
            
            For i = 1 To emailRange.Rows.Count
        
                Set outlookMail = outlookApp.CreateItem(0)
                
                With outlookMail
                    .To = emailRange.Cells(i, 1).Value
                    .Subject = topicRange.Cells(i, 1).Value & " " & Format(deadlineRange.Cells(i, 1).Value, "dd mmmm yyyy")
                    .Body = "Dear," & vbCrLf & vbCrLf & _
                            "TEXT: " & topicRange.Cells(i, 1).Value & vbCrLf & _
                            "DEADLINE: " & Format(deadlineRange.Cells(i, 1).Value, "dd mmmm yyyy") & "." & vbCrLf & vbCrLf & _
                            "Kind regards,"
                    .Display
                    On Error Resume Next
                    .Send
                End With
                
                Set outlookMail = Nothing
            Next i
            
            Set outlookApp = Nothing
            Set ws = Nothing
            Set topicRange = Nothing
            Set emailRange = Nothing
            Set deadlineRange = Nothing
        
        End Sub
      3. Later, right-click on ThisWorkbook under the VBA Project section and click on View Code.
      4. Paste the following code in the workbook module and save it:
        Private Sub Workbook_Open()
            Call Module1.SendEmails
        End Sub

      5. Now, open the Task Scheduler and close the Excel file.
      6. In the Task Scheduler window, click on Create Basic Task.
      7. In the Create Basic Task Wizard:
        • >> Name the task and add descriptions.
        • >> Click on Next.
      8. Now, choose Task Trigger as daily.
      9. Select the desired date and time and click on Next.
      10. At this time, choose Action as Start a program.
      11. Browse to choose EXCEL.EXE.
      12. Insert the intended Excel file path in the Add arguments section.
      13. Finally, click on Finish.
      14. Now, wait until the start time is reached, previously set within Task Scheduler.
      15. When it is time, the Excel File will open.
      16. As a result, the SendEmails sub-procedure will called from the Workbook_Open event.

      Hopefully, you have found the ultimate solution to your requirements. I have attached the solution workbook; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

      • Hello,

        I have the routine for “Public Sub SendReminderMail()” working correctly, however I would like to add an additional filter to the data. Along with returning dates between 0 and x days, I would like it to further filter that dataset by only returning dates in that range and also the corresponding cell in column O is blank (=””). How would I add that?

        would it be an “AND” statement in the following line:
        If CDate(xValDateRng) – Date 0 Then…

  4. Hi Lutfor,

    I can also confirm the SendEmails sub-routine works well. I was trying without success to modify it with another qualifier: as well as returning the values for dates between the declared range (i.e. 0 to 14 days), I would like to further filter the dataset if a respective cell in another column for the returned dataset row was blank (“”). For example, if my Date range is (“A2:A50”), and my Document submittal Date range is (“B2:B50”), I’m trying to send emails where the value in column “A” is within the date range “and” the value in column “B” is blank (“”).
    What would that syntax look like?
    Thanks!!

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 16, 2024 at 7:36 PM

      Hello Greg

      Thanks for visiting our blog and informing us that the previous solution worked perfectly. You want to filter the data that includes the date falling within the specified date range (0 to 14 days). Also, you want to include the rows where the document submittal date is blank.

      I have come up with an Excel VBA code. You can try it; if needed, make changes to fulfil your goal.

      SOLUTION Overview:

      Excel VBA Code:

      Sub SendEmails()
      
          Dim deadlineRange As Range
          Dim emailRange As Range
          Dim topicRange As Range
          Dim docSubmittalRange As Range
          Dim ws As Worksheet
          Dim outlookApp As Object
          Dim outlookMail As Object
          Dim i As Long
          
          On Error Resume Next
          Set deadlineRange = Application.InputBox("Select the range of deadlines:", Type:=8)
          On Error GoTo 0
          If deadlineRange Is Nothing Then Exit Sub
          
          On Error Resume Next
          Set emailRange = Application.InputBox("Select the range of emails:", Type:=8)
          On Error GoTo 0
          If emailRange Is Nothing Then Exit Sub
          
          On Error Resume Next
          Set topicRange = Application.InputBox("Select the range of topics:", Type:=8)
          On Error GoTo 0
          If topicRange Is Nothing Then Exit Sub
          
          On Error Resume Next
          Set docSubmittalRange = Application.InputBox("Select the range of document submittal dates:", Type:=8)
          On Error GoTo 0
          If docSubmittalRange Is Nothing Then Exit Sub
          
          Set ws = ThisWorkbook.ActiveSheet
          
          Set outlookApp = CreateObject("Outlook.Application")
          
          For i = 1 To emailRange.Rows.Count
      
              If deadlineRange.Cells(i, 1).Value >= Date And deadlineRange.Cells(i, 1).Value <= Date + 14 Then
      
                  If Trim(docSubmittalRange.Cells(i, 1).Value) = "" Then
                      Set outlookMail = outlookApp.CreateItem(0)
                      
                      With outlookMail
                          .To = emailRange.Cells(i, 1).Value
                          .Subject = topicRange.Cells(i, 1).Value & " " & Format(deadlineRange.Cells(i, 1).Value, "dd mmmm yyyy")
                          .Body = "Dear," & vbCrLf & vbCrLf & _
                                  "TEXT: " & topicRange.Cells(i, 1).Value & vbCrLf & _
                                  "DEADLINE: " & Format(deadlineRange.Cells(i, 1).Value, "dd mmmm yyyy") & "." & vbCrLf & vbCrLf & _
                                  "Kind regards,"
                          .Display
                          On Error Resume Next
                          .Send
                      End With
                      
                      Set outlookMail = Nothing
                  End If
              End If
          Next i
          
          Set outlookApp = Nothing
          Set ws = Nothing
          Set topicRange = Nothing
          Set emailRange = Nothing
          Set deadlineRange = Nothing
          Set docSubmittalRange = Nothing
      
      End Sub

      I have attached the solution workbook; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  5. Hi! The code doesn’t open my MS Outlook after running the code.

    I have a mac and run the lastest software. Does it also work on a MAC?

    Thank you!

    Lukas

    • Hello Lukas Cenjar,

      This VBA code won’t work on MAC but here we are giving you a sample code that might work on MAC. We updated our existing code based on MAC requirements.
      1. We used AppleScript to interact with the Mail application on macOS to create and send the email.
      2. Then, used MacScript to run AppleScript commands from VBA to handle Mac-specific scenarios.

      Public Sub SendReminderMail()
          'Declare the variables
          Dim XDueDate As Range
          Dim XRcptsEmail As Range
          Dim xMailContent As Range
          Dim xFinalRw As Long
          Dim xValDateRng As String
          Dim xValSendRng As String
          Dim k As Long
          Dim xSubEmail As String
          Dim xMsg As String
          Dim AppleScript As String
      
          'Input box for the deadline/due date column
          Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", Type:=8)
          If XDueDate Is Nothing Then Exit Sub
          
          'Input box for email addresses
          Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", Type:=8)
          If XRcptsEmail Is Nothing Then Exit Sub
          
          'Input box for email content
          Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", Type:=8)
          If xMailContent Is Nothing Then Exit Sub
          
          'Count rows for due dates
          xFinalRw = XDueDate.Rows.Count
      
          'Loop through each row
          For k = 1 To xFinalRw
              xValDateRng = XDueDate.Cells(k).Value
              
              'Check if the due date is within the next week
              If xValDateRng <> "" And CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
                  xValSendRng = XRcptsEmail.Cells(k).Value
                  xSubEmail = xMailContent.Cells(k).Value & " on " & xValDateRng
                  xMsg = "Dear " & xValSendRng & vbCrLf & "Text : " & xMailContent.Cells(k).Value
      
                  'AppleScript to send email
                  AppleScript = "tell application ""Mail""" & vbCrLf
                  AppleScript = AppleScript & "set newMessage to make new outgoing message with properties {subject:""" & xSubEmail & """, content:""" & xMsg & """, visible:true}" & vbCrLf
                  AppleScript = AppleScript & "tell newMessage" & vbCrLf
                  AppleScript = AppleScript & "make new to recipient at end of to recipients with properties {address:""" & xValSendRng & """}" & vbCrLf
                  AppleScript = AppleScript & "send" & vbCrLf
                  AppleScript = AppleScript & "end tell" & vbCrLf
                  AppleScript = AppleScript & "end tell"
      
                  'Run AppleScript from VBA
                  MacScript (AppleScript)
              End If
          Next
      End Sub
      

      Regards
      ExcelDemy

  6. Hi,

    The second method works for me. However, I would like to ask how can I edit the body of the mail, instead Dear “email address” and change to actual name. Also, the word “text” to be removed on the body of the mail.

    • Hello Mary Grace,

      Please use this updated code to get your desired result:
      Here I added an input box to select the column with the names of the recipients (XRcptsName ). Then, updated the email body to include the recipient’s actual name and removed the word “text.”

      Public Sub SendReminderMail()
      'Declare the variables
          Dim XDueDate As Range
          Dim XRcptsEmail As Range
          Dim XRcptsName 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
          Set XRcptsName = Application.InputBox("Choose the column for the name of the recipients:", "ExcelDemy", , , , , , 8)
          If XRcptsName 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 XRcptsName = XRcptsName(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 " & XRcptsName.Offset(k - 1).Value & CrVbLf
                  xMsg = xMsg & 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
      

      Regards
      ExcelDemy

  7. Hi,

    I have an Excel Spreadsheet containing a column Description of Lifting Equipment and a column for the Test Certificate Expiry Date.

    I formatted the cells to become yellow when the Expiry Date is within 30 Days of the Actual Date (Todays Date) , and Red when it has Expired…

    What I would like for Excel to do is send me a mail when the Cells reach their Conditions to Yellow and Red.

    Is that possible?

    Can someone help me with the Coding Please?

    Regards Vickie

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto May 26, 2024 at 9:12 AM

      Hello Vickie

      Thanks for visiting our blog and sharing an exciting problem with such clarity. I have reviewed your situation and developed two sub-procedures to fulfil your goal. Your problem is about automating email alerts in Excel based on conditional formatting for expiry dates of lifting equipment test certificates.

      Don’t worry! I have demonstrated it in an Excel workbook. Please check the following:

      Automate email alerts in Excel based on conditional formatting for expiry dates of lifting equipment test certificates

      Excel VBA Sub-procedure:

      Sub SendEmailAlerts()
      
          Dim ws As Worksheet
          Dim cell As Range
          Dim expiryDate As Date
          Dim today As Date
          Dim emailBody As String
          Dim OutApp As Object
          Dim OutMail As Object
      
          Set ws = ThisWorkbook.Sheets("Sheet1")
          today = Date
      
          Set OutApp = CreateObject("Outlook.Application")
          OutApp.Session.Logon
      
          For Each cell In ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
              If IsDate(cell.Value) Then
                  expiryDate = cell.Value
                  If expiryDate <= today Then
                      emailBody = "The test certificate for " & cell.Offset(0, -1).Value & " has expired on " & expiryDate & "."
                      Call SendEmail(OutApp, emailBody, "Expiry Alert: Red")
                  ElseIf expiryDate <= today + 30 Then
                      emailBody = "The test certificate for " & cell.Offset(0, -1).Value & " will expire on " & expiryDate & "."
                      Call SendEmail(OutApp, emailBody, "Expiry Alert: Yellow")
                  End If
              End If
          Next cell
      
          Set OutMail = Nothing
          Set OutApp = Nothing
      
      End Sub
      
      Sub SendEmail(OutApp As Object, emailBody As String, subject As String)
          Dim OutMail As Object
          Set OutMail = OutApp.CreateItem(0)
          
          With OutMail
              .To = "[email protected]"
              .subject = subject
              .HTMLBody = "<p>" & emailBody & "</p>"
              .display
              '.Send 'To send emails, uncomment the ".Send" command.
          End With
      End Sub

      I hope you have found the sub-procedures you were looking for. I have attached the solution workbook as well. Good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

      • Is it also possible to fit the system that it only sends 1 email at a time. I am now getting three mails, that are all the same because of an same expiration date? IS there someone who knows this?

        Kind regards,

        Roel

        • Avatar photo
          Shamima Sultana Oct 11, 2024 at 10:57 AM

          Hello Roel,

          To address your first concern about receiving multiple emails due to the same expiration date, you can modify the code to ensure only one email is sent per unique expiration date. This can be done by adding a simple check to track which dates have already triggered an email. Here’s how you can adjust the code:

          Sub SendEmailAlerts()
              Dim ws As Worksheet
              Dim cell As Range
              Dim expiryDate As Date
              Dim today As Date
              Dim emailBody As String
              Dim OutApp As Object
              Dim sentDates As Object
          
              Set ws = ThisWorkbook.Sheets("Sheet1")
              today = Date
              Set sentDates = CreateObject("Scripting.Dictionary") ' Track sent emails by date
              Set OutApp = CreateObject("Outlook.Application")
              OutApp.Session.Logon
              For Each cell In ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
                  If IsDate(cell.Value) Then
                      expiryDate = cell.Value        
                      ' Check if the expiry date has already triggered an email
                      If Not sentDates.exists(expiryDate) Then
                          If expiryDate <= today Then
                              emailBody = "The test certificate for " & cell.Offset(0, -1).Value & " has expired on " & expiryDate & "."
                              Call SendEmail(OutApp, emailBody, "Expiry Alert: Red")
                          ElseIf expiryDate <= today + 30 Then
                              emailBody = "The test certificate for " & cell.Offset(0, -1).Value & " will expire on " & expiryDate & "."
                              Call SendEmail(OutApp, emailBody, "Expiry Alert: Yellow")
                          End If             
                          ' Mark the expiry date as having been processed
                          sentDates.Add expiryDate, True
                      End If
                  End If
              Next cell
              Set OutApp = Nothing
          End Sub

          This update ensures that for any particular expiration date, only one email will be sent.

          Regards
          ExcelDemy

      • Hello, i have the same problem. I want to create a system in which i get an email based of a color change in an excel cell. I have used your new VBA sub procedure. However it won’t send me automatically an email with the expiration date. It does show me the concept version if i put an apostrophe before the send button. How do I fix it to send me an email automatically when the expiration date reaches 30 days?

        • Avatar photo
          Shamima Sultana Oct 11, 2024 at 11:00 AM

          Hello Roel,

          To ensure your system automatically sends an email when the expiration date reaches 30 days, you need to adjust your VBA code slightly. The apostrophe before the .Send line is preventing the email from being sent. Removing that apostrophe will allow the email to be sent automatically.

          Here’s how you can modify the SendEmail sub-procedure:

          Sub SendEmail(OutApp As Object, emailBody As String, subject As String)
              Dim OutMail As Object
              Set OutMail = OutApp.CreateItem(0)
          
              With OutMail
                  .To = "[email protected]" ' Change this to your email address
                  .Subject = subject
                  .HTMLBody = "<p>" & emailBody & "</p>"
                  .Send ' Uncomment this line to send the email automatically
              End With
          End Sub

          Make sure to replace “[email protected]” with your actual email address. Additionally, ensure that your main subroutine correctly checks for the conditions to trigger the email alert based on cell color changes.

          If you follow these steps, you should receive automatic email notifications as intended.

          Regards
          ExcelDemy

  8. I gave the Power automate procedure a go but I ran into a problem where I can no longer see the option to add dynamic content when the ‘To’ box when I am creating the ‘Send an Email (v2)’ Action.

    Is this a bug or something?

    • Hello Lorcan,

      As all the steps are sequential the issue of not seeing the option to add dynamic content in the ‘To’ box when creating the ‘Send an Email (v2)’ action in Power Automate might be due to a bug or a temporary glitch.

      Try the following steps to troubleshoot,

      1. Refresh your browser or reopen Power Automate.
      2. Clear your browser cache.
      3. Ensure all fields are properly configured before the ‘Send an Email (v2)’ action.
      4. Check for any updates to Power Automate.

      If the issue persists, consider reaching out to Microsoft support for further assistance. You can try our 2nd method until the issue is solved.

      Regards
      ExcelDemy

  9. Hi – the VBA code is just what I need. Is there a way to use this with email hosts that are not Outlook (specifically Gmail host)? Or a way to incorporate the code into a Google Sheets?

    Thank you

    • Hello Dan,

      Gmail doesn’t natively allow sending emails via VBA due to security restrictions. However, you can try to send emails via Gmail by using CDO (Collaboration Data Objects) with SMTP.

      Sub SendEmailGmail()
          Dim objMessage As Object
          Set objMessage = CreateObject("CDO.Message")
      
          objMessage.Subject = "Test Email"
          objMessage.From = "[email protected]"
          objMessage.To = "[email protected]"
          objMessage.TextBody = "This is a test email."
      
          With objMessage.Configuration.Fields
              .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
              .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
              .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
              .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
              .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
              .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
              .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your-password"
              .Update
          End With
          
          objMessage.Send
      End Sub

      Google may block this unless you’ve allowed less secure apps or set up an app-specific password if you’re using two-factor authentication (2FA).

      For Google Sheets, using Google Apps Script would be more appropriate since it integrates seamlessly with Gmail. Here’s how you can approach both:

      
      function sendEmail() {
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
        var recipient = "[email protected]";
        var subject = "Email from Google Sheets";
        var body = "This is an automated email from Google Sheets.";
        
        MailApp.sendEmail(recipient, subject, body);
      }

      Apps Script is directly integrated with Google’s ecosystem, making it an easier option for automating email alerts, and it’s more secure than trying to bypass restrictions in Gmail for VBA.

      Regards
      ExcelDemy

  10. I am getting this error:
    Syntax error at position 23 in ‘Deadline eq’2024-10-03’ ‘.
    inner exception: Syntax error at position 23 in ‘Deadline eq’2024-10-03’ ‘.
    clientRequestId: af7fc979-3139-434f-b116-401b0fde8af9

    when i try using Power Automate, can you help with a fix?

    • Hello Matthew Senko,

      It seems that you’re encountering a syntax error in your Power Automate flow. The issue is likely related to how the date is formatted in your query. Make sure that the date is enclosed in double quotes and use the correct format.
      Instead of: Deadline eq ‘2024-10-03’
      Try using: Deadline eq ‘2024-10-03T00:00:00Z’

      If the issue persists, please double-check your query syntax. Let me know if you need further assistance!

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo