How to grab column value from ADLS gen 2 csv file and use the column value in the body of the email,also send blob data as attachment to outlook mail

Question

Here is my Scenario, There will be a drop of csv file into blob storage every day ,that will be processed by my dataflow in ADF and generate a csv in output folder.

Now Using logic apps, I need to send that csv file (less than 10 mb ) as an attachement to customer via Outlook connector. Besides ,My body of the email must have dynamic value coming from that blob csv . For example 'AppWorks' is the column value in column 'Works/not'. Sometimes it may be "AppNotWorks".So How to handle this scenario in Azure logic apps

So, you want to send mail for each separate row where body would be the value of the column `Works/not`. Correct me if I'm wrong. — Saideep Arikontham, Feb 16 '23 at 10:52
For example i have a csv file in blob .I need to attach that file as an mail attachment via Outlook and also in the body of the Mail , I need to access the "CustomerName" Column Value which is changing on a daily basis but its only one similar value . — AzSurya Teja, Feb 16 '23 at 12:37
Example : Body of my mail: Hi [CustomerName] , Random description . — AzSurya Teja, Feb 16 '23 at 12:38
So, the entire column has same value which changes daily. Correct me if I'm wrong — Saideep Arikontham, Feb 16 '23 at 12:40

score 0 · Accepted Answer · answered Feb 16 '23 at 14:01

You can use the combination of both data factory and logic apps to do this. Use look up activity to get the first row of the file (Since the entire column value will be same, we can get the required value from one row).

enter image description here

Now use web activity to trigger the logic app. Pass the logic app's HTTP request URL to web activity. In the body, pass the following dynamic content:

@activity('Lookup1').output.firstRow

enter image description here

When you debug the pipeline, the logic app will be successfully triggered. I have given the Request Body JSON schema to get values individually. For the sample I have taken, it would look as shown below:

{
    "properties": {
        "customer": {
            "type": "string"
        },
        "id": {
            "type": "string"
        }
    },
    "type": "object"
}

Create a connection to storage account to link the required file.

Now, using the Outlook connector, send the Email.

The following is the entire Logic app JSON:

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Get_blob_content_(V2)": {
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['azureblob']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/files/@{encodeURIComponent(encodeURIComponent('JTJmZGF0YSUyZnNhbXBsZTEuY3N2'))}/content",
                    "queries": {
                        "inferContentType": true
                    }
                },
                "metadata": {
                    "JTJmZGF0YSUyZnNhbXBsZTEuY3N2": "/data/sample1.csv"
                },
                "runAfter": {},
                "type": "ApiConnection"
            },
            "Send_an_email_(V2)": {
                "inputs": {
                    "body": {
                        "Attachments": [
                            {
                                "ContentBytes": "@{base64(body('Get_blob_content_(V2)'))}",
                                "Name": "sample1.csv"
                            }
                        ],
                        "Body": "<p>Hi @{triggerBody()?['customer']},<br>\n<br>\nRandom description</p>",
                        "Importance": "Normal",
                        "Subject": "sample data",
                        "To": "<to_email>"
                    },
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['office365']['connectionId']"
                        }
                    },
                    "method": "post",
                    "path": "/v2/Mail"
                },
                "runAfter": {
                    "Get_blob_content_(V2)": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "manual": {
                "inputs": {
                    "schema": {
                        "properties": {
                            "customer": {
                                "type": "string"
                            },
                            "id": {
                                "type": "string"
                            }
                        },
                        "type": "object"
                    }
                },
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {
                "azureblob": {
                    "connectionId": "/subscriptions/xxx/resourceGroups/xxx/providers/Microsoft.Web/connections/azureblob",
                    "connectionName": "azureblob",
                    "id": "/subscriptions/xxx/providers/Microsoft.Web/locations/westus2/managedApis/azureblob"
                },
                "office365": {
                    "connectionId": "/subscriptions/xxx/resourceGroups/v-sarikontha-Mindtree/providers/Microsoft.Web/connections/office365",
                    "connectionName": "office365",
                    "id": "/subscriptions/xxx/providers/Microsoft.Web/locations/westus2/managedApis/office365"
                }
            }
        }
    }
}

The following is the resulting Mail image for reference:

How to grab column value from ADLS gen 2 csv file and use the column value in the body of the email,also send blob data as attachment to outlook mail

1 Answers1