How to extract date and time stamp of specific emails received in Outlook automatically and populate data in Excel File ?

[memento]

I have checked the following and tried to incorporate them:

Solved: Re: Create a Flow when email body contain a specif... - Power Platform Community (microsoft....

Solved: Re: Extract an Email Received Date/Time and Update... - Power Platform Community (microsoft....

but I am stuck - how to i get the action name for "Extract the date and time of the email received" ?

condition: The emails received with specific words 

and with those emails, extract:

- the date and time received

- from (sender)

- message subject

- Size of message

and place the above information in an excel file that is in sharepoint site.

This is the current skeleton of my flow

[ekarim2020]

HI @memento ,

Search for the Received Time property. In the example below the Received Time property is being stored in a Compose action:

 

Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site

[memento]

Thanks @ekarim2020 

This is what i did, the flow ran successfully but nothing was added to my excel table:

The message :

But in the email test message, I did include the following statement "Pertaining to the below complaint,"

What went wrong ?

[v-yetonggu-msft]

Hi @memento ,

Regarding the format of line breaks in the body of an email, I suggest you split each word into.

I did a test for your reference.

My Excel Table:

My Flow:

Best Regards,

Sunshine Gu

[memento]

Thanks @v-yetonggu-msft it works now.

But how to just show the date only under Date Received 

and also time only in local time under Time Received?

Desired Output

Date Received = 2024-06-24

Time Received = 11:24 

Thanks.

[memento]

Hi 

I have managed to resolve the date and time part:

formatDateTime(utcNow(),'yyyy-MM-dd')
formatDateTime(utcNow(),'hh:mm')

But the time is incorrect.

How do I convert the time to local time?

[memento]

I checked this

Reference guide for expression functions - Azure Logic Apps | Microsoft Learn

and 

Default Time Zones | Microsoft Learn

But I can't seem to get right syntax to convert the time.

Please help !

[memento]

Refering to Default Time Zones | Microsoft Learn

I tried to change:

formatDateTime(utcNow(),'hh:mm')

to 

formatDateTime(utcNow(),'hh:mm', 'Singapore Standard Time')

and the error states the locale time cannot be found. But i checked "Singapore Standard Time" is found in the table in Default Time Zones | Microsoft Learn

So what have i typed wrongly ???

[memento]

Hi @ekarim2020  @v-yetonggu-msft  any advice for the above questions on conversion in the time extraction ?

Thanks

[memento]

And how to get the text of the body from the email?

I tried to find dynamic content 'text' but it's not available.

As i use Body

the output in excel turns out to be all HTML codes.

any idea ?

[Nived_Nambiar]

Hi @memento 

Expression you have used is wrong - it should be like this 

convertFromUtc(utcNow(),'Singapore Standard Time','hh:mm')

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[Nived_Nambiar]

Hi @memento 

i see that you are using HTML to text action in your flow- use its output to get plain email body content if you have used input as email body inside it.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[memento]

Hi @Nived_Nambiar 

Is it possible to extract only the text in sentences and exclude those with email links?

Thanks!

[Nived_Nambiar]

Hi @memento 

well you can do it using regex which can be implemented via office scripts in excel.

For this - create the following office script in a blank excel file stored either in sharepoint/onedrive.

function main(workbook: ExcelScript.Workbook, text: string) {

    const urlRegex = /https?:\/\/[^\s]+/g;

    console.log(text.replace(urlRegex, ""));

    return text.replace(urlRegex, "");

}

Now save the script and let' see how we will be using it.

Assume that this is the email body with hyperlink

Next after retrieving the email body anc converting it to plain text as shown below

Use run script action to rrun the office script which removes the URL/Hyperlinks 

Also pass the plain text content output to input to this script as shown above.

Now the result dynamic content from the run script action will have required output- text with no hyperlink.

Now see the result-

what you see after converting email body to plain text (input)

What you see in run script's output 

I would recommend it to test bit more with your test data and add any changes/updates to code if needed. Well this can serve as basis for solving the query.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[memento]

Hi @Nived_Nambiar 

Thanks for the detailed explanation.

I tried but seems like it is not working for me as the links still appear.

my overall flow:

[Nived_Nambiar]

Could you show the links looks in your email body when converted to plain text

As you have just shown the flow design of yours - so I could not be very clear what is the exact issue u are facing ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[memento]

HI @Nived_Nambiar 

so in the output shows text and if there is any URL, it will just start with [  and then is blank.

However, in the excel file, it shows everything:

Caution: External Email

Hi @firstname last name [firstname.lastname@name.com]

Kindly refer to the link of ABC report123 below.

ABC Report123.xlsx
[https://companyname.sharepoint.com/:x:/r/sites/O365-SharepointSitename/blahblahblahblahblahblahblahb...............................................]

Thank you.

Note: i have removed the actual data but masked with other words to bring out the same meaning. 

Why did the output shown in PA flow different from the actual output in Excel ?