Extract Email from outlook using Logic app
I am trying to extract all my email information such as ID, receivedDatetime, Internetmessageid, from, to, attachment, CC, BBC etc in a SQL Table. I created below flow using Logic app
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"contentVersion": "1.0.0.0",
"triggers": {
"Recurrence": {
"type": "Recurrence",
"recurrence": {
"interval": 1,
"frequency": "Day"
}
}
},
"actions": {
"Get_emails_(V3)": {
"type": "ApiConnection",
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['office365-1']['connectionId']"
}
},
"method": "get",
"path": "/v3/Mail",
"queries": {
"importance": "Any",
"fetchOnlyWithAttachment": false,
"folderPath": "Id::AAMkADU1NzM2NmJiLWUzOGItNDlmZS1iNjc3LWFkYzIzYmMzY2M4NgAuAAAAAAA8vBvBB693S5_H10o_0OuyAQD3AekO3P8LQpKHquyOHAMyAAAAAAEMAAA=",
"fetchOnlyUnread": false,
"fetchOnlyFlagged": false,
"includeAttachments": false,
"top": 25
}
},
"runAfter": {},
"metadata": {
"Id::AAMkADU1NzM2NmJiLWUzOGItNDlmZS1iNjc3LWFkYzIzYmMzY2M4NgAuAAAAAAA8vBvBB693S5_H10o_0OuyAQD3AekO3P8LQpKHquyOHAMyAAAAAAEMAAA=": "Inbox"
}
},
"For_each": {
"type": "Foreach",
"foreach": "@body('Get_emails_(V3)')?['value']",
"actions": {
"Insert_row_(V2)": {
"type": "ApiConnection",
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['sql']['connectionId']"
}
},
"method": "post",
"body": {
"ReceivedDateTime": "",
"HasAttachments": "",
"InternetMessageID": ""
},
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('****'))},@{encodeURIComponent(encodeURIComponent('***'))}/tables/@{encodeURIComponent(encodeURIComponent('[POC].[OutlookEmailData]'))}/items"
}
}
},
"runAfter": {
"Get_emails_(V3)": [
"Succeeded"
]
}
}
},
"outputs": {},
"parameters": {
"$connections": {
"type": "Object",
"defaultValue": {}
}
}
},
"parameters": {
"$connections": {
"value": {
"office365-1": {
"id": "***",
"connectionId": "**",
"connectionName": "office365-1"
},
"sql": {
"id": "**",
"connectionId": "***",
"connectionName": "sql-6",
"connectionProperties": {
"authentication": {
"type": "ManagedServiceIdentity",
"identity": "
}
}
}
}
}
}
}
Using GetMail(V3) action, i can only extract max of 25 records as it is the maximum limit. I have more than 1000 mails. How can i extract all of them. Can someone help me on this.
Solution
You can fetch more than 25 emails by using the below flow.
Here Do Until loop is executing on greaterOrEquals(variables('TotalFetched'),variables('TargetCount')) condition.
Code-
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"contentVersion": "1.0.0.0",
"triggers": {
"When_a_HTTP_request_is_received": {
"type": "Request",
"kind": "Http"
}
},
"actions": {
"BatchSize": {
"runAfter": {},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "BatchSize",
"type": "integer",
"value": 25
}
]
}
},
"TotalFetched": {
"runAfter": {
"BatchSize": [
"Succeeded"
]
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "TotalFetched",
"type": "integer",
"value": 0
}
]
}
},
"Until": {
"actions": {
"Get_emails_(V3)": {
"type": "ApiConnection",
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['office365']['connectionId']"
}
},
"method": "get",
"path": "/v3/Mail",
"queries": {
"importance": "Any",
"fetchOnlyWithAttachment": false,
"folderPath": "Inbox",
"fetchOnlyUnread": false,
"fetchOnlyFlagged": false,
"includeAttachments": false,
"top": 25
}
}
},
"Increment_variable": {
"runAfter": {
"Get_emails_(V3)": [
"Succeeded"
]
},
"type": "IncrementVariable",
"inputs": {
"name": "TotalFetched",
"value": "@variables('BatchSize')"
}
}
},
"runAfter": {
"TargetCount": [
"Succeeded"
]
},
"expression": "@greaterOrEquals(variables('TotalFetched'),variables('TargetCount'))",
"limit": {
"count": 60,
"timeout": "PT1H"
},
"type": "Until"
},
"TargetCount": {
"runAfter": {
"TotalFetched": [
"Succeeded"
]
},
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "TargetCount",
"type": "integer",
"value": 100
}
]
}
}
},
"outputs": {},
"parameters": {
"$connections": {
"type": "Object",
"defaultValue": {}
}
}
},
"parameters": {
"$connections": {
"value": {
"office365": {
"id": "/subscriptions/0e89****e7c/providers/Microsoft.Web/locations/eastus2/managedApis/office365",
"connectionId": "/subscriptions/0e8*****b5e7c/resourceGroups/DefaultResourceGroup/providers/Microsoft.Web/connections/office365",
"connectionName": "office365"
}
}
}
}
}
Loop executes 4 times to fetch 100 emails.
- How do you get a Key Vault secret using inline ‘CSharp Script Code’ In an Azure Workflow?
- Equivalent Connector for Create Block Blob in Azure Standard Logic App
- Workflow validation failed for the workflow 'shiptest'. {"error":{"code":"WorkflowTriggerTypeUnsupported",
- How can you remove the odata.etag when using the Get Entities action with an Azure Table?
- Graph Pagination in Logic Apps
- Logic App Standard: extension bundle doesn't support connecting to the storage account using Managed Service Identity
- Filter an array in Azure Logic Apps
- Logicapp designer in visual studio code becomes entangeled
- Azure Logic App - Update o365 profile image
- Extract Email from outlook using Logic app
- Updating azure Storage Account table through Logic Apps
- How to select data from a collection?
- Logic App AuthorizationFailure - vnet integration needed
- Use Salesforce Connectors from Hosted Azure Logic App in VSCode?
- Azure logic apps encode left bracket in json query param names to ~2. How can I rewrite with concat and/or replace functions?
- Azure Logic App Local Debugging - Getting DirectApiAuthorizationRequired Error
- How to handle null value in logic app condition module
- Add properties to an object in a variable in Azure Logic Apps
- Workflow Validation Failed Error: The function cannot be called from a logic app. It must not have a custom route
- In an AZURE Function how to throw an error to be caught in the Azure Logic AP
- Cannot get AAD auth token in Logic Apps
- Parameterising connections.json in Azure Logic Apps(Standard) for deployment via Azure DevOps
- Logic Apps: How to use create a new watchlist with data (raw content) module
- Add extra attribute to object Logic app
- How to hide or change legend in "Run query and visualize results" logic app function?
- Azure Logic App Authorization Policy set via Bicep
- Azure Event Grid Graph API SharePoint Event info useful?
- Azure Logic App Function call for non json body
- Publish Failure Reporting Add or Build Definition File
- Azure Logic App returns metadata instead of SQL query results when invoked via HTTP trigger