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

Last updated: Dec 19, 2023

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.

Generating Automatic Email Alerts in Excel: 2 Easy Ways

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.

Download Practice Workbook

To practice by yourself, download the following workbook.

Automatic Email Alerts.xlsm

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. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.

<< Go Back to Alerts in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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

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

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

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

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

11 Comments

Dennis Jan 11, 2024 at 2:50 PM

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

Junaed-Ar-Rahman Jan 16, 2024 at 3:31 PM

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:

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

isaac chavez Mar 28, 2024 at 9:41 PM

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 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
isaac Apr 2, 2024 at 10:06 PM

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 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:
  Press ALt+F11, click on Insert followed by Module.
  
  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
  
  Later, right-click on ThisWorkbook under the VBA Project section and click on View Code.
  
  Paste the following code in the workbook module and save it:
  Private Sub Workbook_Open() Call Module1.SendEmails End Sub
  
  Now, open the Task Scheduler and close the Excel file.
  
  In the Task Scheduler window, click on Create Basic Task.
  
  In the Create Basic Task Wizard:
  
  >> Name the task and add descriptions.
  
  >> Click on Next.
  
  Now, choose Task Trigger as daily.
  
  Select the desired date and time and click on Next.
  
  At this time, choose Action as Start a program.
  
  Browse to choose EXCEL.EXE.
  
  Insert the intended Excel file path in the Add arguments section.
  
  Finally, click on Finish.
  
  Now, wait until the start time is reached, previously set within Task Scheduler.
  
  When it is time, the Excel File will open.
  
  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
  - Reply
    Greg Apr 11, 2024 at 10:45 PM
    
    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…

Greg Apr 10, 2024 at 2:40 AM

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!!

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:

I have attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOK

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

Lukas Cejnar Apr 19, 2024 at 7:14 PM

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

Shamima Sultana Apr 21, 2024 at 9:06 AM

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.

Regards
ExcelDemy