Send Email from Dynamics 365/Microsoft Dataverse with Attachment from Notes Using Flow

Automation of email sending out from the system is a common requirement in Dynamics 365 applications or model-driven apps for Microsoft Dataverse. The requirements of automated emails can be an approval email to the case owner, reminder email to the task assignee, status update email to the customer, etc. In some scenarios, automated email requires to include attachments from the Notes (annotation) related to the record or SharePoint files from the document folder of the record.

Before I built my flow, I did a quick web search for the existing solutions. I found a few great tutorials on how to create an email with attachment in Microsoft Dataverse using Azure Function or HTTP with Azure AD. Those blog posts inspired me to build my version of it and find out if it can be done with just Microsoft Dataverse connector.

Problem: There are two Attachment entities in Dynamics 365 and Microsoft Dataverse connector shows x2 "Attachments" in the entity selection list. When the entity name is chosen from the list of Entities in the Microsoft Dataverse connector, the first one is ActivityMimeAttachment and the second one is Attachment. Those who are familiar with creating email attachments in Dynamics 365 using SDK know that it needs to be created using ActivityMimeAttachment entity. But the problem with creating ActivityMimeAttachment is that the lookup field to Attachment entity is mandatory and the system would not let to save the record without that value. Then, I attempted to create an Attachment entity and populate that lookup but the flow failed with the following error.

The 'Create' method does not support entities of type 'attachment'.

To resolve those issues, I had to come up with a workaround solution as mentioned in UPDATED: steps 5 and 6 using both Microsoft Dataverse connector and my other blog post about how to set lookup fields with Null value from dynamic content in Microsoft Dataverse connector.

In this blog post, you will learn how to achieve the following using the automated flow from Power Automate.

• retrieve the files attached to the entity in Notes (annotation)
• retrieve the queue to set as an email sender
• adding attachments to the email entity
• send email from Dynamics 365/Microsoft Dataverse using bound action

The Use Case: Case office generates a successful letter and attaches as a note to the case entity. Then, the case status is updated to Approved and the Application Successful email is automatically sent out to the customer together with the successful letter attachment.

Sample Flow: The following sample flow is the simplified version of to above real-life scenario which is built with just out-of-the-box Microsoft Dataverse tables (without Case table). The sample flow triggers when the Contact record is assigned and it emails to the new owner of the record with the note attachments of the record.

This is the summary of the solution and the detailed explanation of the flow steps can be found below.

1. Trigger the flow
2. Retrieve Notes with attachment related to the entity
3. Get the sender mailbox queue
4. Create a draft Email record
5. Create an empty Attachment record
6. Update the Attachment record from the step above with file content from the Note attachment
7. Send out the Email from the system

1. Trigger the flow

The flow triggers when the Contact record is assigned (the owner field is updated)

2. Retrieve Notes with attachment related to the entity

This step retrieves the Note records of the Contact entity with attachments (isdocument eq true)

Only 3 fields are required to be used in the later steps. (filename,mimetype,documentbody)

3. Get the sender mailbox queue

This step is just to retrieve the queue to set as the email sender From field. (the mailbox from which the email is to be sent out)

4. Create a draft Email record

In this step, a draft Email record is created to add attachments.

• The Queue record from step 3 as a sender in From Activity Party. Assuming there is only one queue with that mailbox name is specified, the first() function is used to get the only record in the entity collection. (first(outputs('List_Mailbox_Queue')?['body/value'])?['queueid'])
• The new owner User as a recipient in To Activity Party.
• The triggering Contact record in the Regarding field.
• And some value in Subject and Description (email body)

5. Create an empty Attachment record

UPDATED: After writing my blog post about How to Set Lookup Fields with Null Value from Dynamic Content in Microsoft Dataverse Connector, I found that Microsoft Dataverse (legacy) connector is no longer required to create the empty Attachment record. The blog post has been updated to combine both step 5 and 6 as once step using Microsoft Dataverse connector.

Once the draft Email is created, the next step is to loop through the Note attachments from step 2 and create an Attachment (ActivityMimeAttachment) record.

In the Entity Name list, make sure the correct Attachments (activitymimeattachments) Entity is selected. (the first one out of x2 "Attachments" in the Entity Name list for Attachment entity). In order to see the entity logical names in the selection, enable the Experimental Features.

As I mentioned in the problem statement at the beginning of the post, the Attachment field is mandatory for Attachment (ActivityMimeAttachment)  record. Since the Microsoft Dataverse connector cannot be used to set the null value to the lookup field, The null or empty string '' can be used to set the lookup field with an empty value.

🛈 Important 

The value of Attachment (Attachments) needs to be empty string '' to allow the system to generate the attachment GUID and the value for Entity needs to be custom string value 'email' instead of selecting from the drop-down.

In the input property for Entity, choose "Enter custom value" and type "email". Selecting "Email Activity" from the list will populate the integer Object Type Code 4200 and the create step will throw and error.

The entity with a name = '4200' with namemapping = 'Logical' was not found in the MetadataCache.

In Item (Email Messages) field, enter emails(GUID) with the Email Message unique identifier from the Email record created in step 4 in place of GUID. Body, File Name and Mime Type fields need to be populated from the retrieved Note entity as in the screenshot above.

🛈 Important 

This step needs to be created using the standard Microsoft Dataverse (legacy) connector to set NULL value to the lookup field. NOT Microsoft Dataverse connector.

Once the draft Email is created, the next step is to loop through the Note attachments from step 2 and create an Attachment (ActivityMimeAttachment) record.

As I mentioned in the problem statement at the beginning of the post, the Attachment field is mandatory for Attachment (ActivityMimeAttachment)  record. Since the Microsoft Dataverse connector cannot be used to set the null value to the lookup field, the Create action of Microsoft Dataverse (legacy) connector needs to be used.

Select the first one out of x2 "Attachments" in the Entity Name list for Attachment (ActivityMimeAttachment) entity.

Set "null" value using Expression for Attachment field.

Enter custom value "email" (not integer Object Type Code) in Entity Value field, select "emails" in Item Type and the Email Message unique identifier from the Email record created in step 4.

The problem with Attachment (ActivityMimeAttachment) entity Microsoft Dataverse (legacy) connector is not being able to set the actual attachment file information (such as file name, content of the attachment, Mime type). That is why there is another step to update with the file information.

6. Update the Attachment record from the step above with file content from the Note attachment

This step is to set the actual attachment file information to the Attachment (ActivityMimeAttachment) record created in step 5.

For this Update step, Microsoft Dataverse connector needs to be used.

With the Microsoft Dataverse connector, Update action of Attachment (ActivityMimeAttachment) will show additional file information fields and populate Body, File Name and Mime Type fields need to be populated from the retrieved Note entity as in the screenshot above.

7. Send out the Email from the system

Once all of the attachments are created, the last step is to send out the draft Email using Perform a bound action step from Microsoft Dataverse connector.

When the Email Messages is selected for Entity name, "SendEmail" Action will be available for Action Name.

Populate the Email Message unique identifier from the Email record created in step 4 into Item ID and IssueSend needs to be set as Yes. (then, the system will update the status of the Email record to "Pending Send" and the server-side synchronisation will pick up the email.

If the IssueSend is set as No, the email status will be updated to "Sent" and the email will be just created as a sent email. (but not actually delivered out of the mailbox)

Summary

By using an approach to set the empty string '' to the Attachment lookup field in combination of the Microsoft Dataverse (legacy) connector and Microsoft Dataverse connector, we can create the Attachment (ActivityMimeAttachment) for the Email record in Dynamics 365/Microsoft Dataverse.

You can download the above sample flow from my GitHub repository via this link.