Generate Document From Word Template and Send As Email Attachment Using Cloud Flow

With Word templates in Dynamics 365 or a model-driven app, users can generate and download standardised documents automatically populated with data from a particular row. There is also a process action called “SetWordTemplate” that will attach the generated document from a word template as a note attachment on a record. The user can download the generated document, attach it to the email and send it out from the system.


In this blog post, you will learn how you can automate the process above by generating a document from a Word Template and sending it as an email attachment using Power Automate cloud flow. This solution can be used when the system needs to automatically send out the pre-populated document on a specific trigger. (e.g., automatically send an in-principle approval letter to the applicant once the case status is changed to Approved)

I presented this solution at the Auckland Power Platform User Group and if you prefer to watch the demo, you can check out the recording on YouTube.

There are other different ways to achieve the same outcome but different ways have different pros/cons. You can use the Word Online (Business) connector to populate a Microsoft Word template but the flow needs to be updated if there is any addition/removal of the fields on the template. By using the Word Templates in Dataverse, the user can manage/modify the Word Template without any change to the cloud flow. You can read more about creating Word Templates in Microsoft Learn.

There are some prerequisites for this solution.
  • The table needs to be enabled for Business Process Flows (BPF) or else, the table cannot be selected in the Target parameter of SetWordTemplate Action 
  • The table needs to be enabled for Attachments (including notes and files) or else, the SetWordTemplate action will be failed with the “Creating Entity with an invalid parent.” error
  • The table needs to be enabled for Activity or else, the table cannot be set as Regarding value in an Email

The solution involves x3 components.
  1. Environment variable to store the name of the Word Template
  2. Process action to call the out-of-the-box SetWordTemplate action
  3. Cloud flow to call the action above and send the generated document as an email attachment
1. The environment variable is just Text data type variable to store the name of the Word Template which will be used in the cloud flow to pick up the correct template.

2. Action to call the out-of-the-box SetWordTemplate action is a global (unbound) action with an Entity Reference type mandatory input parameter for Template and one Entity Reference type optional input parameter for each table (from which the documents are going to be autogenerated).

For each table, add a condition to check if the input parameter contains data and populate into the Target parameter of the SetWordTemplate action.



3. These are all the steps included in the cloud flow which calls the custom Set Word Template action and send it as an email attachment.
  1. Trigger and initialise variable
  2. Populate Word template
  3. Get Note attachment content
  4. Send Email with attachment
  5. Delete a Note attachment


1. Trigger and initialise variable
The trigger can be anything depending on your business requirement. In this example, it triggers when the Status Reason (statuscode) is updated to Approved or Rejected. Four variables are initialised in the beginning of the flow. Note ID is to keep the GUID of Note auto-generated by the custom Set Word Template action to delete the Note at the end of the flow. File Name, Mime Type and Document Body variables are used to keep the values of the attachment file from Note and set it to email attachment.


2. Populate Word template
Depending on the value, different Word templates can be used to generate the document. In this example, the Approval Letter Template is used when the Status Reason of the Application is Approved. The GUID of the Document Template can be retrieved by the List Rows action where the name of the template is same as the value in Approval Letter Template environment variable.

To call the custom Set Word Template action from the flow, we need to use the method mentioned in this blog post to set the values of the EntityReference (Lookup) parameters as a JSON object. To make sure that the "Perform an unbound action" step is rendered as a generic steps, I set the Action Name as below.
trim('lzw_SetWordTemplate')
The GUID of the Document Template is the first object from the List rows step above (which can be specified using the expression below. The Application GUID is part of the trigger output.
first(outputs('List_Document_Template_-_Application_Approval_Letter')?['body/value'])?['documenttemplateid']


3. Get Note attachment content
After the custom Set Word Template action, the populated document will be created as a Note attachment related to a record (Application in this example). The next step is to retrieve the content of the Note attachment and set to the variables. The List rows action will return an array, so the values of the first object can be retrieved by using the expressions below.
Note ID
first(outputs('List_Notes_-_Last_Created_for_Contact')?['body/value'])?['annotationid']
File Name
first(outputs('List_Notes_-_Last_Created_for_Contact')?['body/value'])?['filename']
Mime Type
first(outputs('List_Notes_-_Last_Created_for_Contact')?['body/value'])?['mimetype']
Document Body
first(outputs('List_Notes_-_Last_Created_for_Contact')?['body/value'])?['documentbody']


4. Send Email with attachment
The next step is to send an email with the attachment. The detailed information can be found in this blog post. For this example, a new email is created with
  • Queue (with hardcoded GUID) as a sender
  • Contact from the Application as a recipient and
  • the Application itself as Regarding of an Email.
Then, Activity MIME Attachment is created with "null" value for 
  • Attachment (Attachments)
  • custom text value "email" for Entity
  • Email GUID from the step above to Item (Email Messages)
  • Document Body, File Name, Mime Type variables to respective columns
After that, SendEmail bound action is called with the IssueSend = Yes.


5. Delete a Note attachment
The last step is to delete the Note attachment generated by the custom Set Word Template action.



If the requirement is to generate a PDF document (instead of Word document file) based on the Word template and send as an email, you can follow the instructions in this blog post to invoke an HTTP request to {dynamicsURL}/api/data/v9.0/ExportPdfDocument API. The Web API endpoint ExportPdfDocument is part of the Export sales records to PDF functionality, so it might not be available in the Dataverse environment where Dynamics 365 Sales module is not installed.

Comments

  1. Thank you for sharing this amazing post. Just wondering if possible to populate the email content from the Email Template table so the content will be dynamic including the personalization and it takes the attachment populated and we perform an action to send it.

    ReplyDelete
    Replies
    1. Hi Julien

      Is this what you are looking for?
      https://benitezhere.blogspot.com/2020/03/how-to-send-email-using-email-template-with-power-automate.html

      If you need to attach the attachment dynamically in the cloud flow, please use the InstantiateTemplate Action (instead of SendTemplate) to create the draft email, create the attachment and perform a SendEmail action to send it out.

      Delete
  2. Dear Linn Zaw Win,

    I followed the same steps that you described above. Unfortunately, the word document is not populating the data.
    Please check the screenshots in this website below.
    https://ibb.co/X4mfq3r
    https://ibb.co/2YpBBfm
    https://ibb.co/x3LdSpM

    Thank you

    ReplyDelete
    Replies
    1. Hi Ralph
      Sorry for the late reply. I just reviewed your screenshots and you are missing the step in your custom action to call the OOB SetWordTemplate action and pass through your Employee EntityReference value.
      https://www.screencast.com/t/IF1t9xSmN

      Basically, you custom action just accept the input parameters but does not do anything at the moment. Add that Action step in your custom action.

      Delete
  3. Hello Linn,

    I have a scenario using your flow steps to populate for example the last actual revenue from the "WON" Opportunity into the template automatically based on the Account. How can I add the actual revenue field and pass the related value after the query in the flow dynamically into the Word Template? Do I have to create an additional parameter in the custom action? Could you provide a simple screenshot of the idea?

    Your input is highly appreciated!

    ReplyDelete
    Replies
    1. Hi Julien

      In that case, you will need to "List Rows" for the Opportunity Close table, filter by statuscode eq 2 (Completed) and take the first() from the actualrevenue column. Then, you can use that dynamics value in the word template.

      Delete
    2. Hi Linn,

      Thank you for your prompt reply.

      I was able to filter and extract the value but I am still stuck how that dynamic value from the first() expression will be passed to the Word Template because from the XML Mapping Pane there is nothing to do.

      So mainly it is a custom parameter coming from the flow and embedded within the template. What should be adjusted to make it work? Can you provide a SS?

      In native populate word as template easily we can achieve it because it shows all params.

      Thank you!!

      Delete
    3. Hello Linn,

      Shall I add in the custom action the Opportunity as EntityReference and pass to the GUID of the first() expression from power automate? https://i.imgur.com/4Xx9VJC.png

      I believe I should split the conditions in the custom action (If OPP Contains Data instead of using AND operator for both) to be able to set the Target Parameter right?

      Please let me know your thoughts.

      Delete
    4. Hi Julien

      Sorry, I forgot that you were trying to use the SetWordTemplate custom action (and I got confused with Populate a Microsoft Word template action from Word Online connector).

      For SetWordTemplate custom action, you can only show the repeating data (1:N, N:1, and N:N relationships) in a table row, so either

      1) build your Word template based on the Opportunity Close table and populate the Opportunity with N:1 relationship in the mapping of the Word Template

      2) Create a column column in an Opportunity table for actual revenue, map it in the word template, populate it with the latest actual revenue upon Opportunity won in the cloud flow before calling the SetWordTemplate custom action.

      Delete
    5. Hi Linn,

      Thank you for your reply.

      The main requirement is for the user to decide on the account entity which templates he wants to generate. Assuming it is the last won opportunity flow so the record ID that will be passed to the custom action is the Account GUID. In the opportunity microsoft already provided us with the actual revenue field. However, we are still stuck on "How we pass the first() expression retrieved from the list rows to the SetWordTemplate custom action so that it populates the exact value from the cloud flow once the document is generated?"

      Delete
    6. Hi Julien

      In that case, you need to create the word template from the Opportunity table and add the fields from parent Account table. Then, pass the first() Opportunity GUID to the custom action as I mentioned in the forum thread.

      Delete

Post a Comment

Popular Posts