Sending out a confirmation email when a file is created in SharePoint (via email sent to teams)

[clemencschen]

Dear All,

First of all, happy new year!

I am a new user of Power Automate so please excuse my lack of knowledge 😉

I am trying to build the following flow:

Trigger: an employee sends a file via email to a Team using the channel email address (Microsoft Teams). As the email is received, both the message and the attachment are stored separately into “Email Messages” in the SharePoint linked to the Team’s channel.

My flow is sorting the message and its attachment based on their names.  I am using “When a file is created” (SharePoint) as a trigger.

Now I would like to add a new function: send back an email to the employee to confirm that the file was well-received.

To do so, I used the bloc Send an Email (V2) (Outlook) which is sending emails but only to me and not to the employee.

To correct that I tried adding the bloc “Get file properties” (SharePoint) but I am unsure about what to input in “Library Name” (I tried several options) but I get the error message “Not found”.

In the end, I don’t know if I made the right choices, but my goal is to send out this email to the employee 😉

I thank you very much for your help!

 This is my current flow. The flow is working very well until I add the last box [Send an email (V2)] which only sends emails back to me and not to the original senders.

[fchopo]

Hello @clemencschen 

When you upload a file in a Teams channel, it is stored in the "Shared Documents" library + channel name. Have a look at the following flow:

The use who uploaded the document is stored in the "Created by" column, so you can use it to send the email back.

Hope it helps!

Ferran

[clemencschen]

Thank you very much for your quick answer, I'll make some tests and come back to you! 🙂

[clemencschen]

Hi again @fchopo,

I tried the flow you indicated and it is failing when I test it, indicating "Bad Request" and 

"Parameter 'To' cannot be null or empty." 
I think the reason might be the following: when I check in Microsoft Team (or the related sharepoint), column "Created by" states "Sharepoint application" and not the user name of the person that actually sent the email.

Thank you for helping me! 🙂

[fchopo · Accepted solution]

Hello @clemencschen 

Instead of using the "when a file is created in SharePoint" trigger, probably you will have to use "When a new channel message is added", and check if it contains an email. This is because using the first trigger, you don't know who posted the email in Teams.

If you check the output of that trigger when an email is send to a teams channel, it shows the following output:

{
  "@odata.type": "#microsoft.graph.chatMessage",
  "etag": "1610393239378",
  "messageType": "message",
  "createdDateTime": "2021-01-11T19:27:19.378Z",
  "lastModifiedDateTime": "2021-01-11T19:27:19.378Z",
  "subject": "Test message",
  "importance": "normal",
  "locale": "en-us",
  "webUrl": "https://teams.microsoft.com/l/message/19%3Accda955f78b44e49ae80247a62592c4d%40thread.tacv2/1610393239378?groupId=6f6f6d39-cf95-4d4b-ad29-01ee4dcdd1dd&tenantId=9fef9207-da41-4072-a3a5-5f37f2c5de70&createdTime=1610393239378&parentMessageId=1610393239378",
  "id": "1610393239378",
  "from": {
    "user": {
      "id": "6f1fb271-c14e-4f7a-9184-0acf66857e52",
      "displayName": "Ferran Chopo Garcia",
      "userIdentityType": "aadUser"
    }
  },
  "body": {
    "contentType": "html",
    "content": "<div>\n\n<table style=\"padding-bottom:13px; border-width:0px; border-style:none\">\n<tbody>\n<tr valign=\"top\">\n<td>\n<table style=\"border-width:0px 0px 1px 0px; border-color:#deb7ca; border-style:none none dotted none\">\n<tbody>\n<tr valign=\"top\">\n<td style=\"padding-bottom:7px\">\n<table align=\"left\" style=\"padding-right:28px; border-width:0px; background-color:rgb(255,255,255); border-spacing:0px\">\n<tbody>\n<tr valign=\"top\">\n<td style=\"padding:0px\">\n<div style=\"padding-left:3px; font-size:14px; font-family:'Segoe UI','Segoe WP','Segoe UI WPC',Tahoma,Arial,sans-serif; color:rgb(102,102,102)\">\nFerran Chopo Garcia has shared a OneDrive for Business file with you. To view it, click the link below.\n</div></td>\n</tr>\n</tbody>\n</table>\n</td>\n</tr>\n<tr valign=\"top\">\n<td><a href=\"https://ferranchopodev-my.sharepoint.com/:x:/g/personal/ferran_ferranchopodev_onmicrosoft_com/EdoanqIM8vBOiSTJ4H9bWDcBuD53Z7pNHSylymV7oB1QAw\" target=\"_blank\">\n<table align=\"left\" style=\"padding-right:28px; padding-bottom:10px; border-width:0px; height:20px; background-color:rgb(255,255,255); border-spacing:0px\">\n<tbody>\n<tr valign=\"top\">\n<td style=\"padding:0px\">\n<div style=\"background-color:rgb(255,255,255); height:20px; width:20px; max-height:20px\">\n<a href=\"https://ferranchopodev-my.sharepoint.com/:x:/g/personal/ferran_ferranchopodev_onmicrosoft_com/EdoanqIM8vBOiSTJ4H9bWDcBuD53Z7pNHSylymV7oB1QAw\" target=\"_blank\"><img width=\"20\" src=\"https://r1.res.office365.com/owa/prem/images/dc-xlsx_20.png\" style=\"border:0px\"></a></div></td>\n<td>\n<div style=\"padding:0px 0px 0px 5px; font-size:14px; font-family:'Segoe UI','Segoe WP','Segoe UI WPC',Tahoma,Arial,sans-serif; color:rgb(0,114,198)\">\n<a href=\"https://ferranchopodev-my.sharepoint.com/:x:/g/personal/ferran_ferranchopodev_onmicrosoft_com/EdoanqIM8vBOiSTJ4H9bWDcBuD53Z7pNHSylymV7oB1QAw\" target=\"_blank\" style=\"text-decoration:none; margin:0px; font-size:14px; font-family:'Segoe UI','Segoe WP','Segoe UI WPC',Tahoma,Arial,sans-serif; color:rgb(0,114,198)\">100\n Sales Records.xlsx</a></div></td>\n<td width=\"0\" height=\"0\" style=\"display:none; visibility:hidden\"></td>\n</tr>\n</tbody>\n</table>\n</a></td>\n</tr>\n</tbody>\n</table>\n</td>\n</tr>\n</tbody>\n</table>\n\n</div>\n\n<div style=\"display:none; visibility:hidden\"></div>\n\n\n<div style=\"font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt\">\nThis is a test message with an attachment</div>"
  },
  "channelIdentity": {
    "teamId": "6f6f6d39-cf95-4d4b-ad29-01ee4dcdd1dd",
    "channelId": "19:ccda955f78b44e49ae80247a62592c4d@thread.tacv2"
  },
  "attachments": [],
  "mentions": [],
  "reactions": []
}

As you can see, there is some HTML code inside the message, so your flow could be something like this:

Hope it helps!

Ferran

[clemencschen]

It is working 🔆 thank you so much!

I decided to do two separate flows since this solution needed a new trigger.

And even with a second flows it still has the "confirmation email" function since as soon as the post is received in teams and know that is email was stored in sharepoint.

[clemencschen]

Hi again @fchopo ! Following up on the previous resolution a new issue appeared 😉

I intend to communicate the email address to users that are not in the team so that they can deliver some files to us. With users external to the team (but still in our organization), the solution does not work. 

As you can see below, for external users the output of the trigger does not specify a user.

Output when an outside user sends an email :

{
  "@odata.type": "#microsoft.graph.chatMessage",
  "etag": "1610621818635",
  "messageType": "message",
  "createdDateTime": "2021-01-14T10:56:58.635Z",
  "lastModifiedDateTime": "2021-01-14T10:56:58.635Z",
  "subject": "202101 flash test external",
  "importance": "normal",
  "locale": "en-us",
  "webUrl": "https://teams.microsoft.com/l/message/19%3A0f1d4c15f7824b58979de05a8042a05d%40thread.tacv2/1610621818635?groupId=e3ea7d35-b9e0-4045-8d1a-3f91352a3f8e&tenantId=4c1d9e0f-5c27-4228-a35a-de7b4083ff7b&createdTime=1610621818635&parentMessageId=1610621818635",
  "id": "1610621818635",
  "body": {
    "contentType": "html",
    "content": "<div dir=\"auto\">Test</div>"
  },
  "channelIdentity": {
    "teamId": "e3ea7d35-b9e0-4045-8d1a-3f91352a3f8e",
    "channelId": "19:0f1d4c15f7824b58979de05a8042a05d@thread.tacv2"
  },
  "attachments": [],
  "mentions": [],
  "reactions": []
}

As you can see, in this code (compared to the one you showed), the part "user" does not exist.
Yet when the message is displayed in teams it appears with the email address of the sender (even though, the sender belongs to our organization)

I am not sure there is a solution for that but I figures you might know one 😄
thanks again!

[fchopo]

Hello @clemencschen 

You could something like this:

1) Find the eml file stored in SharePoint using the subject field

2) Open the the eml file and look for the from field.

Although I'm afraid it won't be easy to do that (I've never tried).

Regards,

Ferran