Skip to main content

Notifications

Power Automate - Building Flows
Unanswered

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

(0) ShareShare
ReportReport
Posted on by

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.com)

 

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

 

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

memento_0-1719128075871.png

 

  • memento Profile Picture
    memento on at
    Re: How to extract date and time stamp of specific emails received in Outlook automatically and populate data in Excel File ?

    HI @Nived_Nambiar 

     

    memento_0-1721005018059.png

     

    memento_1-1721005115530.png

     

    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/blahblahblahblahblahblahblahblahblahblahblahblahblahblahblah/............................................]

     

     

     

    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 ?

  • Nived_Nambiar Profile Picture
    Nived_Nambiar 17,115 on at
    Re: How to extract date and time stamp of specific emails received in Outlook automatically and populate data in Excel File ?

    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 Profile Picture
    memento on at
    Re: How to extract date and time stamp of specific emails received in Outlook automatically and populate data in Excel File ?

    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:

    memento_0-1720754478908.png

     

  • Nived_Nambiar Profile Picture
    Nived_Nambiar 17,115 on at
    Re: How to extract date and time stamp of specific emails received in Outlook automatically and populate data in Excel File ?

    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, "");


    }
     
    Nived_Nambiar_0-1720719645279.png

     

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

     

    Assume that this is the email body with hyperlink

    Nived_Nambiar_1-1720719700896.png

     

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

    Nived_Nambiar_2-1720719748715.png

     

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

    Nived_Nambiar_3-1720719806932.png

     

    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.

    Nived_Nambiar_4-1720719875902.png

     

    Now see the result-

     

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

    Nived_Nambiar_5-1720719930666.png

     

    What you see in run script's output 

    Nived_Nambiar_6-1720719956933.png

     

    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 Profile Picture
    memento on at
    Re: How to extract date and time stamp of specific emails received in Outlook automatically and populate data in Excel File ?

    Hi @Nived_Nambiar 

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

    Thanks!

  • Nived_Nambiar Profile Picture
    Nived_Nambiar 17,115 on at
    Re: How to extract date and time stamp of specific emails received in Outlook automatically and populate data in Excel File ?

    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.

    Nived_Nambiar_0-1720629124111.png

     

    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 Profile Picture
    Nived_Nambiar 17,115 on at
    Re: How to extract date and time stamp of specific emails received in Outlook automatically and populate data in Excel File ?

    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! 🙌


  • memento Profile Picture
    memento on at
    Re: How to extract date and time stamp of specific emails received in Outlook automatically and populate data in Excel File ?

    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

    memento_0-1720605841560.png

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

     

    any idea ?

  • memento Profile Picture
    memento on at
    Re: How to extract date and time stamp of specific emails received in Outlook automatically and populate data in Excel File ?

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

    Thanks

  • memento Profile Picture
    memento on at
    Re: How to extract date and time stamp of specific emails received in Outlook automatically and populate data in Excel File ?

    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 ???

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,691

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 65,019

Leaderboard