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.
Send Email with Entity Attachment from Notes Header
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.
Attachment Entities


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.
Send Email with Entity Attachment from Notes




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)

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. BodyFile 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.

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.

Comments

  1. you are a god send!!! thanks so much!!

    ReplyDelete
  2. Hey hi..
    Thanks for the blog, it is really helpful. But in activitymimeattachment create step ,if we put Null it is throwing error after run.
    An error occurred while validating input parameters: Microsoft.OData.ODataException: The 'odata.bind' instance or property annotation has a null value. In OData, the 'odata.bind' instance or property annotation must have a non-null string value.
    Any idea how to overcome this ?
    TIA

    ReplyDelete
    Replies
    1. Understand now,it should create using CDS.
      Thanks

      Delete
    2. I am glad you have figured out the issue. I have updated the blog to highlight that we will need to use CDS Standard connector for that step 5.

      Delete
  3. Hi Linn - this worked a treat for me yesterday, however today I keep getting an error message on step 6 above where it is complaining about "Invalid character in field 'body': '\u0014', hexadecimal value 0x14, is an invalid character" - I've tried with both a Word and PDF attachment, using the exact same records that were working yesterday - I was wondering if you have encountered any issues with your flow recently? just curious if MS have updated something....

    Thanks!

    ReplyDelete
    Replies
    1. Don't worry - figured this out - for some reason (that wasn't happening previously) I had to convert the output of the file to base64 before using it as the Body of the attachment - thank you so much for this post!

      Delete
  4. Hello Linn, I've created this as you have in your instructions (thank you so much by the way!), however even after following the specific instructions I'm still getting the error {"error":{"code":"0x80040800","message":"The 'Update' method does not support entities of type 'attachment'."

    I can provide the entire error message and a screen shot of my flow setup if you'd like, I don't think I can do that as a comment here though. Any tips?

    ReplyDelete
    Replies
    1. The following line mentioned in the Step 5 also applies to the step 6 too.
      "Select the first one out of x2 "Attachments" in the Entity Name list for Attachment (ActivityMimeAttachment) entity."

      I also added the preview image in the blog post at step 6.
      https://1.bp.blogspot.com/-vq8JEEmu3TU/XyFbhfXLaMI/AAAAAAABsI0/W6DLAJKqUOwoOJt0XZ6gAgb2vLWxOocIgCLcBGAsYHQ/s652/Update%2BAttachment%2BEntity.png

      Delete
    2. Thank you for your reply, I have tried using the first of the two Attachments entity, but the option for Body, Filename and and Mime Type are not available when I use that option. Do you know why this might be?

      This is what I have set up:
      Environment: current, Entity Name: Attachments (1st of 2), Record Identifier (assuming this is "Item ID" from your setup): Activity Mime Attachment. The other available fields are: Attachment Number, Entity Value, Attachment, Item, Item Type and Content Id. No option for Body, filename or Mime Type.

      Thoughts?

      Delete
    3. For the Update step, Common Data Service (current environment) connector needs to be used.
      If you use CDS (current environment) connector, you should not have the Environment option.

      Delete
    4. I think this is why I'm having a problem. When I add step "Update a record", I only have one option for Common Data Service connector, I would expect to have "Common Data Service" AND "Common Data Service (Current Environment)".

      When I go to "Connectors" I do see both. But when I am editing my flow, I only see Common Data Service option and not the Common Data Service (Current Environment) option. Do you have any tips about that? Very strange that I'm not seeing that inside my flow.
      Thank you!!

      Delete
    5. You must create solution-aware flows to use the Common Data Service (current environment) connector. (i.e. you need to create your flow inside the solution to see the CDS (current environment) connector options)

      Even if you hvae created a flow outside of the solution and added into the solution later, Common Data Service (current environment) action will still not be available. CDS (current environment) connector is only available for flows which are created originally as part of the solution.

      https://docs.microsoft.com/en-us/power-automate/connection-cds-native

      https://debajmecrm.com/2019/11/24/quick-tip-common-data-service-current-environment-connector-not-showing-up-while-creating-microsoft-flows-power-automate/

      Delete
    6. Thank you. I have discovered the problem, was able to import your solution (finally) and it's working. Thanks again for the help and this blog post, it's really taught me a lot about this type of Flow!

      Delete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Hi

    Thanks for

    I am new to Flows.

    I have a requirement to send an email on update of Lead owner, send an email. The sender must be fetch from queue's email address.

    However, I put it in the expression and says invalid expression:
    first(outputs('Get_user_for_email_to_come_from')?['body/value])?('queueid')

    Any thoughts why?

    Thank you very much

    I have tried to type in the expression you have above but it says that

    ReplyDelete
    Replies
    1. it is an invalid expression

      Delete
    2. Hi Katie

      You are missing a single quote after body/value and the square bracket [] need to be used to get the property ['queueid']

      Here is the updated expression
      first(outputs('Get_user_for_email_to_come_from')?['body/value'])?['queueid']

      Delete
    3. Thank you for mentioning it. Hope you'll continue to post :)

      Delete
  7. Thanks - this was a really useful page. I was able to get it working. Would have taken ages to figure this out without this page!

    ReplyDelete
  8. Once again, you have saved the day! Super post. I would not have delivered my project without this information. Many thanks.

    ReplyDelete
  9. You've just done all my work for me! Thank you once again Linn. You're a legend as always! :)

    ReplyDelete
  10. AMAZING!! you just saved me from madness ;) BTW the editor didn't recognized the 'undefined()' that you put as an empty string in the 'Attachments' field. I just put a 'null' expression and it did the trick. Thanks!✨

    ReplyDelete
    Replies
    1. Thanks for your feedback. I guess Microsoft has updated the Dataverse connector to accept null values in the lookup field. I have updated the blog post.

      Delete
  11. G'day Linn,

    Love the Post thank you!

    Is there a way I can use an email template with this flow. E.g. where the subject and Description are both filled out from an email template within dynamics?

    Thanks

    ReplyDelete
    Replies
    1. You can use the SendTemplate unbound action to use an email template but you can't dynamically attach the files https://benitezhere.blogspot.com/2020/03/how-to-send-email-using-email-template-with-power-automate.html

      Delete
  12. Dear, I have a requirment to send customer statements, which are residing outside CRM (most probably in Azure blob storage or share point, how can we reteive those customer statements and send them in bulk email)

    ReplyDelete
    Replies
    1. That's possible. I have never done it for Azure Blob Storage but you should be able to use this Get blob content using path (V2) action in the Azure Blob Storage connector.
      https://docs.microsoft.com/en-us/connectors/azureblob/#get-blob-content-using-path-(v2)

      For SharePoint, you can use Get file content action in the SharePoint connector.
      https://docs.microsoft.com/en-us/connectors/sharepointonline/#get-file-content

      Here are some examples.
      https://docs.microsoft.com/en-us/microsoft-365/community/power-automate-send-sharepoint-files-as-attachments

      https://youtu.be/bGG_dTBK6a8

      Delete
  13. After numerous searches, I finally stumbled upon this post and found exactly what I needed. Thank you so much for sharing your knowledge and expertise!

    ReplyDelete
  14. Linn, this post is a couple years old, but I have been searching for a proper example of how to do an outgoing email notification in D365 using power automate (in other words, the type of email notification that you might have once generated using a classic workflow process in D365), but I have not found one that works for me. Seems like an unfulfilled need.

    The fact that you are using a QUEUE as the FROM party is interesting. However, I tried that and it gave me the following error: Could not find email address for recipient of type 'Queue' with ID 'ee633de6-4e3a-eb11-a813-000d3a591e88'.

    I had previously been setting the FROM to "systemusers(e7633de6-4e3a-eb11-a813-000d3a591e88)" and it accepts this, but I cannot say if that is correct because I still haven't gotten this to work completely.

    If Power Automate is intended to be "easy" for the average user, they have certainly missed the mark on this. I'm an experienced developer and I cannot seem to find the information on how to do this correctly.

    ReplyDelete

Post a Comment

Popular Posts