Send Weekly Summary Emails for New Leads

The Idea Behind!

Recently I receive the challenge from one of our salespeople if we can send automatically summary emails for a weekly created Lead. For example at the end of the week to receive an email of how many Leads have been created from Monday until Friday. (Same logic can be used for other entities as well, depending on the scenario).

This article is a mix of suggestions, forum topics, and reading combined together to achieve my scenario. Feel free to spread the news and even get back to me if you have ideas on how to develop it even further.

Since there isn't the box option in CRM to archive that, so I knew that my only option will be using somehow Power Automate (ex. Microsoft Flow).

This is how the overall of the Flow looks like.

So, let gets started.

• First of all we will set our Flow as Recurrence, so this will be our trigger point.

For a test purposes, I set the flow to start every 3 days at 09:00 AM, but I think in our scenario will need to be perhaps every Friday at 5 or 6 PM (after work hours), or maybe Monday morning? Anyway…..

• Next is to Get our Past Time, meaning "how long ago since the records were created in CRM"

Here we want all Leads created in the past 3 days.

Remember: The more days ago you set the Flow to get records, the longer will takes for the Flow to complete, it can even reach time out if there are too many records.

• Next steps required a little more context to understand the flow logic later in the instructions.

Array Variables in Action (do not ignore)

You see, when we receive the email with the Leads, Cases, or what so ever we want to extract from CRM, we will have no problems with the strings and date and time fields on the forms, however, if we also want to get information such as; who is the owner of the Lead, who is the Account or the Contact, these fields are Lookup type and they will only return the GUID (Global Unique Identifier) of the records in the following format.

That really doesn’t tell us much, unless if someone doesn’t remember the GUID of each record in CRM, then he can skip all of the steps and proceed to the table structure steps at the end of the Post, just kidding!

We want all these numbers and alphabets to be more user-friendly, or with the simple words we want to see the Names.

We need first to initialize so-called variables, variables store information, or values, in a program. There are different types of variables that are used in different situations: strings, integers, and arrays.

Strings are groups of characters, like words or phrases, and integers are whole numbers, like 5, 50, or 500. But what if we want to store multiple values in a variable? The solution is an Array!

• In our case, we are creating a Collection that will store all our Arrays.

Now, we need to get the list of all Leads in CRM, but since they will be quite a lot, we need to filter them somehow, and this is where we will use our Past Time that we've set early.

• We will add filter createdon ge formatDateTime(body('Get_past_time'),'yyyy-MM-dd')

So basically we are using format DateTime with Body function which refers to the trigger action (Get-Past_time) followed by the date format.

The Core of the flow

Next steps is actually the core of the flow and it is the most important one if you want to make the flow working!

First we need to specify the related entities for each lookup that we want to display.

• As I mentioned early if we want to get the Lead's Account, this will return the GUID of the Account, so we need to specify that for the Account field on the Leads form, behind there is a filed called Account Name that stores the string Name of the Account, which we actually want to see at the end.

This is how the configuration looks like for one of these Lookup fields.

• Basically, we are saying here, for each of the results from the previous action (List all Lead records)... I will rephrase that - for each one of the leads from the list, for Item Identifier (Parent Account for Lead) which is a Lookup field on the Lead form, look in the Account entity (and basically match the GUID with the Account behind)

I know this might look a little more technical, but it is important to get the logic, once you get it you will build more complex flows in no time.

• Repeat the same step, for all other Lookups on the Lead form such as Contact, Partner, or Product, etc.
• After you are done mapping the Lookup fields to the related Entities it is time to compose our table which will be sent by email.

Here I'm typing the name of the Filed that will be displayed in the Email and then which filed stored the information that will be displayed.

For example I've already mapped for the Lead that Parent Account for Lead is actually the Account Name, so the same mapping is set in the table as well.

Remember: the table will display the information from the related entities and not directly from the Lead entity as the fields there are referring only to GUID of the record but not the display name.

• Once done with mapping, we need to map the collected arrays with the mapped fields

Or Another way said, if we've collected a GUID for a Parent Account for Lead we now know which entity will tell us the Display Name behind that GUID, and this is Account Name (on Account Entity).

Before: (this is from List Lead Record Action)

"_parentcontactid_value": "dd3480b2-2d5a-ea11-a811-000d3abaad31",

"_parentcontactid_type": "contacts",

"_parentaccountid_value": "7bf30ec8-6c2d-ea11-a810-000d3aba7e84",

"_parentaccountid_type": "accounts",

"lastname": "bala",

"firstname": "ala",

After: (this is after the GUID are matched with the mapped fields)

• After that, we need to create our HTML table by getting the information from our Array Collection Step created early, actually "create", as we can leave the creation to MS flow by selecting Columns - Automatic.

Last but not least, we need to add the Send email function, which will send the HTML table in an email to us.

I'm using Gmail integration with MS Flow, but you can set it with your Outlook one (which is the most common setup).

In the email body, I have greetings and inside I've inserted the Output from the HTML table step.

The end result is this.

 Thank you and looking forward to any ideas to develop this further in the future.