How to record the contents of an incoming email line by line in an Excel book.(Power Automate Desktop)
Introduce a flow to record the contents of each incoming e-mail (subject, date/time, body, sender's address, etc.) in an Excel book, one line per e-mail.
Robin(for copy and paste)
It can be copied and pasted into Power Automate Desktop.
For Outlook.
Outlook.Launch Instance=> OutlookInstance
Outlook.RetrieveEmailMessages.RetrieveEmails Instance: OutlookInstance Account: $'' MailFolder: $'''Inbox''' EmailsToRetrieve: Outlook.RetrieveMessagesMode.All MarkAsRead: True Messages=> RetrievedEmails
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Test\\mail.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
SET Counter TO 2
LOOP FOREACH CurrentItem IN RetrievedEmails
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CurrentItem.Date Column: 1 Row: Counter
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CurrentItem.Subject Column: 2 Row: Counter
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CurrentItem.Body Column: 3 Row: Counter
ON ERROR
END
Variables.IncreaseVariable Value: Counter IncrementValue: 1
END
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
For non-Outlook.
Email.RetrieveEmails.Retrieve IMAPServer: `` Port: 993 EnableSSL: True Username: `` Password: `` AcceptUntrustedCertificates: False MailFolder: `` MailsToRetrieve: Email.RetrieveMessagesMode.All MarkAsRead: True RetrievedEmails=> RetrievedEmails
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Test\\mail.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
SET Counter TO 2
LOOP FOREACH CurrentItem IN RetrievedEmails
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CurrentItem.Date Column: 1 Row: Counter
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CurrentItem.Subject Column: 2 Row: Counter
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CurrentItem.Body Column: 3 Row: Counter
ON ERROR
END
Variables.IncreaseVariable Value: Counter IncrementValue: 1
END
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
Flow creation steps
To target incoming Outlook mail, set up Launch Outlook Action and Retrieve email messages from Outlook Action.
For non-Outlook, set up Retrieve email messages Action.
Set up Launch Excel Action.
| Parameter | Value |
|---|---|
| Launch Excel | and open the following document |
| Document path | The path to the Excel book that records the email information. |
| Make instance visible | OFF |
| Open as ReadOnly | OFF |
Set up Set Variables Action.
| Parameter | Value |
|---|---|
| Variable | Counter |
| Value | 2 |
Set up For each and specify %RetrievedEmails% for Value of Iterate.
Set up as many Write to Excel Worksheet Action in For each as the number of information to be recorded.
| Parameter | Value | Remarks |
|---|---|---|
| Value to write | %CurrentItem.property% | Select a property. Outlook Properties. Non-Outlook Properties. Typical items to be recorded include. .Body：The body of the email, with tags intact if it is an HTML email. .BodyText：The body of the email, excluding tags if it is an HTML email. .Subject .From .To .Date |
| Column | The number of the column to be written. | Sequential numbers with column A as 1. Column A is 1, column B is 2… |
| Row | %Counter% |
For each, set up a Increase variable and add 1 to the counter variable.
Set up Close Excel Action.
| Parameter | Value |
|---|---|
| Before closing Excel | Save document |
---