How to send automatic follow-up emails in Outlook when nobody replies

Create a workbook in OneDrive for Business (the video uses recipients.xlsx) with a table named Table1 and six columns:

• Name: used to personalize the email.
• Email: the contact's address. The flow also uses it to find replies, so it must be correct.
• First Contact Date: the day you sent the original email.
• Last Follow Up: leave empty. The flow fills it in.
• Follow Up Count: start at 0. The flow raises it to 1, 2 and 3.
• Status: set to Active. The flow changes it to Replied or Completed.

For each new contact you fill in five things: name, email, first contact date, count 0 and status Active. You can add rows at any time and the flow picks them up the next morning.

In Outlook, create a folder called Prospects (any name works). Then open Rules and add a new rule:

• Condition: From, and paste all your prospects' email addresses separated by commas.
• Action: Copy to the Prospects folder.
• Check Stop processing more rules.

This is for speed. The flow checks every contact for a reply, and searching one small folder is much faster than searching an inbox with thousands of emails. When you add a contact to Excel, add their address to this rule too.

In Power Automate, create a scheduled cloud flow. Set the Recurrence trigger to run every 1 day at a fixed time and pick your time zone. The video uses 9:30 AM. Choose a time when your recipients are likely to be reading email.

Add the Excel Online (Business) action List rows present in a table and select your file and table. Under Advanced parameters, turn on two settings:

• Filter Query: Status eq 'Active'. This OData filter returns only rows that still need follow-ups, so Replied and Completed contacts are skipped here and the flow stays fast.
• DateTime Format: ISO 8601. Without this, Excel dates come through as serial numbers such as 46066 that you cannot use in date expressions.

Add an Apply to each (For each) loop and select the value output of List rows present in a table. Everything below runs once per contact.

Inside the loop, add the Office 365 Outlook action Get emails (V3) with these settings:

• Folder: Prospects
• From: the Email column from dynamic content
• Top: 1. You only need to know if at least one reply exists.
• Fetch Only Unread Messages: No. You may already have read the reply, and it still needs to count.

Add a Condition. On the left, enter this expression:

length(body('Get_emails_(V3)')?['value'])

Set the operator to is greater than and the right side to 0. The value property is the list of emails found, and length counts them. One or more means the person replied (true). Zero means no reply (false).

In the True branch, add Update a row (Excel Online (Business)). Use the same file and table, set Key Column to Email, the key value to the Email column from dynamic content, and set Status to Replied. Because the filter only reads Active rows, this contact is skipped from the next run onwards.

In the False branch, add a Compose action named Reference Date with this expression:

if(equals(int(items('For_each')?['Follow Up Count']),0),items('For_each')?['First Contact Date'],items('For_each')?['Last Follow Up'])

If no follow-up has been sent yet, the flow measures from the first contact date. Otherwise it measures from the last follow-up, because each waiting period starts from the most recent email.

Add a Compose named Days Elapsed:

div(sub(ticks(utcNow()),ticks(outputs('Reference_Date'))),864000000000)

Power Automate has no function to subtract one date from another. ticks turns a date into a position on a very long ruler that starts on January 1 of year 0001, where each mark is 100 nanoseconds. Subtract the two positions, then divide by 864000000000, the number of ticks in one day (10,000,000 per second x 60 x 60 x 24). If the reference date is February 14 and today is February 21, the result is 7.

Add a Compose named Required Interval with a nested if:

if(equals(int(items('For_each')?['Follow Up Count']),0),3,if(equals(int(items('For_each')?['Follow Up Count']),1),7,if(equals(int(items('For_each')?['Follow Up Count']),2),14,999)))

Count 0 waits 3 days, count 1 waits 7 days, count 2 waits 14 days. Anything else returns 999, a safety net that can never be reached, so the flow can never keep sending emails. Change 3, 7 and 14 to suit your business.

Add a second Condition. On the left, pick the output of Days Elapsed from dynamic content. Set the operator to is greater than or equal to, and on the right pick the output of Required Interval.

Use greater than or equal, not equal. The flow runs once a day, so if a day is missed (for example during a service outage) the email still goes out on the next run. Leave the False branch empty. The contact is simply checked again tomorrow.

In the True branch, add a Switch with On set to:

int(items('For_each')?['Follow Up Count'])

Add cases 0, 1 and 2, and in each one add Send an email (V2) (Office 365 Outlook). Put the Email column in To and use Name in the subject and body for personalization. Case 0 is a friendly check-in, case 1 a gentle nudge, case 2 the final reach-out. Write the text in your own voice.

After the Switch, add Update a row with Email as the key column and set three fields:

• Follow Up Count: add(int(items('For_each')?['Follow Up Count']),1)
• Last Follow Up: utcNow(). This writes an ISO date, the same format the List rows action reads.
• Status: if(equals(int(items('For_each')?['Follow Up Count']),2),'Completed','Active')

The status check looks for 2, not 3, because both fields are written in the same action and the count you read is still the old value. Count 2 means the third and final follow-up just went out.

Select Test, choose manual, and run the flow. Check your sent items and the Excel table: rows that were due now show a count of 1 and today's date.

A contact added on February 4 gets follow-up 1 on February 7, follow-up 2 on February 14 and follow-up 3 on February 28, and then the status becomes Completed. If they reply at any point, the status becomes Replied and nothing else is sent.