RAX Automation Suite

This tutorial aims to teach you to have RAX EDITOR interact with Excel, Word, and Gmail.  At the end of this tutorial you would be able to:

• Process individual data from a spreadsheet
• Create copies of files from an existing template
• Replace values on a Word document
• Send a personalized email with an attachment
• Update a spreadsheet after processing part of the workflow
• Loop a workflow based on a condition

In order to proceed with this tutorial,  you would need to meet the following requirements:

1. Microsoft Excel 2016 or higher
2. Microsoft Word 2016 or higher
3. Gmail account for sending the emails with attachment
4. Payroll Spreadsheet file. Download here.
   
   Important Note:
   Once downloaded please make sure to replace the email addresses on the Payroll Spreadsheet.xlsx document.
   Replace all of the email addresses with your email address to receive all of the email sent by the automation script.
   
   
5. Word Template file. Download here.

You would also need to update your Gmail settings to allow RAX EDITOR to send the email for you. You could do so by following these two steps:

1. Access your Gmail settings using this LINK.
2. Turn ON Less Secure Apps access:
   

After that you should be able to proceed without any issues!

    The Payroll Spreadsheet document contains the sample list of employees within the organization and the corresponding earnings and deductions broken down per category.

    The Word Template document will be filled up with the appropriate values specific for the employees and emailed to them at the end of the automation. You will notice that this document has been filled up with special characters, these are the placeholders which would be replaced as we go through each employee listed on the Payroll Spreadsheet.

Now, follow these steps to generate a payslip and send to the corresponding email using RAX EDITOR.

PART I. Preparation

1. Let us prepare the files that we would be needing for our Payslip workflow. First off, create a folder on your Desktop and name the folder Payslip.
   
   
2. Place the Word Template.docx and Payroll Spreadsheet.xlsx files inside the Payslip folder. The Payroll Spreadsheet would hold the different values that we would be placing on the Word document. You would notice that the Word document has different special characters, these characters would be replaced with the correct values once our workflow has been created.
   
   
   
3. Let us create two new folders inside the Payslip folder to store the Word Documents and PDF Documents that would be created during the automation process. Name the folders as Doc and PDF respectively.
   
   
   

PART II. Collecting the Values by Row from the Spreadsheet

1. Now that the folders have been created, let us open RAX EDITOR and create a new workflow. To do so, double click on the RAX EDITOR icon on your desktop. Once the application is open, click on the New button then browse for the Payslip folder in order to save the workflow that we would be creating inside that location. Name the workflow as Payslip Automation workflow.
   
   
   
2. Upon creating a new workflow, there would be a Sequence activity by default inside the workflow area. Name that Sequence as Main Sequence.
   
    
3. Add a new Sequence activity inside the Main Sequence. Rename the new activity as “Looping Sequence”.
   
   
   
4. Drag and Drop an Excel Sequence activity inside the Looping Sequence. On the File Path input box, click on the Browse button and choose the Payroll Spreadsheet.xlsx file inside the Payslip folder on your Desktop. This would designate it as the Excel file where we would be pulling the values from.
   
   
   
5. In order to get the values from a row on that Excel file, we would need to add a Read Row activity. Search for the Read Row activity and place it inside the Excel Sequence.
   
   
   
6. Let us declare a variable in preparation for our workflow to traverse each row of our Excel file. Click on the variable button to expand the Variable section then click on the Create Variable text. Let us set the Variable Name as row and set the Variable Type as Int32. Also set the Default value of the row variable to 2.
   
   
   
7. Now we would need somewhere to store the values collected from the Read Row activity. The required type of storage for Read Row activity is an IEnumerable<Object>. Let us create a new variable for this by clicking on the Create Variable button. Let us set the Variable Name as psData.
   
   
   
8. For the Variable Type, since the IEnumerable<Object> type is not shown by default, we would need to choose Browse for Types on the Variable Type dropdown list.
   
   
   On the Type Name input box, type ienumerable. This would filter the list and you should click on IEnumerable<T> under Systems.Collections.Generic.
   
   
   
   You should then see directly under Type Name the text System.Collections.Generic.IEnumerable< Dropdown button >. Click on the dropdown button and choose Object. Afterwards you click on the OK button.
   
   
   
9. Now that the row and psData variables have been prepared, let us go back to our Read Row activity by clicking on the said activity. On the activity’s Worksheet input box, type in “Payroll”. This is because on our Payroll spreadsheet.xlsx file, the values are on a worksheet tab named Payroll.
   
   
   
10. On the Row input box of the Read Row activity, type in row.ToString. We would be using the variable that we have previously declared as the value for the Row, which is currently set as 2. The reason behind it is that when you check our Payroll Spreadsheet.xlsx file, there is a header on the sheet and we would not really need those values hence we would begin on the second row.
    
    
    
11. Since you have the Read Row activity selected, you should be able to see the Read Row attributes on the right-most side of the RAX EDITOR application. Under the Output Category is an attribute named Values with an input box beside it. Type in psData. This would store all of the values pulled from the Read Row activity inside the psData variable.
    
    
    

PART III. Replacing the Values on the Word Document and PDF Creation

1. Data stored inside the psData variable could be accessed individually. Data stored would begin with 0. The first item stored, since we are currently on the second row of the Excel sheet, would be Kit Harington. This is designated as 0. The Employee Number would be 1, and so on and so forth until the last column whose value would be 46.
   
   For this workflow, we would create a new variable netPay with a Variable Type of String.
   
    
2. We would need to assign a value to netPay from our spreadsheet values. We could do so by dragging an Assign Activity outside and directly under the Excel Sequence. On the To input box, type netPay and on the Value input box, type in psData(45).ToString.
   
   
   
3. Let us add an If activity below the Assign Activity. On the Condition input box, type in
   
   netPay = “0”
   
   
   This is due to the assumption that the employees on the spreadsheet have been sorted in such a way that if the value on the Net Pay column of the spreadsheet is zero, these employees should not be receiving their pay slip and should be disregarded.
   
   
4. The If activity has two columns namely the Then and Else paths. The Then path would be the path taken if the condition is met.
   
   Since we know that if the netPay is equal to zero, our automation should already end, we would be creating another variable. Let us name the variable as automationDone with a Variable Type of Boolean and a Default value of FALSE.
   
   
   
5. We have to flag that the automation is done if in case the Then path is taken so we would add an Assign activity inside the Then path. On the To input box, type in automationDone. While on the Value input box, let us type in TRUE.
   
   
   
6. Now that we have finished the path if the condition is met, let us add in a Sequence activity on the Else path in order for us to handle if in case our original condition is not met.
   
   
   
7. On this path, we would need to save our Word document and PDF files inside the Doc and PDF folders that we created during Step 3. In preparation for that let us declare these directories by creating the following variables.
   
   filePathWord would be a String variable with a Default value of the Doc folder.
   filePathPDF would again be a String variable with a Default value of the PDF folder path.
   
   *Make sure to add a \ to the end of the file path. Example: "C:\Users\Username\Desktop\Payslip\Doc\"
   
   Always remember that strings should be enclosed in double quotes.
   
   
   
8. Let us declare a new variable named Name with a Variable Type of String.
   
   
   
9. Inside the Else path’s Sequence activity let us add an Assign activity. On the To input box, type Name and on the Value input box place psData(0).ToString.
   
   
   
10. Since we want to retain the original Word Template document let us add a File Copy activity.
    
    On the Source input box, browse for the location of the Word Template.docx file.
    While on the Destination input box, type in:
    
    filePathWord + Name + “_Payslip.docx”
    
    
    Upon execution when you finalize the workflow, this would make a copy of the Word Template document named Kit Harington_Payslip.docx inside the DOC folder for the initial part of the execution. Since Kit Harington is the first name on the initial row that we are processing.
    
    
11. Afterwards, we would now need to update the personalized Word document. Add a Word Sequence activity after the File Copy activity. The File Path would be:
    
    filePathWord + Name + “_Payslip.docx”
    
    
    
    
12. Inside the Word Sequence activity let us add in a Sequence activity. Let us rename that Sequence to Replacing Data Sequence.
    
    
    
13. Drag a Replace Text activity inside the Replacing Data Sequence. Copy the special character from the Word Template.docx and place it on the Search input box. Remember that this is a String and should be placed inside double quotes.
    
    Now on the Replace input box, type psData(0).ToString. Alternatively, you could use Name since the variable already contains the same value.
    
    
    This means that RAX EDITOR would look for that special character and replace that character with the text inside the Replace input box which happens to be the value pulled from the Excel document.
    
    
14. Add Replace Text activities inside the Replacing Data Sequence in order to search for each special character and replacing them with the specific value from the pSData variable.
    
    
    
    *Please note that the screenshot above does not show all of the Replace Text activities. This is just to provide you with a guide on how to proceed until you reach psData(45).ToString.
    
    
15. Once everything is done, we could now create a PDF copy of the document. Let us add in an Export to PDF activity inside the Word Sequence, directly below the Replacing Data Sequence.
    
    On the File Path input box, type
    
    filePathPDF + Name + “_Payslip.pdf”
    
    This would create a copy of the Word document that we have created in PDF format.
    
    
    
16. Now that the PDF and Word documents have been prepared, we would need to send the PDF file as an email to the employee whose email address is on the Excel sheet.
    
    Let us declare a password variable to store the email’s password. Let us name it as password and the Variable Type as String.
    
    
    
    
17. Add a Get Password activity below the whole Word Sequence. Type in your email’s password inside the Password input box. Please note that you no longer need to add double quotes when typing inside the Password input box so just type in your password directly.
    
    
    Afterwards, go to the Get Password’s attribute section on the right side of the application and inside the Result input box, type in password. That is the variable that we declared earlier to store the password.
    
    
    

PART IV. Sending the PDF Document as an Email

1. Let us add a Send SMTP Mail activity below Get Password.
   
   
   
2. On the To input box, type psData(2).ToString. This is where the email address located based on our spreadsheet.
   
   
   
3. Let us personalize the email that we would be sending specific to the name, on the Subject input box type in psData(0).ToString + “’s Payslip”
   
   Then on the Body input box, type
   “Dear “ + psData(0).ToString + “,” + Environment.NewLine + “Here is your payslip.”
   
   
   
4. Click on the Attach files button below the Body.
   
    
5. Click on Create Argument.
   Choose In for the Direction and String as the Type. For the Value input box, type
   filePathPDF + Name + “_Payslip.pdf”
   
   
   
6. Now for the Send SMTP Mail’s attribute section on the right side of the application. We would be using a Gmail account for this demo so let us set the following attributes
   
   Port: 587
   Server: "smtp.gmail.com"
   Email: *Please type in your Gmail address here as a String. Example “abc@gmail.com”
   Password: password
   From: Your Gmail address as a String. abc@gmail.com
   Name: Your name as a String. Example “George Martin”
   
   
   

PART V. Logging on the Spreadsheet

1. Once the email has been sent, we would need to update the Spreadsheet column in order to log that the email was sent to the recipient. Let us once again add in an Excel Sequence below the Send SMTP Mail. On the File Path input box, click on the Browse button and choose the Payroll Spreadsheet.xlsx file inside the Payslip folder on your Desktop.
   
   
   
2. Add an Update Cell activity inside the new Excel Sequence. On the Worksheet input box, type “Payroll”
   
   On the Range input box, type in “AU” + row.ToString
   
   For the Value input box, type in “Yes”
   
   
   
3. Now we would need to move to the next row on the spreadsheet, in order to do so, let us add an Assign activity, below the Update Cell activity, with the following values:
   
   On the To input box, type row
   While on the Value input box, please type row + 1
   
   
   
   

PART VI. Creating a Do While Loop to Process All Rows

1. Everything has been prepared to process the first row of the Spreadsheet, input the values on the Word document, create a PDF version of the file, Email the proper recipient, then update the Spreadsheet to reflect that the Email has been sent.
   
   
2. Let us Collapse the Looping Sequence workflow in order for us to add whole sequence inside a DoWhile activity. You could do so by clicking on the Collapse button of the Looping Sequence activity.
   
   
   This will compress the Looping Sequence for ease of moving the whole sequence.
   
   
   
3. You might be asking how we would be repeating the process for the other employees on the spreadsheet, we could do that by adding a DoWhile activity as the first activity inside the Main Sequence. Just add it at the top of the workflow.
   
   On the Condition area, type:
   
   automationDone = FALSE
    
    
4. Drag the whole Looping Sequence inside the Body of the DoWhile activity.   This would mean that as long as the automationDone variable would remain as FALSE, the automation would continue to process the rows of the spreadsheet.
   
   
   
5. That’s it! You may now execute the workflow by clicking on the Execute button or pressing F5 while the RAX EDITOR application is selected.
   
   

*Please note that once you have successfully executed the Payslip Automation workflow, you would have to delete the docx and PDF files inside the DOC and PDF folders before attempting to run it again to avoid errors on execution.

If you want to watch the video tutorial of this workflow CLICK HERE.