@clueless010 yes this can be done with the exception of #6: a flow will either run successfully or it will fail, you can't leave the email open in the middle of the flow for manual fixing. Also, if you just use the name joe black you run the risk of duplicate names so it would much more efficient if the title/subject in the calendar is Geburtstag email e.g. Geburtstag rob.elliott@yourcompany.com
The flow will look like this:
1. The trigger is a recurrence schedule set to 1 day to run at 07:30.
2. Next, add 2 compose actions, one to check the calendar from time and one for the to time. For the from compose click in the Inouts field and from the dynamic content box that appears click the Expression tab and paste in formatDateTime(utcNow(),'yyyy-MM-ddT00:00:00.0000000')
For the to compose the expression is AddDays(formatDateTime(utcNow(), 'yyyy-MM-ddT00:00:00.0000000'),1)
3. Next add the Office 365 Outlook action get events (v4). Select your calendar then click in the filter query field and type Start/DateTime ge '' and inside the single quotes select the outputs of the from compose action from the dynamic content box.
Then type and Start/DateTime lt '' and inside the single quotes select the outputs of the to compose action from the dynamic content box.
Finally for the filter query type and IsAllDay eq true so it will only bring back All Day events between the from and to date/times.
4. Next add an apply to each and select value from the dynamic content box. Add a condition and in the left field select Subject from the get events section of the dynamic content box, in the middle field select starts with, and in the right field type Geburtstag.
The red if no channel can be left empty. In the green if yes channel add a compose action and add the expression substring(items('Apply_to_each')?['subject'], 11) which will get all the content of the subject/title after after 11 characters (Geburstag ), that is the email address.
5. You will need to store your random messages in a SharePoint list (or an Excel spreadsheet, but Excel is a rubbish data source so better to use a list). For this example I already had a list called countries. Put the message in the title column.
6. Next, to get a random item from the list add a compose action and add the expression
body('Get_items')?['value'][rand(0,length(body('Get_items')?['value']))]
7. Next add a Parse JSON action. For the Content field select the outputs of the Random compose above. In the schema paste in the following JSON
Spoiler (Highlight to read)
{
"type": "object",
"properties": {
"@@odata.etag": {
"type": "string"
},
"ItemInternalId": {
"type": "string"
},
"ID": {
"type": "integer"
},
"Title": {
"type": "string"
},
"County": {
"type": "string"
},
"Modified": {
"type": "string"
},
"Created": {
"type": "string"
},
"Author": {
"type": "object",
"properties": {
"@@odata.type": {
"type": "string"
},
"Claims": {
"type": "string"
},
"DisplayName": {
"type": "string"
},
"Email": {
"type": "string"
},
"Picture": {
"type": "string"
},
"Department": {
"type": "string"
},
"JobTitle": {
"type": "string"
}
}
},
"Author#Claims": {
"type": "string"
},
"Editor": {
"type": "object",
"properties": {
"@@odata.type": {
"type": "string"
},
"Claims": {
"type": "string"
},
"DisplayName": {
"type": "string"
},
"Email": {
"type": "string"
},
"Picture": {
"type": "string"
},
"Department": {
"type": "string"
},
"JobTitle": {
"type": "string"
}
}
},
"Editor#Claims": {
"type": "string"
},
"{Identifier}": {
"type": "string"
},
"{IsFolder}": {
"type": "boolean"
},
"{Thumbnail}": {
"type": "object",
"properties": {
"Large": {},
"Medium": {},
"Small": {}
}
},
"{Link}": {
"type": "string"
},
"{Name}": {
"type": "string"
},
"{FilenameWithExtension}": {
"type": "string"
},
"{Path}": {
"type": "string"
},
"{HasAttachments}": {
"type": "boolean"
},
"{VersionNumber}": {
"type": "string"
}
}
}
{
"type": "object",
"properties": {
"@@odata.etag": {
"type": "string"
},
"ItemInternalId": {
"type": "string"
},
"ID": {
"type": "integer"
},
"Title": {
"type": "string"
},
"County": {
"type": "string"
},
"Modified": {
"type": "string"
},
"Created": {
"type": "string"
},
"Author": {
"type": "object",
"properties": {
"@@odata.type": {
"type": "string"
},
"Claims": {
"type": "string"
},
"DisplayName": {
"type": "string"
},
"Email": {
"type": "string"
},
"Picture": {
"type": "string"
},
"Department": {
"type": "string"
},
"JobTitle": {
"type": "string"
}
}
},
"Author#Claims": {
"type": "string"
},
"Editor": {
"type": "object",
"properties": {
"@@odata.type": {
"type": "string"
},
"Claims": {
"type": "string"
},
"DisplayName": {
"type": "string"
},
"Email": {
"type": "string"
},
"Picture": {
"type": "string"
},
"Department": {
"type": "string"
},
"JobTitle": {
"type": "string"
}
}
},
"Editor#Claims": {
"type": "string"
},
"{Identifier}": {
"type": "string"
},
"{IsFolder}": {
"type": "boolean"
},
"{Thumbnail}": {
"type": "object",
"properties": {
"Large": {},
"Medium": {},
"Small": {}
}
},
"{Link}": {
"type": "string"
},
"{Name}": {
"type": "string"
},
"{FilenameWithExtension}": {
"type": "string"
},
"{Path}": {
"type": "string"
},
"{HasAttachments}": {
"type": "boolean"
},
"{VersionNumber}": {
"type": "string"
}
}
}
8. You are making things unnecessarily complicated by wanting to send the email to the person at a random time and there is no action for a random time. So just add a delay of action of 1 hour or 40 minutes or whatever time delay you want.
Finally, add the send an email action (v2). In the To fields select the outputs of the Person compose action. In the body of the email select Title from the Parse JSON section of the dynamic content box.
The flow will run and send the email with the random message:
Come back with any questions or if something isn't clear.
Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.
Share
Report
All responses (
Answers (
10,458
