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).

Querying Outlook is indeed native to Excel, using the Exchange connector.

--Nate

Chers amis,

Merci pour toutes vos explications. J'ai essayé par le connecteur Exchange mais malheureusement cela ne fonctionne pas. Voici ce que j'obtiens en résultat.

Je vous remercie pour aide.

Chers amis,

Je vous remercie pour votre aide mais je fais face à de curieuses difficultés de connexions.

Voici ce qu'il se passe lorsque j'utilise le connecteur Exchange : 

1 Je sélectionne le connecteur Exchange "A partir de Microsoft Exchange".

2. J'indique mon adresse E-mail Office 365 à laquelle Microsoft Excel est déjà connecté.

3. Je clique sur "Ok" et j'ai une nouvelle fenêtre qui apparaît immédiatement lorsque je clique sur l'une des tables (calendar ou mail ou meeting request ou people ou task), je clique sur "Se connecter" mais rien ne se passe :

4. Je clique sur : "se connecter sous un nom d'utilisateur différent" une nouvelle fenêtre apparaît indiquant mon compte Office365 je clique dessus :

5. Cela me renvoie sur la page suivante, indiquant mon compte Office365, je clique sur "Se Connecter" :

6. Cela me renvoie à nouveau sur une erreur d'authentification.

7. Je précise que mon logiciel Excel est parfaitement connecté à mon compte Office365 :

You need your Exchange account. Compte Exchange. I guess you are using outlook at your organization? So you have to use that.

But... Working with different accounts and organizational account setup is difficult at best. You probably need to contact your it departement...

Chers amis,

Grâce à votre aide j'ai trouvé d'où venait mon problème.

En fait la difficulté est que je n'ai pas de plan Business mais un abonnement Home.

Je ne savais pas qu'il fallait un abonnement Business avec Exchange.

Je vous remercie pour votre aide.