Take keywords and the attachment from an email sent to a Teams channel and add the keywords to a List and a clickable link to the attachment that has been saved with the email as it was sent to the channel

[KarenFen1]

Hi,

I'm still relatively new to Power Automate but I work in a team where I help users with their queries.

A team of users would like to:

• send an email with an attachment and related information to a Teams channel which would trigger a flow that...
• takes that information, adds a new item to a list taking the related information and adding it to the relevant columns,
• creates a clickable link to the attachment which is now saved in SharePoint (automatically as the attachment will have been saved when the email was sent to the channel).

I have been working on this for some time now.

So far I have:

Trigger

• When a new email arrives (V3) in Sent items addressed to the channel

Actions

• HTML to text (Preview)
• Create list item using the Split to populate the list columns with the correct data

I've been struggling with getting that file link into the list and have tried:

1. Adding the Create file action before the Create item action to save the file in a different SharePoint folder, so it is separate from its email before I create the list item.  In doing so, then getting the file's properties and using Link to item to get the link to the file.  This makes the flow hang. I thought this was because I now have 2 item IDs,  for the list item and 1 for the file, although I thought I was being careful in where I selected the link to item from.  I can't seem to use Link to file.
   
   
2. Adding the Create file action before the Create item action to save the file in a different SharePoint folder, so it is separate from its email before I create the list item.  In doing so, then getting the file's properties and then composing an output which got the link to the item (file) and then using that in the list.  That didn't work either.
   
   
3. Adding the Create file action before the Create item action to save the file in a different SharePoint folder, so it is separate from its email before I create the list item.  In doing so, then getting the file's properties and using Create sharing link for a file or folder and populating the hyperlinked field with that.  Of course that works, but it does so inappropriately as it then opens access to the file across the organisation.  Not what is required.  We require that only those with existing access can open the file.  I know you could argue that only those with access could work with the list where the link is, but there is nothing stopping anyone copying that share link and sharing it elsewhere. 

Any ideas would be welcomed. The screenshots I have attached have it working with the inappropriate share link in a test team.  Of course I'd like to get to the URL without sharing with extra people and if possible, without adding the step to create the file in addition to the attachment which is naturally saved with the email.  I've collapsed the Create file bit of the flow as it simply takes the file attachment and saves it to the channel folder with the attachment's filename and content.

Thank you very much.

[v-jefferni]

Hi @KarenFen1 ,

If using Link to item, this link can only be used for DOWNLOAD the file. If you want users to open the file on browser, the sharing link is necessary. 

For you concern in scenario3, you can see you have already shared the file with "People in your organization", so, no one outside your org will be able to open the file use this link, just like your org users copy the file link from their browser then send to others without permission.

Best regards,

[KarenFen1]

Hi @v-jefferni ,

Thank you so much for your response. I didn't know that Link to item can only be used to Download the file.  That is good to know and now makes a lot of sense with other flows. 😊

I agree.  Outside of this scenario, for any file, users can copy the file's URL and share it by pasting it elsewhere; but if I'm right, unless they actively either click:

• Share,
• Copy Link and change permissions,
• Manage Access,

the link wouldn't work for those not in the team, and owners could even stop members sharing the file, so they couldn't do those things effectively - but owners could allow access requests to grant it where required. 

However, in our situation, in the flow, creating a share link open to People in your organisation opens the file up to anyone in the organisation who has the URL, without the need to go into Share or Manage Access.  The URL generated by the link (which would appear in the list) is different to the file's URL if it were to be opened in a browser directly from SharePoint or Teams - it needs to be as it allows different access.  

So, if we had no link in the list, there were no sharing links to the item, and users were copying and pasting the file's URL, that link wouldn't work unless the person they've sent it to is in one of the membership groups. Please do correct me if I'm wrong.  I'm happy to learn.

I worry that by creating this file link open to everyone in the organisation, it will either:

• clash with the settings that members can't share the file (if they are set).
• override the SharePoint permissions and allow users to share the linked file openly across the organisation by copying and pasting the link.

I hope that makes sense.

Thanks again.

Karen

[v-jefferni]

Hi @KarenFen1 ,

I understand you situation now, and you're totally correct. According to your requirement, I managed to create the URL of any files, could be used to open files on browser.

So, please use Get file metadata instead, to get Etag in the first Compose:

first(split(last(split(outputs('Get_file_metadata')?['body/ETag'],'{')),'}'))

Then open any file and copy the URL, paste in Compose 2, place Compose1 output in it just like I did.

At last, use Compose2 in Create item action

Best regards,

[KarenFen1]

Hi @v-jefferni 

Thank you.  I'm sorry I wasn't as clear to start with and you've given me hope that I can do this.   I've used compose before but not with this formula.  At one point, I tried getting the File's metadata but didn't know what to do with it.

I'll try your solution after lunch.  If I get stuck with the syntax, am I ok to reply again?  In interested in the 7B and the 7D.  I'm guessing that is giving appropriate access?

Many thanks,

Karen

[KarenFen1]

Hi @v-jefferni,

I've fallen at the first hurdle.  I'm not seeing identifier as a Dynamic content option.  Not sure why as I have seen it on previous versions of my flow.

My options from Get file metadata are:

• ItemId
• Size
• Id
• Name
• DisplayName
• Path
• LastModified
• MediaType
• ETag
• FileLocator
• IsFolder

It has wrapped an Apply to each around the create file bit.  I guess this is because potentially there could be several attachments, although in reality, there will only be the 1 per email.  Again, I could be wrong. Either way, could this be limiting the fields I have access to in the Dynamic content box?

While I'm thinking about this, I first started this flow with an additional trigger.  When a file is created in a folder (deprecated) and I tried again with When an item is created.  However, when the email lands in the channel, the email saves as a file with the attachment in a folder named with the date of the email and I've not found a way for it to find only the attachment - except that perhaps the file type will always NOT be an email - but I'd need help with adding that condition.

So I've made the assumption that I had to include a Create file action from the attachment to the email, so it knew which file to work with for the URL. Now though, we have the file twice and of course, we have the Apply to each. 

I wonder if there is an option where it can find the attachment (saved in its channel) without having to save a duplicate copy, and then grab its URL as a link?  Might that make the Identifier content appear?  It would be a better solution in that we would have only 1 file in the system too.

Thanks again.

Best wishes,

Karen

[KarenFen1]

Hi @v-jefferni ,

I've saved a copy of my flow and been experimenting thinking about what I typed in my last reply.  What I've tried may be odd, the order may be wrong, but I've had a go.  It hasn't worked - but I did see identifier this time.  It hangs on when a file is created, (properties only) and the flow then times out.

I started as usual - when a new email arrives - in the Sent items with the channel email address, did the HTML to text and I then thought I'd get it to look for when a file is created in the channel - with the condition, it should not contain .eml - I've even cancelled the test and re-entered the condition to put "" so it looks for files that do not contain ".eml".

Then in the If yes box, I got the file's metadata, composed the first output and then the second one.

After that, I then went on to create the list item.

The flow showed no errors before I tested it and I understood what you said about composing the 2 outputs (thank you, I'd never have got there).

So I think I'm making progress, but any further guidance would be gratefully received.

By the way, went back to the previous version of the flow and I'm still not offered the identifier content.

Thanks very much.

Karen

[v-jefferni · Accepted solution]

Hi @KarenFen1 ,

I'm so sorry that I have lost an action. After Create file, there need to be a Get file properties, then use the output Identifier for Get file metadata:

%7B means "{" and %7D means "}" in URL. It's unimportant, the important thing is you can copy the file URL after open the file from library on browser, the URLs include the output of Compose in my screenshot. 

Best regards,

[KarenFen1]

Hi @v-jefferni ,

That works.  Thank you so much.  I will mark that as the solution.  I wonder if it is possible to take it a couple of steps further though.

At the moment, the email is sent to a Teams channel (they want the email in the channel).  As such, the email and its attachment is saved in the Shared Documents folder for that channel. 

My flow then re-creates the document in a different folder in that channel.  So we now have the same file twice.  I did this as I thought it was the only way to identify the file get the metadata from it. 

The flow (which works perfectly thank you) picks up the keywords from the email and the url of the newly created document.

Scenario 1

Is it possible to do all of that, but without recreating the document, so it picks up the document as it emailed to and is therefore added to the channel? I would want it to take attachments only and not the .eml document that it saves with it.

Scenario 2

When the URL is clicked, it opens in the Desktop version of its native app.

Would  it be best for me to mark this as solved and start a new post, or can we elaborate from here?

Many thanks,

Karen

[v-jefferni]

Hi @KarenFen1 ,

I have no idea about how it works between Outlook and Teams Channel, you'd better post a new issue in the forum and ask for help to someone may have similar scenario. Maybe it's better of you to post in Teams/SharePoint community as well, asking how to trigger a flow when Teams channel receive an Email and get files URL in SharePoint. 

Best regards,

[KarenFen1]

Hi @v-jefferni ,

I will do.  Thank you so much.  You've helped me a great deal.  I'll accept the solution and create a new post.  I hope this thread helps many others too.  I can't believe how long I'd been working on this before I posted!  I wish I had posted earlier.  I will do next time.

Thanks again.

Best wishes,

Karen