Mail Outlook dans Excel avec PowerQuery

[Agreement6]

Bonjour,

Comment suivre les mails Outlook dans Excel avec PowerQuery ?

Merci

[Anonymous · Accepted solution]

Hi @Agreement6 ,

What @watkinnc said is correct, if you want to connect to Outlook you need to use the exchange connector
Please look at my test screenshots below to see if you are doing anything incorrectly during the connection process:
Get data > From Other Sources > From Microsoft Exchange:

Enter your Outlook email account and authenticate it:

Then you can select the table you need:

And the final output:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Agreement6]

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.

[PwerQueryKees]

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

[Agreement6]

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 :

[Anonymous · Accepted solution]

Hi @Agreement6 ,

You'll need a subscription that already includes Exchange, which is one of the Business plans.
This case which has been solved may be helpful to you:
Solved: We Couldn't Authenticate With The Credential Provi... - Microsoft Fabric Community

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Agreement6 ,

What @watkinnc said is correct, if you want to connect to Outlook you need to use the exchange connector
Please look at my test screenshots below to see if you are doing anything incorrectly during the connection process:
Get data > From Other Sources > From Microsoft Exchange:

Enter your Outlook email account and authenticate it:

Then you can select the table you need:

And the final output:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Agreement6]

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.

[Einomi]

@Agreement6 

Que se passe-t-il si tu cliques sur [Table] à côté de Mail dans Power Query, c'est censé ouvrir tes mails
J'ai un fichier Excel qui se connecte directement à ma boîte mail

[Anonymous]

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

--Nate

[Shravan133]

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