Sending Emails With Attachments And Lookup Data In Power Automate

Power Automate aka Microsoft Flow has been quickly adopted as a replacement for SharePoint Designer Workflows. Especially, for citizen developers it has become easier to develop complex business processes vs. having to do it in SharePoint Designer earlier.

In my previous article, I had covered a scenario of Parent-Child relationship between PowerApps Forms. In this article, I am going to extend the scenario by creating a Flow, that will send a notification to Job Poster when an applicant has shown interest in their job posting.

Getting Started,

From our last article, we already have created the Job Applications list. As a recap, we have the list with following structure.

Job Applications
Column Name	Type
Title	Single Line of Text
Job ID	Lookup (Title: Job Postings)

We will start by creating the Flow on Job Applications list.

Creating the Flow

In order to create the Flow, navigate to Job Applications list. In the ribbon, click on Power Automate and then click on Create a Flow.

Sending Emails With Attachments And Lookup Data In Power Automate

In the Panel that opens, click on See your flows at the bottom.

You will be redirected to flow.microsoft.com. Click on Create -> Automated Flow

Provide an appropriate name to your flow and select the SharePoint trigger for “When an item is created” and click on Create.

In the Flow UI, select the site url and list name i.e. Job Applications

As a next step, we will initialize couple of variables that will be used later in the flow. So click on New Step and add Initialize Variable action to the flow

Rename the step, it make it easy to identify the action in later steps. Set a name for the variable, type as String and value as Job ID from dynamic content. Note that the Job ID column is a lookup column in this list.

Since, the Job ID column is a lookup column, the output of this step will be a string JSON format. So we need to extract the ID from this JSON. In order to do so, let’s use a Compose action in our flow. Since the variable we created is a string object, we will convert it into a JSON and then get the value of Id variable from the string using an expression.

Note
In my previous article on Tabbed PowerApps, I had shown the format for a lookup column. Based on same format, this data is extracted.

Similarly, add another step to include an Array Variable. Keep value as blank for now.

Now, that we have all the variables set, let’s get the details of the Job Posting for the current application. We will use the output of the Compose step to get the details of the Parent Item in Job Postings list

We will use the details from this step to get Job Poster and other details. Next, we need to get the attachments from Job Applications list. We will use Get Attachments action for this step.

As a next step, we need to get all the attachments in a variable and later use it in the email. So, we will add Apply to each step and use the Body from the previous step as input for this action. And we will add two steps in this action, first to get the attachments and second to append the attachments in array variable.

In the Get Attachment Content step, configure it to get the attachments from current Job Application item. File Identifier property should be set to the Id of Get Attachments action added in previous steps.

In the Append to Array Variable step, set the value as a JSON Object. This JSON will have two properties, Name and ContentBytes. Name will be mapped to the Display Name of Get Attachments action, ContentBytes will be an expression that will get value from ‘$content’ property of Get Attachments action.

Lastly, we will use Send Email Notification step to send the email to Job Poster with details of the applicant and the attachments as provided by the applicant for the position. In the To address, add the email of Created By user from Job Postings list. Provide a subject and email body as per your needs.

To add attachments in this email, click on Show Advanced Options, then in the Attachments section, click on the switch to convert the input to an Array

Add the array variable to this property. Click Save.

Completed flow, should look like below

Final Outcome

Now, the flow should be ready to test. Create an item from PowerApps that will insert the entry into Job Applications list. Job poster will now receive an email with attachments as uploaded by Job Applicant.