Automate Certificate Generation and Delivery for Quiz Passers

Table of Contents

1. Introduction
2. Setting Up the Process in Microsoft Power Automate
   • 2.1 Requirements
   • 2.2 Creating the Quiz in Microsoft forms
   • 2.3 Creating the Certificate Template
   • 2.4 Creating the Excel spreadsheet
3. Creating the Power Automate Flow
   • 3.1 Setting Up the Trigger
   • 3.2 Getting Response Details from Microsoft Forms
   • 3.3 Adding a Row into the Excel Spreadsheet
   • 3.4 Delaying the Flow
   • 3.5 Getting the Row with the Score
   • 3.6 Handling Pass and Fail Scenarios
     • 3.6.1 Sending an Email for Failed Quiz
     • 3.6.2 Generating and Sending Certificate for Passed Quiz
   • 3.7 Converting the WORD Document to PDF
   • 3.8 Saving a Copy of the PDF to OneDrive
   • 3.9 Sending the Certificate via Email
4. Conclusion
5. Resources

Setting Up a Process in Microsoft Power Automate for Sending Personalized Certificates via Email

In this Tutorial, we will walk you through the process of setting up a Microsoft Power Automate flow that automates the sending of personalized certificates to users who have passed a quiz created in Microsoft Forms. This process requires a Power Automate premium license, as some actions in the flow are only available with a premium license. However, there is an option for a 90-day premium trial, and the license cost is reasonable.

Requirements

To complete this process, you will need the following:

• Power Automate premium license
• Certificate in Word document format
• Quiz set up in Microsoft Forms
• Excel spreadsheet with a table that matches the fields in Microsoft Forms

Creating the Quiz in Microsoft Forms

Start by logging into Microsoft Forms and selecting the "New Quiz" button. Give your quiz a name and add a description if desired. Next, add sections to separate the user's information from the quiz questions. Within the section for the user's information, add text questions to Collect their name and email address, making them required responses. Additionally, include a consent question to comply with data protection purposes. For the quiz section, add multiple-choice questions, providing options and indicating correct answers. Make all questions required.

Creating the Certificate Template

Design your certificate in your preferred software, ensuring it is available as an image file. Insert the image file into a Word document and adjust the layout to Resemble a certificate. Add fields for the user's name and the date and time stamp. Use the Developer tab to insert plain text content controls for these fields. Add properties and save the certificate as a Word document.

Creating the Excel Spreadsheet

Open Excel and add fields for the name, email, and each question in the quiz. Include additional columns for score and a unique ID. Convert the cells to a table, ensuring the first row contains column headings. Use formulas to calculate the score based on the responses. Save the spreadsheet to your OneDrive or SharePoint.

Creating the Power Automate Flow

To create the flow, open Microsoft Power Automate and click on the "New" button. Select "Automated cloud flow" and enter a name for the flow. Choose the trigger "When a new response is submitted in Microsoft Forms." Select the quiz you created as the form.

Using the "Get response details" action, retrieve the response ID from Microsoft Forms. Add a row to the Excel spreadsheet using the "Add a row into a table" action. Set a delay to allow time for the spreadsheet to populate. Retrieve the row that contains the score using the "Get a row" action.

Next, add a condition control to handle the pass and fail scenarios. If the score is below the pass mark, send an email to the user notifying them of their quiz results. If the score is equal to or above the pass mark, generate a personalized certificate for the user in the Word document and convert it to PDF. Save a copy of the PDF to OneDrive and send it to the user via email.

In conclusion, by following these steps, you can set up a Microsoft Power Automate flow that automatically sends personalized certificates to users who have passed a quiz. This not only streamlines the process but also enhances the user experience. With the ability to customize certificates and automate the delivery, you can save time and effort while providing a professional touch.

For further assistance and detailed instructions, please refer to the resources below.

Resources

• Microsoft Power Automate
• Microsoft Forms
• Power Automate Premium License
• Creating a Certificate in Word
• Creating a Quiz in Microsoft Forms
• Creating an Excel Spreadsheet