Outline
- Introduction
- Prerequisite
- Build the Solution
Introduction
As a program manager have you ever been faced with a task where you have to repeatedly fill a certificate with details of participants of an event or perform a repetitive task involving filling a template? Performing such task over and over again could get tiring especially when it seems like the list is inexhaustible.
Microsoft Power Automate tool helps to perform tasks like that by helping to populate the template with the right details but this requires having a Premium connector which is a paid service on the Power Automate tool. Fortunately, there is a way around performing this task without need of a premium connector and still achieving the result needed.
This content is useful in an office, school setting, for a Project/Program manager, Email marketers and every other task that involves filling out details on a template for a large number.
This tutorial will be focused on sending out digital certificates to participants of an event and can also be useful in other similar tasks like sending personalized documents to multiple people.
Prerequisite
To be able to follow through with this guide, you will need to have the following
1. Microsoft Office License with Power Automate License. In case you do not have a Microsoft Office License, click here for a guide on how to sign up for Microsoft Developer Account https://aka.ms/SetupM365Developer
STEPS FOR SENDING OUT DIGITAL CERTIFICATE TO PARTICIPANTS OF AN EVENT
STEP 1: Create a Microsoft Form
It serves as a trigger for the flow in performing the task. This will take in the details of the participants that need to be on the certificate and also their email address where the certificate is to be sent. Note that the names filled out in the form is what will be written on the certificate.
STEP 2: Create a SharePoint Site or use an existing site.
Sharepoint is where the certificate is to be uploaded for use. If there is an existing site it can be used else a new site will be created. A communication site is best used.
The site is given a name though the description is not necessary.
STEP 3: Create a Document Library on the Site
This is where the certificate will get uploaded. Name the library.
Add columns for the details that are needed which are the Full name, Email address and Date Issued to the document library (named Certificates you can name it whatever you want).
The column will be added based on the type of information that will be needed. The Full name and Email Address are under the Text Category while the Date Issued falls under Date and Time category.
Full Name is added to the column, formatted as needed and Saved.
Email Address is added, formatted as needed and saved
Date issued is added to the column though time is not needed to be shown on a certificate so it will be formatted as no time needed
STEP 4: Upload the Certificate to SharePoint
Open the Certificate in App
Add Textbox to the Certificate opened this will hold the Name and Date issued on the certificate. Also any formatting needed on how the Certificate should look can be done here like setting it to be Bold or Capital letter.
Select Quick part to insert the property for Full Name and Date Issued on the Certificate. After which it automatically saves and the word application can be closed.
Move the Certificate to the Sharepoint document folder
Go back to the document library, right click on the document and click on MOVE, select Document library as shown in the image below
STEP 5: Create the flow on Power Automate
To make all of this functional the flow to automate this process is created on Power Automate.
An automated Flow is used because it is an automatic response needed that is, the certificate is needed to be sent to the participants whenever the form is filled.
Name the flow. This flow was named Participants Certificate.
The flow Trigger is made. A Trigger is what initiates the flow which is the Microsoft Form in this case. When a new response is submitted to the MS Form is what triggers the Flow.
A trigger is important to set for every flow in Power Automate.
After clicking the create button it asks for the Form ID which is the MS Form created to receive responses. Select the right form created from the options displayed. In this tutorial, the form created in the first step is called EVENT CERTIFICATE.
The next step on the flow is to add an Action which is the Get Response Details from the MS Form that is, the response submitted in the form is added to the flow.
The Form name and Response ID are filled in appropriately.
A Copy file flow is added to the flow to duplicate the certificate. Be careful to select the right Action which is for the file to be copied into SharePoint.
This step might require you to sign into your SharePoint Account.
The certificate is copied from the SharePoint site document folder where it originally exists to the Document library created in the SharePoint site. This is so that the certificate template will be in the same folder as the response gotten from the MS Form. This will be carried out anytime the flow is triggered so it needs to be replaced so it does not clash with an old file that was copied there in the last trigger.
The next Action is to Update the file property that is, updating the columns in Sharepoint created for Full Name, Email address and Date Issued with the response of the Form. The option selected should be for SharePoint.
The fields are filled appropriately. The ItemID is from the file copied since that is what to be filled with the response gotten. The details of the field for Full Name, Email address and Date Issued is from the Dynamic content of the Get Response Details.
The next Action is to Get the file content that is, the Certificate that was copied that has been updated.
The file identifier is the id dynamic content of the copied file.
The next Action is to create the file for the individual certificates to be issued in a new folder on OneDrive. Note that the option for SharePoint should not be selected.
You could be required to Sign in to your OneDrive Account.
A Root folder was used to hold the file created on OneDrive. The File is named with the Participants Full name as a Dynamic Content, time as an Expression and the .docx document type and the File Content is the Body Dynamic Content from the Updated File Properties.
With this the Name used for every file will change based on the name and time participants fill the Form.
An Action for the created File that is in docx format is to be converted to a PDF format which is more appropriate for the Certificate to be issued in.
The Id of the created File on OneDrive is used to identify the file to be converted.
The PDF in the OneDrive will not be useable there so it needs to be added to SharePoint by adding an Action to create a File in SharePoint.
The fields will be filled appropriately. The dynamic content will be the converted file in Onedrive which includes the File Name and File Content.
The new file created in Sharepoint needs to be updated with the File Properties.
It is filed appropriately with the ID of the File created and details coming from the Form Response
A Delete File Action for the File (the Word document) created in OneDrive will be added.
Note that the Delete File option should be that of the OneDrive.
The File to be deleted is identified by the Id Dynamic content
The initial word document added to Sharepoint by The Copy File Action will also be deleted since it is no longer needed at this point in the flow. The Delete file option should be SharePoint.
The File to be deleted is identified by the Id Dynamic content of the Copy File
STEP 6: Send the Certificate by Mail
The Certificate has been prepared and is ready to be sent out to the participants.
The email content will be written with the required Dynamic Content for the Names Email address and any other expressions that might be needed.
The Advanced option is used to add the file attachments to be sent which will be the converted file and content. The importance of the Mail is also set to be Normal or High but never Low so the mail can be sent with High importance.
STEP 7: The Flow is Saved and Tested
This is the First Test so it will be done Manually
Next the MS Form will be filled with the required details to see if the flow will run successfully
If the Flow is successful it will notify after the Test which indicates that it can be used to send out Certificates to Participants successfully
STEP 8: The Certificate will be sent to every participants who fills the Form
The output expected in this tutorial was this
Additional Resources
Visit the student hub https://aka.ms/learnstudent
