Skip to main content

Celonis Product Documentation

Send Excel File via Mail

Sending csv files via mail is fine but sometimes you need to send a .xlsx file instead. That's the reason we provide you with a workaround that fills an existing Excel worksheet with a table in the Action Flow, downloads it, sends it via mail, and resets the Worksheet again to be used for a new table automatically.

Follow the step-by-step guide below to implement this solution for your Celonis Action Flows use case.

Note

Unfortunately, the Microsoft 365 Excel API does not allow to create a new Excel file through the API endpoint, which is why this template has to take a different approach.

Sample Action Flow

The image below shows a working Action Flow which:

  • Create a Microsoft 365 Excel Table from scratch with a custom header

  • Fill Excel table with Celonis Data

  • Send data both as an HTML table in-line and as an Excel file via mail

60362010.png
Before you start

Please create a Folder in your OneDrive and add a new Excel Workbook with a Worksheet. You will need the names of the OneDrive Folder, the Excel File and the Worksheet within the Action Flow. The automation is more secure and robust if only one file is in this specific folder.

60362011.png

Microsoft Excel Connection

It is very important that you use the same connection to Microsoft 365 Excel that was used to create the original Workbook in order to make sure that you have access to that file.

How to set up the Connection - Microsoft 365 Excel - Connection

In this case:

Folder → TEMPLATE

File → Template_File

Worksheet → Template_Sheet (empty in the beginning)

60362048.png
Configuring Action Flow

Below you will find the step-by-step guide for configuring each module of the above Action Flow.

1. Customize Variables

CHANGES REQUIRED

60361847.png

Configuration:

Action Flows Module: Tools

Action: Set multiple Variables

FolderName: The Name of the Folder where your preconfigured sheet lays

Sheet Name: The Name you gave the Sheet

TableName: A distinct name for the table within the Sheet (does not need to be changed)

# Columns: The amount of columns the table will have

DesiredFileName: The filename which should be displayed in the mail (+.xlsx)

.xlsx

Do not forget to add the suffix .xlsx otherwise the Excel file won't be displayed as an excel file.

The name of the file itself in the Excel Workbook is not required as a variable.

60362052.png
2. Map Cell to Amount of Columns

CHANGES OPTIONAL

This is a pre-configured module that maps the number of columns you want to have to the right cell in the excel sheet. If you need to have more than 26 columns (NOT ROWS) you need to adjust the module and add a mapping for your amount of columns.

60361848.png

Configuration:

Action Flows Module: Tools

Action: Switch

60361872.png
3. Find Workbook

CHANGES REQUIRED

In this module we look for the preconfigured Excel file in the defined folder

60361849.png

Configuration:

Action Flows Module: Microsoft 365 Excel

Action: Search Workbooks

Connection: Set Up Connection that has to be selected in each of the subsequent modules

Folder: {{FolderName}}

Limit: 1 (we just want to use this worksheet we preconfigured)

60361861.png
Route 1 (A)
4. (A) Search old Tables

CHANGES OPTIONAL

In this module, we search for old tables within our Worksheet.

60362016.png

Configuration:

Action Flows Module: Microsoft 365 Excel

Action: List Tables

Workbook ID: {{id}}

Worksheet Name: {{SheetName}}

Limit: 10

60362017.png
5. (A) Reset old Tables

CHANGES OPTIONAL

In this module, we delete old Tables to start with a clean Worksheet when adding new content.

60362018.png

Configuration:

Action Flows Module: Microsoft 365 Excel

Action: Delete a Table

Workbook ID: {{id}}

Table Name: {{Name}} from previous module

Limit: 10

60362022.png
6. (A) Error Handling (Resume)

CHANGES OPTIONAL

This module makes sure that possible errors of the previous deletion are caught and Route B is started, e.g. if there is nothing to delete in the first run.

60362023.png

Configuration:

Action Flows Module: Flow Control

Action: Resume

Please make sure to add this element as an error handler by right-clicking on the previous module and selecting the "Resume" directive.

60362024.png
60362025.png
Route 2 (B)
4. (B) Add Tableheaders

CHANGES REQUIRED

In this module we are defining the table headers by adding a row with the headers as values in the worksheet. The file has to match with the values that will be exported in the Celonis - Query Data module.

60361850.png

Configuration:

Action Flows Module: Microsoft 365 Excel

Action: Add/ Update a Worksheet Row (Advanced)

Workbook ID: {{id}}

Worksheet Name: {{SheetName}}

Row Range: A:{{output}} (The output was set in the switch module to make sure we have the right amount of cells for our columns)

Row ID: 1

Type of Values Being Entered: Values

(required) Row: please add here the header names of the Columns (e.g. VBELN, VDATU...)

60361862.png
5. (B) Create Table with Headers

CHANGES OPTIONAL

In this module we are creating a new Table using the previously defined Rows as Headers for the new Table.

60361851.png

Configuration:

Action Flows Module: Microsoft 365 Excel

Action: Add a Table

Update a Table: Enter manually

Workbook ID: {{id}}

Worksheet Name: {{SheetName}}

Has Headers: Check the box to include the predefined headers

Address: A1:{{output}}1

60361863.png
6. (B) Update the Table

CHANGES OPTIONAL

We define the Styling of the Table here and make sure the table is created properly to be accessible in the following modules.

60361852.png

Configuration:

Action Flows Module: Microsoft 365 Excel

Action: Update a Table

Update a Table: Enter manually

Workbook ID: {{id}}

Table Name: {{name}}

Name: {{DesiredTableName}}

Style: Table Style Light 1

60361864.png
7. (B) Get Input Data for Table

CHANGES REQUIRED

Request the Data you want to fill in the Table

60361853.png

Configuration:

Action Flows Module: Celonis

Action: Query Data

Data Pool: choose the Data Pool of your choice

Data Model: choose the Data Model of your choice

Columns: Query the columns you need - make sure it is the same amount as defined before

Row Limit: adjust the row limit to your needs, it will be the maximum of rows you have in your excel table

60361865.png

Warning

Disconnect 7(B) - Get Input Data for Table from 8(B) - Fill the Table, run the entire action flow up to that point. After running it once, reconnect the two flow parts and configure the subsequent modules.

8. (B) Fill the Table

CHANGES REQUIRED

This will be the most complicated part of the Template as you have to set up this module and can't touch it afterward or the flow will break, so make sure to set it up right. In case you accidentally open it again you need to do the step described in the previous section colored in red and the steps of this module again.

60361854.png

Configuration:

Action Flows Module: Microsoft 365 Excel

Action: Add a Table Row

Workbook: Open the right Workbook (the preconfigured Excel file) by selecting from the suggestions by the module

Worksheet: Fill in the right Worksheet

Table: choose the table which was created by the run once of the Action Flow

If you have problems finding the table click on the reload of worksheet and table (the arrows next to the map toggle)

Row: Fill in the columns with the data you queried in the module before.

Row ID: <empty>

60362040.png
9. (B) Wait until Rows are filled

CHANGES OPTIONAL

We need the Sleep module to make sure the rows are added properly before we download the File.

60361855.png

Configuration:

Action Flows Module: Tools

Action: Sleep

Delay: 5

60361867.png

API Synchronization

Please do not delete this module as it makes sure that the Microsoft Excel API can synchronize the table and does not skip a value. We will update this issue as soon as Microsoft has provided a solution.

10. (B) Prepare Download

CHANGES OPTIONAL

We need this module to combine all bundles of query data into one bundle through aggregation.

60362028.png

Configuration:

Action Flows Module: Tools

Action: Text Aggregator

Source Module: Query Data

Text:

<tr>

<td>{{VBELN}}</td>

<td>{{formatDate(VDATU; "DD.MM.YYYY")}}</td>

<td>{{NAME1}}</td>

</tr>

Note

Although the output is not required, please make sure to keep this aggregation module as we have to combine all Query Data bundles into one bundle to only send one email per recipient.

60362029.png
11. (B) Download the Workbook

CHANGES OPTIONAL

In this step we download the created table to be able to send it via email.

60361858.png

Configuration:

Action Flows Module: Microsoft 365 Excel

Action: Download a Workbook

Download a Workbook: By entering an ID manually

Workbook ID: {{id}}

60361869.png
12. (B) OPTIONAL - Create HTML table

CHANGES OPTIONAL

We create a complete variable that contains the possible HTML table for the email. This step is entirely optional when you want to show the table also as HTML in the email.

60362030.png

Configuration:

Action Flows Module: Tools

Action: Set Variable

Variable name: <TableNameofYourChoice>

Variable value:

<table style="width:100%">

<tr>

<th>Sales Order</th>

<th>Request Delivery Date</th>

<th>Customer</th>

</tr>

{{text}}

</table>

60362031.png

Note

This module is entirely optional, but you could send the table both in the email and as a file for further use.

13. (B) Send Email + Workbook

CHANGES REQUIRED

We will send an email attaching the Excel file.

60362032.png

Configuration:

Action Flows Module: Email

Action: Send an Email

To: choose recipients of your choice

Subject: choose a Subject of your choice

Content Type: HTML

Content:

<html>

<head>

<style> table, th, td {border: 1px solid black;border-collapse: collapse;} </style>

</head>

<p>Dear [Recipient] </p>

<p>Please find attached the [TABLE]</p>

<p>Further details</p>

<p>Best regards,</p>

<p>[Sender]</p>

{{TableForEmailView}}

</html>

Attachments: (see below)

  • Data: {{data}}

  • File name: {{DesiredTableName}}.xlsx

60362055.png

Map Attachment Data

In order to have the attachment shown correctly in your email, every time you make changes to the email content, you have to repeat the steps on the right:

  1. Add an Attachment

  2. Select Source File - "Excel - Download a Workbook"

  3. Select Source File - "Map" (prefills with the output variables from Excel download)

  4. Set File Name to your {{DesiredFileName}}.

  5. "Add"

  6. "OK" in Email

60362037.png
60362038.png
60362039.png
Test the Action Flow

Note

Please test the Action Flow before using it. Send Emails to your own account and check for right values.

  1. If Step 8(B) fails, please disconnect Celonis - Query Data and Excel - Fill the Table, let the Action Flow run once and reset the values in 8(B) according to the warning in this documentation.

  2. If the email only has one line filled, please repeat Step 13(B) and reattach the excel workbook.

Downloading Action Flow blueprints

To download blueprints for Action Flows, go to Marketplace. In the Marketplace page for your Action Flow, click the Download button to get the blueprint.

Blueprints can be imported into your Celonis Platform Team so that you can quickly make the required changes without needing to build the Action Flow from scratch.

Potential Alternatives

You can also create a table with Google Sheets and send a link to your email recipient.

Possible Use Cases
  • Send on-time delivery report to customer service

  • Notify employee about PO/invoice mismatch

  • Inform master data team about frequent master data adjustments

  • Notify clerk about invoice with cash discount approaching

  • Notify when duplicate invoice detected

  • Send outstanding payment report to clerks