Power Platform Community Forum Thread Details

Essentially, I want to create a defined term "newsletter" of sorts. When someone "subscribes" via MS Form, they trigger a scheduled sequence of pre-written emails over the course of a few weeks. Then, it concludes.

Any ideas? Maybe I'm missing something simple, but it seems that every "send email" action under the Automated Cloud Flow does not include any scheduling options, only immediate send options.

Here are the steps I'm envisioning:

Trigger: Microsoft Forms - when a new form is submitted

Action: Microsoft Forms - get response info (in this case, an email address)

Action: Outlook 365 - send an email (this could be an immediate response, like a "welcome" message)

Action: Outlook 365 -??? Send an email on the soonest Monday following their Form submission

Action: Outlook 365 -??? Send a 2nd email on the 2nd Monday following their Form submission

Action: Outlook 365 -??? Send a 3rd email on the 3rd Monday following their Form submission

Action: Outlook 365 -??? Send a 4th email on the 4th Monday following their Form submission

-done-

Is this possible? Thank you for any help you can provide!

Categories:

Business Process Flows

Hi @DS-02041556-0

Yes, as I mentioned I would leverage SharePoint.

Data is always good for Analytic boards etc., being able to track the emails success, form submission etc.
Excel is an ok choice, but as it is nice but it lacks in the ability to reflect changes quickly and a huge portion of the time requires people to but Delay actions in anywhere from 3-10 minutes. It is just not a solid thread safe multi-sync process which you are creating.

If you have many people submitting forms or heck even a small portion and you end up overlapping them its a pain with the Excel file. Excel is great for important data from and or doing things that only Power Automate does, but not with reliance on other things.

Here is how I would do it (Pseudo). just go slow and do everything step by step. Don't let it overwhelm you. I like to give specific supported steps. Each step is called what I wrote, so you can find them easily. if you get stuck at what to put, feel free to ask me.

Resources you will create

2 Tables

-To Store Email Templates

-To Create Email Events which will get sent in the future

2 Flows

-One triggered by your Form, and the one that creates the proper scheduled events that the second flow will run

-One scheduled flow to send your emails.

Data Schema (we can discuss more)

Email Events: This stores all the emails that you are or did send out and has the date configured for when it should go out. This also tracks which Template it used, whether it was successful or not to be sent

General Columns pretty self-explanatory, except Template ID, it is the SharePoint ID of the template to use here. this makes it easy to get the template and I do not recommend using a lookup here.

It also has a Date To Send and a DateTime sent so you know when to do it (day wise), but also you know WHEN it was sent.

Only store the Date, not the DateTime, in the [Date to Send] as it makes it easier to filter later

Template Week, is just a single INT, that tells us if it's a first week, 2nd week, 3rd week etc etc

Email Address, Description, Template Week, Date to Send, DateTime Sent, IsSuccessful

Storing our email templates

Email Templates: This stores an Email Template in a multi-line rich content column in SharePoint

Title <== build it in just unhide it, Description, Email Content, Email Week, ID <== this one is built in, just unhide it

Add Trigger: When a New Response is Submitted
Add a Get Response Details: and point it at the form in the Trigger
Add a Send Email V2: Let the user know that you received their submission and then tell them they will receive further instructions in the next few weeks every Monday. Or you can send this last, after you figured out the Dates, and you could put in a nice little HTML Table, with the Dates of communications. Either way works
Have 4 Create Items (SharePoint)
- 1st Monday
- 2nd Monday
- 3rd Monday
- 4rth Monday
- These are created

The last part is to Get the proper ID of the Email Template, from the Email Templates Row

This is easy. You can create 4 environment variables, to store the IDs of the proper Template.

This way you do not have to make a call to get it, it's just available to you, to use in the appropriate Week Email :-)

Technically that is it, your work is done on this Flow

We can add error handling etc but this gets you going

Remember your Creates will go to your Email Events table, and I will help you calculate the dates (in a moment)

FINAL NOTE: It would be possible to simply copy the Email Template, into a Column in the Email Events and save even that Get Item in Flow 2. That's a judgement call for you to make.

Flow 2 - Sends the Emails

Create a Reoccurring that runs every Monday at what like 6 AM or whatever time you want. No need for it to run any other day since its always on Monday
Create a Get Items (with a filter of Date To Send = formatDateTime(utcNow(), 'yyyy-MM-dd')
NOTE the Date Format we can work on. I wanted you to understand how we can easily filter on the current "Todays" date to grab ALL the Emails that need to go out for everyone
Add an Apply to Each
- inside the apply to each, you will do this
  - Get Item
    - This is where we use a call to SharePoint to get a Specific Item. We know the one to get, because it has
    - the Email Template that was assigned to the Emails for today (each row has its own template value, even though they all use the same template). this is just in case you every change your mind
  - Now you add a Send Email
    - The Sender Email would also be an Environment Variable, hard coding is not good. This way again you wont have to make any calls to another table
    - The subject you can make whatever
    - And the Body would be the Email Content that you grabbed in the Get Item
  - After you send, Do an Update Item on the Email Events
    - Do this on the Correct Week/Monday so that you track you send it and to whom. Great for validation and analytics
      - This is easy to figure out :-) as there is an expression we can use for that.
    - Set the IsSuccessful to true (again we can add error handling so we can put false also
Done

This is how I would do this. You could event connect the SharePoint list directly to Power BI for reporting, or setting alerts if you don't see an email when there should be etc.

Ok back - this will run starting today at 630 AM and runs every monday, each week, for a year

The Flow, again we can put error handling in, but these 2 flows make up the word you want

FINALLY: The only thing left would be for you to try to make it and come back to ask questions. This is a model I have used many times

There is a way to optimize it more and its built in to be able to do it, but from a teaching perspective, I wanted to do it this way. then I can explain how to further optimize the first flow.

I hope this helps

If this resolves your issue, I'd appreciate if you could Mark it as such and maybe a like.

Then if if you need help after, please feel free to come back for questions.

Thanks!!

Community site session details

Upon Trigger Event, Begin Scheduled Emails

Helpful resources

News and Announcements

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

Markus Franz – Community Spotlight

Kudos to the March Top 10 Community Stars!

Leaderboard

Featured topics

Product updates