Use Case: Automating the process of sending project status emails can save time and ensure consistency. This involves dynamically retrieving project details from a SharePoint list, formatting the information into a structured email, and sending it to relevant stakeholders. The entire workflow can be triggered by a simple Microsoft Copilot command, such as:
“Send a project status email for Project Alpha to the stakeholders.”
In the following solution Copilot acts as the front-end, allowing users to trigger the flow using natural language commands.
Create the Power Automate Flow
Start by building a flow that retrieves project details and sends an email.
Trigger: HTTP Request
- In Power Automate, create a new flow.
- Select When an HTTP request is received as the trigger.
- Define the JSON schema to accept dynamic inputs
{
"type": "object",
"properties": {
"projectId": { "type": "string" },
"emailSubject": { "type": "string" },
"stakeholders": { "type": "array" }
}
}
Retrieve Project Data:
- Add a Get Items action to fetch data from the SharePoint list where your project updates are stored.
- Use a filter query to retrieve details for the specified project ID:
ProjectID eq @{triggerBody()?['projectId']}
Format the Data
- Add an HTML Table action to format the project details.
- Configure the table to include relevant columns like Task Name, Status, and Deadline.
Send the Email- Add a Send an Email (V2) action.
- Configure the action:
- To: Pass the stakeholders dynamic content from the HTTP trigger.
- Subject: Use the emailSubject input.
- Body: Include the HTML table from the previous step.
Save the FlowConfigure the Action in Copilot StudioSet Up a New Action- Open Microsoft Copilot Studio.
- Navigate to Actions and click New Action.
Configure the HTTP Request- Method: POST
- URL: Paste the HTTP trigger URL from the Power Automate flow.
- Headers: Add Content-Type: application/json.
- Body:
{
"projectId": "{{projectId}}",
"emailSubject": "{{emailSubject}}",
"stakeholders": "{{stakeholders}}"
}
Add Conversational Prompts
Define prompts that users will use to trigger the action.
Example: Send a project update for Project Alpha to stakeholders [email1@example.com, email2@example.com].
Map the dynamic inputs (projectId, emailSubject, and stakeholders) to match user inputs.
Test Power Automate and Copilot – this should work!
Thank you for your time. For more updates and in-depth insights, visit my YouTube and Blog, Be sure to Subscribe for regular content. 
