scheduled flow + vf email template demo

I have built out a demo scheduled flow that sends an email to each opportunity owner with a list of open opportunities, using a visualforce email template. This flow is much like a report subscription, but with more context, and without nearly as much work of scheduling subscriptions to N users. And - no 5 subscription limit!

The basic approach is covered in this article - which was built on custom objects in our HR system.

In order to avoid hitting the flow iteration limit from building a unique collection of owner Ids (which I can do using this hackish but inefficient approach), I built an invocable apex class that takes a sobject collection and a field, and returns a unique collection of values. This could have some other interesting uses, and opens this flow up to run on up to about 1990 unique active owners, which is pretty robust by my standards…Above that value, you will hit the flow 2,000 iteration limit, though you may hit the cpu limit or some other limits before you get there…

You can test it out with the unmanaged package - there is a link towards the end of the article. I will briefly summarize the approach below, going from bottom to top :

vf email template –> vf component –> component controller –> flow

a) visualforce email template
the email template is related to the user object, as we will pass in opportunity owner Id. it has some text, and instantiates the visualforce component, which is where the magic happens.

<c:OppEmailComponent oppOwner="{!relatedTo.Id}"/>

b) visualforce component
the component accepts the ownerId from the email template, and passes it to the controller. the controller returns a list of opportunities, and formats the opportunities into a table. this is where you could modify which fields you display. you would first need to update the query in the controller to query those fields.

c) VF component controller
accepts the Id passed by the component, and uses it to query for open opportunities owned by that user. note that it is possible to use an apex:repeat directly in a visualforce email template without using a custom controller, but you cannot sort the records. so if a sorted list is better than a random list, use the controller.

d) the scheduled flow (install the package to see it)
The flow works as follows

The flow uses an invocable apex method to get the unique list of owner ids to avoid burning iterations, instead of this hack which does not scale as well.

The flow does not use the $record variable at all, but you must have at least one matching record for the scheduled flow to run. From my experience, no harm comes from having multiple matching records - the flow only runs once. But just in case that ever changes, its best to return a single record - so I recommend filtering on username so only a single user will be returned. I dont know that this is an intended use of scheduled flows, but that is why its fun!

Some important notes, and then a link to the package

  1. This flow and the demo visualforce component controller filters for all open opps. Its generally easiest to use a formula field on opp to flag the opps that you want to include in the report. Then you can update the query in the opportunityEmailComponentController to filter on that field, and update the filters on the getOpps query element in the flow to filter on that formula field.

  2. you have to use this hack to grant the automated process user profile access to the controller. hopefully that will change someday

  3. you must set the Automated Process User Email Address in process automation settings to allow the automated process user to send email alerts

  4. click debug to test the flow.

that should do it. install the package, click debug to test out the flow, but watch out - it will send an email to all opp owners!

you may want to update the email alert to hard code an email address for testing, instead of the user email.

here is a link to the package - use at your own risk.

full link :

this link will install in sandbox. you have to alter the link to install in prod and since you would never do that i wont even explain how :)

you may need to turn on opportunity historical trending to install the package - i have not been able to figure out how to remove that dependency, so apologies in advance.

here is an overview of the flow

for testing, i recommend ending the flow at the debug email, so you dont spam all opp owners in your org

and here is the email!


Now read this

Evaluating max/min of multiple date fields in flow

I have 3 date fields on an object and i need the MIN of the 3. But the MIN formula function does not allow dates, just numbers IF could work for up to 3, and there are some solutions out there using formulas, but seemed fragile /... Continue →