Send an email to Share Point file owners if the file is expired

[andewmilorad]

Hello,

I am completely new to Power Automate and I'd appreciate your help much.

I need to build a flow that sends emails to SharePoint file owners whose files have expired. The email should send one email for all expired files and include a list of links to expired files.

I have managed to create a flow that sends separate emails, but don't know how to combine them into one email to reduce inbox clutter.

Also, is there a way to exclude certain owners from this flow?

I would appreciate a detailed instructions if anyone could help. 🙂

Thanks a ton!

[v-yetonggu-msft]

Hi @andewmilorad ,

Could you show me a complete screenshot of your SharePoint list?

Private information can be coded.

Who do you need to exclude? Please describe your needs in detail.

Best Regards,

Sunshine Gu

[andewmilorad]

Hello @v-yetonggu-msft - thank you for quick response! 🙂 

I need to automate sending emails to people in the "SME" column when the "expiry date" in their respective row has passed.

What's crucial is that, for example, if "Susan" in the table has multiple different documents, I don't want to send her 5 separate emails for each document. Instead, the automation should consolidate all the links to her expired documents into one email and send it.

However, I want to exclude 3 people listed as SME from receiving these emails across the entire SharePoint. For instance, in the example from the image, "Laszlo" shouldn't receive emails regarding this matter; in other words, I want to exclude him from this automation.

I would appreciate a detailed instructions if anyone could help. 

Thanks a ton!

[v-yetonggu-msft · Accepted solution]

Hi @andewmilorad ,

If your person column is a single-select person column, please see the following test.

In my scenario:

I don’t want to send expired document reminders to Bo Feng.

My SharePoint Library:

My Outlook Inbox:

I found that your 'Expiry Date' column name contains spaces. In the SharePoint list, if the column name contains special characters such as spaces, '/', '-', the display name and internal name of the column will be different, and in flow In both OData Filter and expressions, we need to refer to the internal name of the column.

Here is the link to the steps to view the internal name of the column.

🔍How to Find the Internal Name of a SharePoint Column – Ellis Karim's Blog

My Flow:

Expiry_x0020_Date lt '@{utcNow('yyyy-MM-dd')}'
item()?['SME/Email']
union(body('Select'),body('Select'))

If you want to exclude three people please use this expression in Filter array advanced mode:

@And(not(contains(item()?['SME/Email'], 'Prefix1')),not(contains(item()?['SME/Email'], 'Prefix2')),not(contains(item()?['SME/Email'], 'Prefix3')))

item()?['SME/Email']
item()?['{FilenameWithExtension}']
item()?['SME/DisplayName']
item()?['Expiry_x0020_Date']
replace(replace(replace(string(outputs('Compose')),'[',''),']',''),'"','')

Best Regards,

Sunshine Gu

[andewmilorad]

Hello @v-yetonggu-msf, thank you for the quick response! 🙂

I have few more questions: 
- How can I add a custom name for each SME at the beginning of the email? 

- How can I add a custom date "+14 days" for when they have to review the content?

- Is there any way for the email received by the SME to display as "Item Name," with a hyperlink directly to the document, creating a short URL?

Thank you! 

[andewmilorad]

Hello @v-yetonggu-msf, thank you for the quick response! 🙂

I have a few more questions:

• I have multiple SMEs, it prompts the following error: The 'inputs.parameters' of workflow operation 'Send_an_email_(V2)' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'emailMessage/To' is required to be of type 'String/email'. The runtime value '"email1SME.com,email2SME.com" to be converted doesn't have the expected format 'string/email'.
  Every individual SME should receive only email and document for himself and not other included in it - I hope you understand what the issue is here.
  
• How can I add a custom name for each SME at the beginning of the email?
• How can I add a custom date "+14 days" for when they have to review the content?

• Is there any way for the email received by the SME to display as "Item Name," with a hyperlink directly to the document, creating a short URL? 

Thanks a lot! 🙂 

 

[v-yetonggu-msft]

Hi  @andewmilorad ，

First of all, your email address must be a valid email address, not a string written by yourself.

For example, all the email addresses in my test examples are the email addresses of employees of our company.

As for the rest of your problem, you can re-open a thread and attach a more detailed description so that more people in the forum can see it. This is more effective than me solving it alone.

Thank you for your understanding.

-------------------------------------------------------------------------------------------------------------------------------

If I solve your problem, please mark my response as a solution so I can help more people who are experiencing this challenge in the future. 

Best Regards,

Sunshine Gu

[andewmilorad]

Hello @v-yetonggu-msft, 

Thank you for your response and guidance.

Just to clarify, I've used email addresses from our company, ensuring I haven't manually inputted anything. I've removed them from this post to protect my email address from being publicly available.

The problem in your solution that I have is when in one folder I have multiple SMEs, An error message appears: : The 'inputs.parameters' of workflow operation 'Send_an_email_(V2)' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'emailMessage/To' is required to be of type 'String/email'. The runtime value '"email1SME.com,email2SME.com" to be converted doesn't have the expected format 'string/email'.

 

Each individual SME should only receive an email and document intended for them. 

Regarding the remaining issues, I'll follow your suggestion and provide a more detailed description in a new thread to seek further assistance from the forum.

Once the issues are resolved, I'll make sure to mark your response as a solution.

Thank you once again for your support.

Best Regards,
Andrew

[v-yetonggu-msft]

Hi @andewmilorad ，

Is your SME column a multi-select person column?

Best Regards,

Sunshine Gu

[andewmilorad]

Hello @v-yetonggu-msft 
Please see below - no its not. Only one SME per document is allowed to be. 

[v-yetonggu-msft]

Hi @andewmilorad ，

Could you show me your 'replace' formula? I guess your formula is wrong.

Best Regards,

Sunshine Gu

[andewmilorad]

Hi @v-yetonggu-msft 
This is formula that I put in section "TO" 

replace(replace(replace(string(outputs('Compose')),'[',''),']',''),'"','')

Kind regards,

Andrew

[v-yetonggu-msft]

Hi @andewmilorad ,

Please go to the flow running history and post a screenshot of the output of your compose.

Please do not block all email addresses.

You can block the domain name of the email address. 

Thank you for your cooperation.

Like this:

Best Regards,

Sunshine Gu

[andewmilorad]

Hello @v-yetonggu-msft 

Hope this is what are you looking for 🙂 

Kind regards,

Andrew

[v-yetonggu-msft · Accepted solution]

Hi @andewmilorad ,

I know the problem.

Change the output of compose in Apply to each to this formula.

split(replace(replace(replace(string(outputs('Compose')),'[',''),']',''),'"',''),',')

Then change the formula in 'Send an email' to this formula.

replace(replace(replace(items('Apply_to_each'),'[',''),']',''),'"','')

My Flow:

My Outlook Inbox:

Best Regards,

Sunshine Gu

[andewmilorad]

Thank you so much! 🙂 
Now everything works fine!! You are the best 

[andewmilorad]

Hello @v-yetonggu-msft greatly appreciate your assistance with a few questions I have regarding its functionality. I'm hoping you can lend me a hand! 🙂 

My question are: 

• Custom Name for SMEs in Email: How can I add a custom name for each Subject Matter Expert (SME) at the beginning of the email?
• Custom Date for Review Deadline: Is there a way to add a custom date that is "+14 days" from the current date for when the SMEs have to review the content?

•  Hyperlinking "Item Name" in Email: Is it possible for the email received by the SMEs to display as "Item Name" with a hyperlink directly to the document, creating a short URL?

• Error Handling with Multiple SMEs: Additionally, when I have multiple SMEs, it prompts the following error: "Every individual SME should receive only email and document for himself." How can I resolve this issue?

  

I would highly appreciate detailed instructions or any insights you could provide to help me navigate through these challenges.

Thank you all in advance for your support!