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
this is related the user object, as we will pass in the owner Id. has some text, and instantiates the component, which is where the magic happens.
b) visualforce component
accepts the Id from the email template, and passes it to the controller, which returns a list of opportunities. then 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 from the component, and uses is to query for open opportunities owned by that user. note that it is possible to use an
apex:repeat 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
- get all open opportunities
- extract a unique list of ownerIds
- remove inactive owners from the list
- loop over the list and send the visualforce email in the loop (yeah, i know…its in a loop)
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 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
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.
you have to use this hack to grant the automated process user profile access to the controller. hopefully that will change someday
you must set the
Automated Process User Email Addressin process automation settings to allow the automated process user to send email alerts
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.
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!