Mail Outlook dans Excel avec PowerQuery

Tracking Outlook emails in Excel or Power BI using Power Query involves connecting to your Outlook mailbox and extracting relevant email data. This process is typically performed using the Outlook API or an intermediary service that can access your email data. Here’s a detailed guide on how to achieve this:

1. Using Power Query in Excel

Note: Direct integration of Outlook emails in Power Query is not supported natively in Excel. Instead, you can use VBA (Visual Basic for Applications) to extract email data and then load it into Excel. Here’s how:

Step 1: Use VBA to Export Emails to Excel

1. Open Excel.
2. Press Alt + F11 to open the VBA editor.
3. Insert a New Module:
• Go to Insert > Module.
4. Paste VBA Code:
• Use the following VBA code to extract email data from Outlook and write it to Excel:

Sub ExportEmailsToExcel()

    Dim olApp As Object

    Dim olNs As Object

    Dim olFolder As Object

    Dim olMail As Object

    Dim i As Integer

    Dim ws As Worksheet

    ' Create a new worksheet

    Set ws = ThisWorkbook.Sheets.Add

    ws.Name = "EmailData"

    ws.Cells(1, 1).Value = "Subject"

    ws.Cells(1, 2).Value = "From"

    ws.Cells(1, 3).Value = "Received Time"

    ws.Cells(1, 4).Value = "Body"

    ' Initialize Outlook application

    Set olApp = CreateObject("Outlook.Application")

    Set olNs = olApp.GetNamespace("MAPI")

    Set olFolder = olNs.GetDefaultFolder(6) ' Inbox

    ' Loop through emails

    i = 2

    For Each olMail In olFolder.Items

        ws.Cells(i, 1).Value = olMail.Subject

        ws.Cells(i, 2).Value = olMail.SenderName

        ws.Cells(i, 3).Value = olMail.ReceivedTime

        ws.Cells(i, 4).Value = olMail.Body

        i = i + 1

    Next olMail

    ' Cleanup

    Set olMail = Nothing

    Set olFolder = Nothing

    Set olNs = Nothing

    Set olApp = Nothing

End Sub

1. Run the VBA Code:
• Press F5 to run the code and export email data to Excel.
2. Save the Excel File:
• Save your Excel file with the extracted email data.

Step 2: Load Data into Power BI

1. Open Power BI Desktop.
2. Click on Get Data.
3. Choose Excel and select the file where you saved the email data.
4. Load the Data:
• Select the relevant worksheet and load the data into Power BI for further analysis.

1. Using Power Query in Power BI

Direct access to Outlook data is not supported natively in Power Query either. However, you can use Microsoft Graph API or third-party connectors to fetch email data.

Option 1: Microsoft Graph API

1. Register an App in Azure AD:
• Go to the Azure portal.
• Register a new application to get the Client ID and Secret.
2. Use Microsoft Graph API to Access Emails:
• You will need to set up API permissions for accessing Outlook emails.
• You can use Microsoft Graph API endpoints like /me/messages to get email data.
3. Connect Power BI to Microsoft Graph API:
• Use Web connector in Power BI to call the Graph API and pull email data.
• You might need to handle authentication and API request formatting.
4. Transform and Load Data:
• Use Power Query to transform the JSON response and load the data into Power BI.

Option 2: Third-Party Connectors

1. Explore Third-Party Tools:
• There are third-party tools and connectors like Power Automate or Zapier that can help automate the extraction of email data from Outlook to a storage solution like SharePoint or SQL Server.
2. Set Up a Flow:
• Use Power Automate to create a flow that extracts email data and stores it in a database or file accessible by Power BI.
3. Connect Power BI to Data Source:
• Use Power BI to connect to the data source where email data is stored (e.g., SQL Server, SharePoint).