none
Blob Storage Files as an attachment in the mail using LogicApps RRS feed

  • Question

  • Dear Team, 

    in our scenario, we have Blob Storage files (pdf, xls. docx etc) path stored in the database table column as comma separated values. 

    example table structure 

    Email, Sub, Body, Filestosend (Comma separated multiple files path), IsMailsent

    Let me know how do we implement the below scenario,

    As and when there a tables row updated, we wanted to loop through the ISmailsent false rowset and loop through to send the mail one more loop to attach the files from the  Filestosend (Comma separated multiple files path)

    The main challenge is to convert the comma separated  blob storage path as attachment in the mail. 

     


    Selvakumar Rathinam

    Monday, November 25, 2019 4:54 PM

All replies

  • Depending on the database, you would first need to have a way to know when a row is updated. In case of something like CosmosDB, you could use the change feed to trigger a function app which in turn would trigger your Logic App. OR you could have a recurring Logic App to query for rows with IsMailSent set to false.

    In your Logic App, you would split the FilesToSend value using the split functionloop over them (make sure to set concurrency to 1) and fetch the content for each using the Get blob content using path action and store them in an array with each item format like the following

    {
      "Name": "<NameOfFile>",
      "ContentBytes": "<Base64OfFileContent>"
    }
    

    Finally, you would use the Send an email (V2) action with the attachments.

    Here is a Logic App that handles looping over the CSV paths, fetches the blob content and sends the email with all the attachments

    image
    image

    And here is the workflow JSON for the same

    {
      "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
          "Blob_Paths_(CSV)": {
            "inputs": {
              "variables": [
                {
                  "name": "blob_paths",
                  "type": "string",
                  "value": "/container/somedata1,/container/somedata2,/container/somedata3"
                }
              ]
            },
            "runAfter": {},
            "type": "InitializeVariable"
          },
          "For_Each_Blob": {
            "actions": {
              "Attachment_Details": {
                "inputs": {
                  "ContentBytes": "@{base64(body('Get_blob_content_using_path'))}",
                  "Name": "@{last(split(items('For_Each_Blob'), '/'))}"
                },
                "runAfter": {
                  "Get_blob_content_using_path": [
                    "Succeeded"
                  ]
                },
                "type": "compose"
              },
              "Get_blob_content_using_path": {
                "inputs": {
                  "host": {
                    "connection": {
                      "name": "@parameters('$connections')['azureblob']['connectionId']"
                    }
                  },
                  "method": "get",
                  "path": "/datasets/default/GetFileContentByPath",
                  "queries": {
                    "inferContentType": true,
                    "path": "@{items('For_Each_Blob')}",
                    "queryParametersSingleEncoded": true
                  }
                },
                "runAfter": {},
                "type": "ApiConnection"
              },
              "Push_Details_to_Attachments_Array": {
                "inputs": "@union(variables('attachments_array'), createArray(outputs('Attachment_Details')))",
                "runAfter": {
                  "Attachment_Details": [
                    "Succeeded"
                  ]
                },
                "type": "compose"
              },
              "Update_Attachments_Array": {
                "inputs": {
                  "name": "attachments_array",
                  "value": "@outputs('Push_Details_to_Attachments_Array')"
                },
                "runAfter": {
                  "Push_Details_to_Attachments_Array": [
                    "Succeeded"
                  ]
                },
                "type": "SetVariable"
              }
            },
            "foreach": "@split(variables('blob_paths'), ',')",
            "runAfter": {
              "Init_Attachments_Array": [
                "Succeeded"
              ]
            },
            "runtimeConfiguration": {
              "concurrency": {
                "repetitions": 1
              }
            },
            "type": "Foreach"
          },
          "Init_Attachments_Array": {
            "inputs": {
              "variables": [
                {
                  "name": "attachments_array",
                  "type": "array",
                  "value": []
                }
              ]
            },
            "runAfter": {
              "Blob_Paths_(CSV)": [
                "Succeeded"
              ]
            },
            "type": "InitializeVariable"
          },
          "Send_an_email_(V2)": {
            "inputs": {
              "body": {
                "Attachments": "@variables('attachments_array')",
                "Body": "<p>It's Cool!</p>",
                "Subject": "Test Looping Attachments",
                "To": "<email-id-to-send-the-email-to>"
              },
              "host": {
                "connection": {
                  "name": "@parameters('$connections')['office365']['connectionId']"
                }
              },
              "method": "post",
              "path": "/v2/Mail"
            },
            "runAfter": {
              "For_Each_Blob": [
                "Succeeded"
              ]
            },
            "type": "ApiConnection"
          }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
          "$connections": {
            "defaultValue": {},
            "type": "Object"
          }
        },
        "triggers": {
          "manual": {
            "inputs": {
              "schema": {}
            },
            "kind": "Http",
            "type": "Request"
          }
        }
      },
      "parameters": {
        "$connections": {
          "value": {
            "azureblob": {
              "connectionId": "/subscriptions/<subscription-id>/resourceGroups/<resource-group-name>/providers/Microsoft.Web/connections/azureblob",
              "connectionName": "azureblob",
              "id": "/subscriptions/<subscription-id>/providers/Microsoft.Web/locations/westus2/managedApis/azureblob"
            },
            "office365": {
              "connectionId": "/subscriptions/<subscription-id>/resourceGroups/<resource-group-name>/providers/Microsoft.Web/connections/office365",
              "connectionName": "office365",
              "id": "/subscriptions/<subscription-id>/providers/Microsoft.Web/locations/westus2/managedApis/office365"
            }
          }
        }
      }
    }
    Tuesday, November 26, 2019 9:02 AM
    Moderator
  • Hi Selvakumar Rathinam - Hope my answer helps
    Tuesday, December 10, 2019 8:13 AM
    Moderator