Integrate Excel and Outlook SDK Based Automations To A Single Project

In your Outlook Sample project, add baseFolderPath as an environment variable.

Use this new path in Save All Mail Attachments.

To reuse this automation, release the Editable project. You can do so in two ways.

• Either you can do so from Lobby by choosing (…)
• From within the project you can do so by clicking the button Release

Another way to release the project.

At release time, it will ask you to specify a version number, you can accept the default suggestion.

Notice the name change from Editable to (version number) Released.

In your Excel project, add Get Folder Collection to the baseFolderPath environment variable. This step will return all the sub folder names within a given folder. In this case, it will give each timestamp based folders fetched from each email from the Outlook. Rename the output parameter to folderList.

You can see the folder name by using a Log Message.

Loop through each folder. For this will add For Each loop to the flow.

Add all the required steps into the folder. The idea is on each file, a certain set of actions needs to be performed.

Within each index of For Each, fetch list of Excel files in each folder. For this, add Get File Collection to the loop.

The value for Get File Collection is as given in the image.

Add one more For Each to loop through each Excel file in that sub folder. Here to distinguish both the loop variables, you can rename it appropriately. Also include the required Excel actions within this sub For Each loop.

First create a string variable and assign the loop variable’s full path to the Excel file.

Adjust the Get File Name to take the output variable.

This tutorial uses hard coded ranges in Filter Range. When you generalize the logic to your needs, you may want to calculate this at run time. For example, add Get UsedRange Row and Get UsedRange Column activities to find out the start and end cell values.

Now use a Custom script activity to calculate complete range.

var startColumn = 1;
var startCell = String.fromCharCode(startColumn + 64) + startColumn;
var endCell = String.fromCharCode(columnRange + 64) + rowRange;
return startCell + ":" + endCell;

Just for demonstration purpose, filter against the first column and look for cell values containing a.

Use similar approach to find out range of values after filtering to be used with Copy Range.

Append “_new” to the new CSV file name in Save As Workbook.

Like the Outlook sample, you need to release the package and publish to the library. You can do so either from the SAP Build Lobby or from within the project.

Create a brand new Automation project Main Automation.

Include both Excel and Outlook projects as dependency. Here you will add Excel Project. Similarly you have to add Outlook based project.

This is the result after adding both project’s packages as dependency.

Call this new project as Main Automation project.

Add automations from both Outlook sample and Excel sample projects to the flow.

Don’t be confused with the name. There are Package/Project Name(s) and Automation name(s). In this step, you will be using automations

Save and test the project.

At the end, after this tutorial is tested, the below source data was used.

After the automaton is executed, it has filtered the data (Town starting with a), hidden a column (Column F with title storey_range) and resultant Excel will look like this: