Automating Statement Sharing Using UiPath

[bhuvangates (Bhuvaneswaran B)]

Automating Statement Sharing Using UiPath

Use Case Description

The IndusInd Bank, Kancheepuram Branch, regularly sends monthly account statements to its customers. For June 2024, Mr. V. Devarajan, the Branch Manager, received PDF files of all the customers’ monthly statements, named as CustomerAccountNo.PDF (without password protected). His task is to open an Excel file (IndusIndKpm.xlsx) containing customer information, read each customer’s details, and individually share the password-protected PDF statements with the account holders. The password protection format for each PDF is the first 4 characters of the customer’s name in capital letters followed by their date of birth in DDMM format (e.g., for a customer named Bhuvaneswaran B born on 2nd November 2000, the password would be BHUV0211). Given the volume of statements and the urgency, Mr. Devarajan finds it challenging to complete the process on time. Seeking assistance, he approached Mr. B. Bhuvaneswaran, an RPA Developer in the IT Department, known for his expertise in UiPath. To streamline this process, an RPA bot can be designed using UiPath, which will open the Excel file, extract the necessary customer information (Customer Account Number, Name, Date of Birth, and Email ID), identify the corresponding PDF in the folder, add the required password to the PDF, compose an email with a customized message, and finally send the email with the attached password-protected PDF to the respective customer. This automation will significantly reduce manual effort and ensure timely delivery of the account statements to all customers.

Objective:

Automate the process of reading customer information from an Excel file, password-protecting the corresponding PDF statements, and emailing them to each customer with a customized message.

Prerequisites:

1. UiPath Studio installed.
2. Excel file (IndusIndKpm.xlsx) containing fields: Customer Account Number, Name, Date of Birth, and Email Id.
3. PDF files named as CustomerAccountNo.PDF stored in a designated folder.
4. A folder to save the password-protected PDFs.
5. SMTP server details for sending emails.

Body of the email:

Dear [Customer Name],

Thank you for Banking with us.

Please find attached herewith your account statement for the month of June 2024.

We have recently upgraded our statement generation platform to ensure seamless and timely delivery of your account statement to your registered email ID.

IMPORTANT: We understand that emails with large attachments often face delivery challenge due to file size restrictions imposed by email service providers. In order to ensure that the statements are regularly delivered irrespective of the size of the attachment, statements exceeding a predefined size shall be sent as a link embedded in the communication from the Bank.

How to open your statement:

• Access the email sent from the email ID ‘estatements@indusind.com’.
• If you have received an attachment, please click on attachment which shall prompt you for a password to access the statement.
• If you don’t see an attachment, your statements are accessible through a link. Simply click here to view your statement. On clicking the link, your password protected statement will get downloaded.
• Please note that in case you are accessing the link from a corporate domain having firewall, then it may require whitelisting of the URL by your organization.
  ** URL: https://ccms.indusind.com/

PASSWORD ACCESS TO YOUR STATEMENT

Your 8 character password is a combination of the first 4 characters of your Name in Capital letters followed by your Date of Birth in DDMM format.

Please note that your password is case sensitive.

This is a system generated mail and please do not reply to this mail.

AS-IS WORKFLOW, TO-BE WORKFLOW

Other information about the use case

Industry categories for this use case: BFSI

Skill level required: Advanced

UiPath Products that were used: UiPath Studio

Other applications that were used: Excel, PDF, SMTP / Outlook / Exchange

Other resources: -

What is the top ROI driver for this use case?: Improve customer experience