Technical Articles

August 5, 2020 5 minute read

SAP Intelligent RPA – Convert MS Excel into PDF & Send Outlook Email

Introduction:

In this blog post we will see how to convert Microsoft Excel spreadsheet to PDF. Here considering the requirement which is like we have a daily report for a Sales Person in MS Excel. The same we are converting it into PDF format and attaching it to send Outlook email through SAP Intelligent RPA.

Steps to convert MS Excel into PDF & Send MS Outlook Email:

Create a new Microsoft Excel spreadsheet else open existing one.
Create a new text file write and save the body content of email in HTML format.
Create a new project in desktop studio.
Select excel and outlook integration from library.
Create a new work flow.
Drag and drop activities, save and build to generate the code.
Edit the generated code.
Save, rebuild and debug.
Click on bot.
Final output.

Prerequisites:

Microsoft Excel
Microsoft Outlook
Desktop Studio 1.0.8.36

1. Create a new MS Excel spreadsheet else open existing one:

Below is the screen shot of existing daily report for a sales person.

2. Create a text file in HTML format write and save the email body content:

Create a new text file write and save the body content of email in HTML format you want to send in email. Following is the sample of email body content. Here, the special characters will be replaced by a receiver name i.e. “Baba” We can seen in email as Hi Baba

<!DOCTYPE html>
<body>
<p>Hi @@@@,</p>
<p>Welcome to SAP Intelligent RPA!</p><br>
<p>SAP Intelligent Robotic Process Automation (RPA) accelerates digital transformation of
business processes by automatically replicating tedious actions that have no added value.</p>
<p>Regards,</p>
</body>
</html>

3. Create a new project in desktop studio:

Open the desktop studio > Click on File and click on new project.

Give the project name, title, client name and comments are optional and click on save.

4. Select outlook and excel integration from library:

Right click on script perspective and click on Include library script to select outlook and excel integration.

Select outlook and excel integration checkbox and click on save button.

5. Create a new work flow:

Right click on Global from work flow perspective and create a new work flow and give the name for work flow and click on save.

6. Drag and drop activities:

From Activities drag and drop 1. initialize excel activity, 2.Drag and drop open existing Excel file activity, 3. Drag and drop end Excel activity. 4. Finally, we have to drag and drop custom activity and rename it to Send Email as shown in below image.

Double click on Open existing Excel file activity from workflow and give the path of your MS Excel file where it is located.

Click on context tool window.
Right click and create a new folder. Rename it as storeData.
Right click on above folder and create a new item and rename it as eMailBody.
Click on Save button.
Finally click on build to generate the code.

7. Edit the generated code:

Here we have to give the path of our newly created MS Excel file or path of existing MS Excel file. In this scenario we can see in below code the path is given for daily report in MS Excel file and to convert into PDF. That path also have given in next line i.e. where to save PDF file.

// ----------------------------------------------------------------
//   Step: Open_existing_Excel_f
// ----------------------------------------------------------------
GLOBAL.step({ Open_existing_Excel_f: function(ev, sc, st) {
	var rootData = sc.data;
	ctx.workflow('ExcelToPdf', '49d47dc0-ea8b-40cb-8b48-096100e6e424') ;
	// Open existing MS Excel file
	ctx.excel.file.open("C:\\ABC\\HO\\DEF\\DailyReport.xlsx");
        // Save into PDF
	ctx.excel.file.saveAsPDF(0, "C:\\ABC\\HO\\DEF\\DailyReport.pdf");
	sc.endStep(); // End_Excel
	return;
}});
//--------------------------------------------------------------------

We have to give the path of our text file where we have written email body.
Special characters i.e. @@@@ what we have written in text file will be replaced by a receiver name called “Baba”.
Give the path of your converted PDF file. So that it will attach and send an email

// ----------------------------------------------------------------
//   Step: Send_Email
// ----------------------------------------------------------------
GLOBAL.step({ Send_Email: function(ev, sc, st) {
	var rootData = sc.data;
	ctx.workflow('ExcelToPdf', '78c603b7-a2d4-4fbe-9065-0b845f7d3d68') ;
	// Send Email
	ctx.fso.init();
	ctx.outlook.init();
		ctx.outlook.reset();// This makes the current email index as 0
		// Path of your text file where you have written email body
		var emailContent = ctx.fso.file.read('C:\\ABC\\HO\\DEF\\text.txt');
		// Log statement. This will show email body content in desktop debugger
		ctx.log('Email body is' + emailContent);
		//Special characters will be replaced by name Baba
	  rootData.storeData.eMailBody = emailContent.replace('@@@@', 'Baba');
		// Enter email id you want to send an email
		ctx.outlook.mail.create({To: 'abc@def.com',
		Subject:'Excel To PDF'});
		ctx.outlook.mail.setBodyHtml(0, rootData.storeData.eMailBody);
		// Give the path of your converted PDF file. So that it will attach and send an email
	  ctx.outlook.mail.attach(0, "C:\\ABC\\HO\\DEF\\DailyReport.pdf");
	  ctx.outlook.mail.show(0); //It helps to test without sending the actual mail.
	  ctx.outlook.mail.send(0);
	sc.endStep(); // end Scenario
	return;
}});

8. Save, rebuild and debug:

Once we done with the above code. Click on save, rebuild and debug.

9. Click on bot:

10.Final Output:

Receiver received an email. Below is screen shot of email and PDF file downloaded from email.

Conclusion:

This blog post should help you to understand use of outlook and excel library. Converting MS Excel file into PDF. Attach it. Send MS Outlook email through SAP Intelligent RPA.