Integrate Excel and Outlook SDK Based Automations To A Single Project
- How one automation in a project can be embedded into another automation project
- How one automation can call another automation,
- How to pass values from one automation to another
- How you can build an integrated business process using SAP Build Process Automation
- Complete the tutorial: Extend Outlook SDK based automation
- Complete the tutorial: Build Your First Automation Using Excel SDK of SAP Build Process Automation
SAP Build Process Automation has native integration to several Microsoft Office products including Outlook, Excel SDK. In this tutorial, you will integrate the automations to a single business process. Create a new automation that includes both the Outlook and Excel automations. Use a combination of environment variables and input/output variables to pass information across the project and in this way, processing will be dynamic and can work for any email or Excel file.
- Step 1
In your Outlook Sample project, add
baseFolderPathas 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.
- Step 2
In your Excel project, add Get Folder Collection to the
baseFolderPathenvironment 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
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 Rowand Get
UsedRange Columnactivities to find out the start and end cell values.
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.
- Step 3
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:
Which activity you will use to add custom code?