Datasets may be of great help to you if you have tables with data that you would like to use in your templates.
To make use of a dataset, you need to create it in Shared Email Templates (TXT and CSV files import is supported) or to connect an Excel table that you have on OneDrive or in SharePoint.
Click the button below to open or download a quick How to use datasets cheat sheet:
Download cheat sheet
Dataset and key column: Definitions
In Shared Email Templates, a dataset is a set of values organized into a table from which you can retrieve data for your email messages. An essential category of a dataset is a key column.
The key column is always the leftmost column of your dataset. It can also be called an 'identifier' column because there you'll keep values that help you identify the rows from which data will be taken.
It's very important that all values in your key column are unique, otherwise the app will always take the first row with the value you indicate, even if there are several duplicates of this value in other rows of the key column.
Create a dataset
In this section, you'll learn how to create a dataset from scratch (or import a TXT or CSV file) and how to connect an Excel table from OneDrive or SharePoint.
To start creating a dataset, right-click a folder and select New Dataset:
In your default browser, a new tab with the Shared Email Templates web app will open:
Give your dataset a name and select one of the three options:
The leftmost column of your dataset is always the key column. Start creating a table by typing the first value there:
You can rename the key column by entering a new name:
To add rows, click the plus sign as many times as many rows you need:
To add columns, click the plus sign as many times as many columns you need:
To save your dataset, click the Save icon:
On the Shared Email Templates pane in your Outlook, datasets are marked with a special icon:
Import datasets (TXT or CSV file)
You can import datasets to Shared Email Templates in TXT or CSV format. If your table is in another format, you can easily convert it to one of the required ones.
Open the file that contains an Excel table that you want to use as a dataset for your templates, click Save As, and pick one of the following formats: Text (Tab delimited), Unicode Text, or CSV (Comma delimited).
Right-click a folder and select New Dataset:
In the web app, click Simple:
Then click the Import icon:
Select your file and click Open:
Your table will be imported to Shared Email Templates. Don't forget to give it a name.
Note. If your original table exceeds the maximum size of a dataset in Shared Email Templates (which is 32 rows, 32 columns, and 255 symbols in each cell), the app will cut your data while importing.
Note. If you import a CSV file, the delimiters between cells must be commas. In a TXT file, the delimiter is a tab character.
Connect an Excel table
You can link an Excel table to a dataset. An Excel workbook can be located on OneDrive or in SharePoint. Before linking an Excel table, take the following points into consideration:
You can connect only datasets formatted as Excel tables. To format an Excel range as a table, select it and press Ctrl + T.
As soon as the workbook is connected, do not remove or move it, otherwise the add-in won't be able to find it and use in templates.
If you use a connected Excel table as a dataset for a team template, make sure that all the team members have access to the connected Excel workbook on OneDrive or in SharePoint.
If you connect an Excel table with more than 1,000 rows, it may slow down the add-in performance.
To connect an Excel table, take the following steps:
Depending on the location of your file, click Excel Table from OneDrive or Excel Table from SharePoint:
Pick the necessary workbook and click Open:
Choose a table and click Select:
There are two icons next to the name of the selected workbook: Open Workbook and Change Workbook. Use them if needed. On making sure that the right workbook and table are selected, name your dataset and click Save:
On the Shared Email Templates pane, a connected Excel dataset is marked with a small Excel icon:
On the Shared Email Templates pane in your Outlook, select a dataset that you want to edit and click the Edit in browser icon:
To edit the name of your dataset or change any value the dataset contains, simply click it and start typing. To add columns or rows, use the plus sign:
Note. The maximum size of a dataset is 32 rows, 32 columns, and 255 symbols in each cell.
Tip. To change the order of columns, you can drag and drop them. The only exception is the key column, it will always be the first one and can't be moved.
As soon as the necessary changes are made, don't forget to click Save.
Set default rows
If you use a template with a table bound to a dataset, you can get some rows of the dataset selected automatically when you insert the template into an email message. You just need to set those rows as default ones:
Open a dataset for editing in the web app.
Click the plus sign next to the rightmost column.
Type in Default Row as a title for the new column:
Note. The title is case-insensitive. It can be entered in any letter case, but the text should be unchanged. Otherwise, default rows won't be selected automatically.
Enter any symbol or text into the necessary cells in the Default Row column. When you paste the template into an email message, the key values from the rows you marked will be selected automatically.
Save the changes.
When inserting a template with a table bound to the dataset into an email message, you'll see that the default key values are checked in the dialog window:
In case you use the ~%WhatToEnter macro to paste a specific value from the dataset, the key value from the first default row will be preset in the macro dialog window:
Use datasets in templates
There are two ways to add a value from a dataset to a template: either put a placeholder and select a value while pasting a template into an email message or insert a fixed value from a dataset into a template. In the first case, make use of the ~%WhatToEnter macro. In the second case, rely on the ~%DatasetValue macro.
WhatToEnter: Add placeholders
In the template editor, put the cursor where you want to see a value from a dataset and click the Insert macro icon:
In the Search field, start typing "what to enter" to find the macro in the list. Select What to Enter:
Choose Dataset from the dropdown menu:
You'll see several fields to fill in:
It will be displayed in the What to enter dialog window when you paste a template into an email message. Enter something clear and understandable.
Choose the dataset that contains the necessary values.
Specify the column from which the required value will be retrieved.
Click the Insert button.
In the template, the macro placeholder will look like in the screenshot below:
Let's get some other data from the same sample dataset with the ~%WhatToEnter macro. The color of a bag is needed:
The size has to be specified as well:
Finally, the template will look like this:
Save the template and insert it into an email message. In the macro dialog window having the title that was specified as Window title, select the necessary value from the key column of the dataset and click OK:
Note. If some rows in your dataset are marked as default ones, the key value from the first default row is preset in the macro dialog window.
With the values retrieved from the dataset, your email message will look like this:
DatasetValue: Add fixed values
The ~%DatasetValue macro retrieves any value you need from the dataset that you specify.
Click the Insert macro icon and type in "dataset value" in the Search field. Select Insert Dataset Value:
Choose the necessary items from the dropdown menus that you'll see in the dialog window:
- Select dataset
Choose the dataset that the add-in will pull the necessary value from.
Choose the column from which the necessary value is going to be extracted.
Key value (value from the key column)
It indicates the row from which the necessary value has to be taken.
You can add several ~%DatasetValue macros to the template, and it will look like this:
When you paste such a template into the message body, the add-in doesn't ask to select anything and simply retrieves the values you specified:
Bind a table to a dataset
If you bind a table in your template to a dataset, the add-in will use specified key values to pull rows and fill in the table when you paste the template into an email message.
To add a table to a template, open the template in the Edit mode and click the Table icon on the toolbar:
You can learn more about tables in our How to create and format tables blog post.
Put the cursor in any cell of the row that you want to fill in with values from a dataset, right-click and select the Bind to dataset option:
Note. Reserve just one row for the values to be retrieved. After you bind a table to the dataset, you'll be able to insert as many rows as you need when pasting the template into an email message.
Enter the title for the window that will appear when you paste your template and choose the necessary dataset from the dropdown menu:
As soon as the Select dataset dialog window closes, the add-in fills in empty cells in the row with the names of columns from the specified dataset:
The ~%[ ] parameters with the column names populate the cells in the same order the columns are present in the dataset. In case of mismatching order, you can edit the ~%[ ] parameter by typing in the correct column name in the square brackets or you can copy the ~%[ ] parameter and paste it into the necessary cell.
Note. If there is some text in cells of the selected row, the ~%[ ] parameter won't be inserted automatically into these cells. You can add the ~%[ ] parameter with a column name manually.
Save the template.
When you paste such a template into an email message, you'll see the dialog window with the specified title and the list of key values from the selected dataset:
Select the necessary checkboxes and click OK.
If your dataset has default rows, their key values are already selected in the dialog window:
Tip. To quickly find the value of interest, start typing its name in the Search field.
Tip. If you want to add all rows from the dataset to your table, click the topmost checkbox and get all the list items selected:
A table filled in with the values from the selected rows of the dataset will appear in your email message:
Unbind a table from a dataset
To unbind a table from a dataset, right-click the bound row when editing your template and choose the Unbind from dataset option from the menu:
Bind a list to a dataset
Follow the steps below if you want any of your templates to contain a list bound to a dataset. This is an example of such a dataset:
Open your template in the Edit mode.
- Decide whether you want to get a bulleted list or a numbered one and click the corresponding icon on the toolbar (or click the arrow that is to the right of the icon of interest to choose from the options available).
- Right-click the line where the first list item is supposed to appear (to the right of the symbol that you already have) and select the Bind to dataset option:
In the dialog window, type the necessary title and pick the dataset your list has to be bound to:
A list item appears in your template. The header of the dataset column is in square brackets:
When pasting this template into an email message, you'll see a dialog window showing the values that the key column contains:
Pick any you want and click OK:
Tip. If all the values are needed, click the very first checkbox (next to the Search field). All the checkboxes will be selected:
The list inserted into the message body along with the template will contain the items you have selected:
Unbind a list from a dataset
To unbind a list from the dataset it's bound to, open your template in the Edit mode, right-click the list item, and select the Unbind from dataset option:
Visit our blog to discover more ways to use datasets in your daily routine: