Earlier this week, I blogged about the solution I was going to upload to git, and, finally, it’s there now.
For all the details, please keep reading. However, once and if you wanted to have a look at the solution itself, here is a link:
https://github.com/ashlega/SimpleSolutions
In the first version, there are only a few components in this solution:
There is another one that covers the sales part, at least to some extent, but it would need to be cleaned up first, so I will, hopefully, upload it some time later (not too soon, though).
Email table has been extended with a couple of additional columns to support email scheduling:
And there is a view that displays that column:
Other than that, it’s your usual “out of the box” email table.
In a nutshell, here is how this all works together:
Those Azure Functions mentioned on the diagram are important, since this is where most of the automation for email templates and scheduling will be happening. Also, although you may choose to do it differently (it would be a simple change in the functions code), in my case I opted for not using server-side email integrating for dispatching scheduled emails, and the reason for that is that I wanted to maintain control of when and how those emails are going to be dispatched. Why? Well, I am not 100% certain of how exchange spam policies work in some cases, and, since I kept running into them while working on this solution, I figured it might be better to do it this way.
When setting up the functions, you’ll need to configure a few parameters – here is my local.settings.json:
Why do I have smtpUserName and smtpPassword, yet I also have smtpFromEmail and smtpFromName?
This is because it’s all set up to use a shared mailbox when sending emails. Does not have to be that way (but you may need to adjust Azure Function code)
For SDK and SQL connection string, I’m using an application user:
“DataverseSqlConnection”: “Server=virtualchallenges.crm3.dynamics.com; Authentication=Active Directory Service Principal; Database=orgc8b2478d; User Id=f693b…; Password=UBl…”
“DataverseSdkConnection”: “Url=https://virtualchallen…;AuthType=ClientSecret;ClientId=f693…;ClientSecret=UBl…;RequireNewInstance=true”
As you can see, it’s the same application user in both cases.
Here is a quick overview of the azure functions project:
SdkClient.cs has a few helper methods to work with Dataverse using Xrm SDK
SqlClient.cs is very simple – it’s creating a SqlClient instance using provided connection string
MailConnector.cs is using SmtpClient to send emails
ProcessScheduler.cs takes care of creating emails for the active schedulers. If, somehow, it gets interrupted and/or fails, it won’t be creating the same emails again the next time it runs – it will be adding missing emails. Initially, the emails are created but not sent.
In turn, ProcessScheduledEmails.cs takes care of processing scheduled emails. In my case, corresponding Azure Function has been scheduled to run every hour, and that seems to work fine with 150 emails per run below:
Which means only 150 emails will be processed per run. It’s good enough for me, though, and the reason for doing it this way has to do with the outbound spam policies: https://docs.microsoft.com/en-us/microsoft-365/security/office-365-security/configure-the-outbound-spam-policy?view=o365-worldwide
I am not a security specialist, but, for what it’s worth, once your accounts starts running into those policies, you’ll start seeing this kind of notifications coming from Exchange to the admin account: “User …, has been detected as sending suspicious messages outside the organization and will be restricted if this activity continues“. Eventually, your account will get restricted, and we don’t want that to happen. Although, if it does happen, it can get un-restricted by the admin. That’s until the policy kicks in again.
By the way, if you do choose to use SMTP with Office 365, you may need to enable it first: https://docs.microsoft.com/en-us/exchange/clients-and-mobile-in-exchange-online/authenticated-client-smtp-submission
How does it work with the templates?
It’s all done in the TemplateProcessor.cs:
In short, that class is using HandlebarsDotNet library to process email subject and body template (which are stored in the Email Template table).
Right now TemplateProcessor class implements ProcessEmailTemplatesForSqlReader method, which will basically read data from the SqlDataReader, will group it by “email” column, and will apply email template to each group.
One specific advantage of using SQL over Fetch in this scenario is that we can add constants and/or SQL functions to the queries. In that way, I can add the same “email” (among other values) to different rows. I can also add other “common” data columns per email (contact name, for example?)
From there, I can this kind of syntax to access data from the first row in each email group:
{{this.rows.0.nextMonth}}
I can also use “each“, though, and it can be applied to “this.rows“.
All column values in the SqlDataReader output can be accessed by name in the template (see “nextMonth” above, for example).
Just to be a bit more specific, here is an example of a query I used in one of my own templates:
I still need to talk about “support requests” and server-side email synchronization a little more, but I’ll do it in the next post later.