Logic Apps: Sending email from SharePoint list to recipient from person column and/or lookup column

Question

I built a workflow in Azure Logic Apps to send an email with attachments from a SharePoint list when a new item is created.

Actually my workflow sends an email with the SMTP connector to a person, which is selected in the SharePoint list item from a column (lets call it "A"). This column "A" is from type "person or group". Now I want to improve my workflow. There is another column (lets call it "B") in my SharePoint list. This column "B" is from type "lookup", which is linked to another SharePoint list. This linked list has just two columns: title and email address.

What I want is this: If a person from "A" AND/OR a title from "B" is selected, then the email should be send to the person from "A" AND/OR to the email address from "B". How could I implement this in my actual workflow?

Anyone that can help? Please see the screenshots from my actual workflow below (the first screenshot is just for the overview of the Logic Apps structure). Thank you in advance.

Answer 1

Hi Sener Gazi

So, as promised I've tested your scenario in my test lab, and came to a solution that fits your needs. At least, assuming you want every recipient to have a seperate email, and not have all email addresses to the 'To' field.

What I did:

• I've recreated your Logic App as starting point
• Set up SharePoint with 2 lists
  • One with a Person column and a Lookup column
    
  • One with a title and email address
    
• Added another array variable called 'recipients'
  
• Next added two checks:
  • One if the person field is filled, and if so: add that person's email address to the 'recipients' array (checking it with a coalesce, returning either the value or null, so when not null it's filled)
    
  • One if the lookup field is filled, and if so: retrieve the list item from the second list to get the email address field, and add that to the 'recipients' field (checking it with a coalesce, returning either the value or null, so when not null it's filled)
    
• Made the 'For each' responsible for sending the mail loop over the 'recipients' array
  

See the code below which you should be able to use directly in your Logic App code view within the 'actions' attribute of the Logic App JSON:

{
            "Check_if_lookup_column_is_filled": {
                "actions": {
                    "Append_lookup_to_array_recipients": {
                        "inputs": {
                            "name": "recipients",
                            "value": "@body('Get_item')?['EmailAddress']"
                        },
                        "runAfter": {
                            "Get_item": [
                                "Succeeded"
                            ]
                        },
                        "type": "AppendToArrayVariable"
                    },
                    "Get_item": {
                        "inputs": {
                            "host": {
                                "connection": {
                                    "name": "@parameters('$connections')['sharepointonline']['connectionId']"
                                }
                            },
                            "method": "get",
                            "path": "/datasets/@{encodeURIComponent(encodeURIComponent('https://test.sharepoint.com/sites/SaaSDEMO'))}/tables/@{encodeURIComponent(encodeURIComponent('ebf1d197-1104-425e-8d7b-ed81722dddfa'))}/items/@{encodeURIComponent(triggerBody()?['LookupColumn']?['Id'])}"
                        },
                        "runAfter": {},
                        "type": "ApiConnection"
                    }
                },
                "expression": {
                    "and": [
                        {
                            "not": {
                                "equals": [
                                    "@coalesce(triggerBody()?['LookupColumn']?['Id'])",
                                    "@null"
                                ]
                            }
                        }
                    ]
                },
                "runAfter": {
                    "Check_if_person_column_is_filled": [
                        "Succeeded"
                    ]
                },
                "type": "If"
            },
            "Check_if_person_column_is_filled": {
                "actions": {
                    "Append_person_to_array_recipients": {
                        "inputs": {
                            "name": "recipients",
                            "value": "@triggerBody()?['PersonColumn']?['Email']"
                        },
                        "runAfter": {},
                        "type": "AppendToArrayVariable"
                    }
                },
                "expression": {
                    "and": [
                        {
                            "not": {
                                "equals": [
                                    "@coalesce(triggerBody()?['PersonColumn']?['Email'])",
                                    "@null"
                                ]
                            }
                        }
                    ]
                },
                "runAfter": {
                    "Initialize_variable_recipients": [
                        "Succeeded"
                    ]
                },
                "type": "If"
            },
            "For_each": {
                "actions": {
                    "Send_Email_(V3)": {
                        "inputs": {
                            "body": {
                                "Attachments": "@variables('Attachments')",
                                "To": "@{items('For_each')}"
                            },
                            "host": {
                                "connection": {
                                    "name": "@parameters('$connections')['smtp']['connectionId']"
                                }
                            },
                            "method": "post",
                            "path": "/SendEmailV3"
                        },
                        "runAfter": {},
                        "type": "ApiConnection"
                    }
                },
                "foreach": "@variables('recipients')",
                "runAfter": {
                    "Check_if_lookup_column_is_filled": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "For_each_2": {
                "actions": {
                    "Append_to_array_variable": {
                        "inputs": {
                            "name": "Attachments",
                            "value": {
                                "ContentData": "@{body('Get_attachment_content')['$content']}",
                                "ContentType": "@{body('Get_attachment_content')['$content-type']}",
                                "FileName": "@{items('For_each_2')?['DisplayName']}"
                            }
                        },
                        "runAfter": {
                            "Get_attachment_content": [
                                "Succeeded"
                            ]
                        },
                        "type": "AppendToArrayVariable"
                    },
                    "Get_attachment_content": {
                        "inputs": {
                            "host": {
                                "connection": {
                                    "name": "@parameters('$connections')['sharepointonline']['connectionId']"
                                }
                            },
                            "method": "get",
                            "path": "/datasets/@{encodeURIComponent(encodeURIComponent('https://test.sharepoint.com/sites/SaaSDEMO'))}/tables/@{encodeURIComponent(encodeURIComponent('679c40a4-9b60-496f-88f2-8cb8f2addb9b'))}/items/@{encodeURIComponent(encodeURIComponent(triggerBody()?['ID']))}/attachments/@{encodeURIComponent(items('For_each_2')?['Id'])}/$value"
                        },
                        "runAfter": {},
                        "type": "ApiConnection"
                    }
                },
                "foreach": "@body('Get_attachments')",
                "runAfter": {
                    "Initialize_variable": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "Get_attachments": {
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['sharepointonline']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/datasets/@{encodeURIComponent(encodeURIComponent('https://test.sharepoint.com/sites/SaaSDEMO'))}/tables/@{encodeURIComponent(encodeURIComponent('679c40a4-9b60-496f-88f2-8cb8f2addb9b'))}/items/@{encodeURIComponent(encodeURIComponent(triggerBody()?['ID']))}/attachments"
                },
                "runAfter": {},
                "type": "ApiConnection"
            },
            "Initialize_variable": {
                "inputs": {
                    "variables": [
                        {
                            "name": "Attachments",
                            "type": "array"
                        }
                    ]
                },
                "runAfter": {
                    "Get_attachments": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_variable_recipients": {
                "inputs": {
                    "variables": [
                        {
                            "name": "recipients",
                            "type": "array"
                        }
                    ]
                },
                "runAfter": {
                    "For_each_2": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            }
        }

When pasting the code below to change the values of SharePoint to yours. The connections should directly work. Also, based on the screenshots you should be able to replicate the solution directly within the designer of your Logic App.

Please click 'Accept answer' if you think my answer is helpful!

Feel free to drop additional queries in the comments below.

Kind regards,

Sonny

Answer 2

@Sonny Gillissen : Unfortunately it still doesn't work. Please let us leave out the lookup column, just let us focus on the person column.

It seems like in your first screenshot for the person column that you leave out the for each loop. Is this correct? I have tried to reproduce it the same way. I get no error in the run history, but there is still no email sending. You an see in the screenshot from the run history that the workflow did not take the true path of the condition (execution skipped).

I had to modify the coalesce again (from "items()" to "triggerbody()"), as I removed the for each loop:

"@coalesce(triggerBody()?['Zuordnung Person']?['Email'])"

Logic App workflow:

Run history: