Answered

Extracting Data from an email to create a calendar event

(0) Share

Report

Posted on by moreno5

I am very new to this and am trying to work my way around PowerAutomate.

I would like data to be extracted from an email when it arrives in an inbox. I then want to use that data to create an event in a calendar.

I have created the "When a new email arrives (V3)' and then converted the body with the "Html to text" section.

The email body is as follows:

"Please take care, this email is from an external source.

Do not click on any links or open any attachments unless you are sure you know who the sender is.

**** This message is sent from an unmonitored account. Please do not reply. ****

Joe Bloggs has requested Holiday from 19/10/23 to 03/11/23.
The Total time the employee is away is 12 day(s), a total of 84 hour(s).
Joe Bloggs will return to work on 04/11/23.
Please login to https://www.login.xxxxxxxx.xx.xx/xxxxxxxx to approve or reject this request.

This request will be deleted in 20 days if there is no response.

Kind regards."

I would like to extract the following things from this email

Name of the person - Joe Bloggs (will be different each time)

Type of Absense - Holiday

Date From - 19/10/23

Date To - 03/11/23

Once I have this information I would like to create an event using the data above.

Any help appreciated.

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

HenWang Microsoft Employee on at

Like (1)

Report

Hi

Please follow below formulas to get the value you want:

I copied the example from your description and paste them in “Compose” action;

In my Compose 3 action, below formula will return this text:
Formula: split(outputs('Compose'),'.')[4]

Text:
****

Joe Bloggs has requested Holiday from 19/10/23 to 03/11/23

The name of the person formula:

slice(split(outputs('Compose_3'),'has')[0],7)

Type of absence:

split(split(outputs('Compose_3'),'requested')[1],'from')[0]

Date from:
split(split(outputs('Compose_3'),'from')[1],'to')[0]

Date to:
split(split(outputs('Compose_3'),'from')[1],'to')[1]

Best Regards,
Hen Wang

Was this reply helpful? Yes No
moreno5 20 on at

Like (0)

Report

Thank you @HenWang, it worked brilliantly.

I am now trying to create a calendar event using the data we have split. It looks like to do so I will need to convert the date to a date that PowerAutomate understands. Do I need to initialise a variable?

How can I convert both "date from" and "date to"?

Currently it is DD/MM/YY and I need to convert it to YYYY-MM-DDTHH:MM:SS

Thanks in advance

Was this reply helpful? Yes No
HenWang Microsoft Employee on at

Like (0)

Report

Hi @moreno5 ,

No need to create new variable to hold the value. You can use below formula to format the date value:
formatDateTime(outputs('Date_from'), 'yyyy-MM-ddTHH:mm:ss')

For example:
formatDateTime('03/15/2018 12:00:00', 'yyyy-MM-ddTHH:mm:ss') // Returns '2018-03-15T12:00:00'.

More information about this formula:
Reference guide for expression functions - Azure Logic Apps | Microsoft Learn

Best Regards,
Hen Wang

Was this reply helpful? Yes No
moreno5 20 on at

Like (0)

Report

@HenWang Thanks for your prompt reply.

I get the following error message when doing this:

"Unable to process template language expressions in action 'Compose_3' inputs at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string ' 25/01/24 ' was not valid. The datetime string must match ISO 8601 format.'."

Was this reply helpful? Yes No
HenWang Microsoft Employee on at

Like (1)

Report

Hi @moreno5 ,

Below formula would help:

In compose 2:
concat(split(outputs('Date_from'),'/')[1],'/',split(outputs('Date_from'),'/')[0],'/',string(20),split(outputs('Date_from'),'/')[2])

In compose 4:
formatDateTime(outputs('Compose_2'), 'yyyy-MM-ddTHH:mm:ss')

Actually you need to do the change also for the "Date to" value because I didn't notice that you are using the date format which can not be recognized by power automate flow.

Hope this will solve the problem.

Best Regards,
Hen Wang

Was this reply helpful? Yes No
moreno5 20 on at

Like (1)

Report

Thanks, that worked a treat!

Was this reply helpful? Yes No
moreno5 20 on at

Like (0)

Report

Hi @HenWang

I am getting some strange results from this. It seems to put in the calendar entry but finishes a day before it should.

The follow email was sent....

"Please take care, this email is from an external source.
Do not click on any links or open any attachments unless you are sure you know who the sender is.

**** This message is sent from an unmonitored account. Please do not reply. ****
Jane Wright has requested holiday from 10/06/23 to 21/06/23.
The Total time the employee is away is 7 day(s), a total of 56 hour(s).
Jane Wright will return to work on 22/06/23
Please login to https://www.login..co.uk to approve or reject this request.

This request will be deleted in 20 days if there is no response.

Kind regards."
The image below shows the entry in my calendar.
As you can see it ends on 20th instead of 21st.

This is the final output in the flow which looks fine.

Do you know what is going on?

thanks,

Was this reply helpful? Yes No
moreno5 20 on at

Like (0)

Report

bump

Was this reply helpful? Yes No