Extract Email from outlook using Logic app

Question

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.

You can use Do Until loop and place Get emails (V3) in it to achieve this — Ikhtesam Afrin
– Ikhtesam Afrin, Commented Nov 26, 2024 at 11:43
@IkhtesamAfrin I tried below method, I am able to extract more than 25 records but it keep on loading the same top 25 records. — Hemant Kumar
– Hemant Kumar, Commented Nov 27, 2024 at 10:33
You can add the search query in Get Emails action which will be based on top and skip — Ikhtesam Afrin
– Ikhtesam Afrin, Commented Nov 27, 2024 at 10:36
Add it in Get Emails actions by enabling additional property — Ikhtesam Afrin
– Ikhtesam Afrin, Commented Nov 28, 2024 at 5:14

Ikhtesam Afrin · Accepted Answer · 2024-11-26 14:42:50Z

You can fetch more than 25 emails by using the below flow.

enter image description here

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.

enter image description here

Collectives™ on Stack Overflow

Extract Email from outlook using Logic app

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related