Read excel file and and send email based on email template

Hello,

I am new to UIPath and I require your help to be able to complete this task.

I have an sample excel file(see attached), I want to read the excel file, check licenses that will expire in 2 months time based on the current datetimestamp and then send out an email(using email template… see attached) to the owners advising them that their licenses will expire. A copy of the email should be send to the I.T. support email address.

Where we do not have owners email address, the email should be

Hello,

I am new to UIPath and I require your help to be able to complete this task.

I have an sample excel file(see attached), I want to read the excel file, check licenses that will expire in 2 months time based on the current datetimestamp and then send out an email(using email template… see attached) to the owners advising them that their licenses will expire. A copy of the email should be send to the I.T. support email address.

Where we do not have owners email address, the email should be sent to the Managers.

I can’t upload a sample file as new users are not allowed to upload file. However, below is a sample of how my excel file looks with its column name

applicationname Owner Name Owner email Expiry date ITSupport email Managers
Application 1 John Adams johnemailaddress 18/04/2019 Itsupportemailaddress manager1emailaddress
Application 2 Tony Abraham tonyemailaddress 18/04/2019 Itsupportemailaddress manager2emailaddresse
Application 3 Mekus Theophilus mekusemailaddress 18/06/2019 Itsupportemailaddress manager3emailaddress
Application 4 Jenny Parkinson jennyemailaddress 18/06/2019 Itsupportemailaddress manager4emailaddress

Sample of my emailtemplate:

Dear {Owner},

Your {Application Name} will expire in {expirydate}.

Can you please confirm iif you want the application renewed?

Regards
ITSUpport

@Arroba
Please follow below steps-

  • Use read range activity to read data from excel. The output will be a datatable
  • User for each row activity to iterate through the datatable
  • Inside the loop, add send email activity and update the properties from the row data.

Hope This is Helpful!

Hi @Arroba,

I hope this will meet your requirements. License_Mail.zip (18.7 KB)

Warm regards,
Nimin

Hi Nimin,

Thanks very much for the BOTS.
I will check it and get back to you.

Thanks once again and much appreciated.

1 Like

Hi nimin,

Thanks very much it is working but there has been a change in my requirement, I want all application that will expire email sent only to ITSupport.

Secondly I want IT support to receive only one email if they have multiple application expiring when the BOT runs?

Sample email should look like:

Inside the loop, append the application name to a variable, which will be later put in your email body.
After loop, outside, call send email activity passing the variable value.

Hi Madhavi,

If I have multiple application to send out in one email as per my earlier screenshot, can one variable hold all the application being passed by loop before it is send in the send email activity outside the loop?

attached is what I have so far and I only get one application expiring on my email even if there many expiring.

I have attached the file so that you can have a look?Application_License_Mail.zip (18.0 KB)

Yes… One variable can hold entire data.
I have updated your mail format to have a key and replacing that with the retrieved data-
Capture

Please find the updated workflow.
Application_License_Mail.zip (18.1 KB)

Hope this will be helpful! :slight_smile:
If this resolves your problem, please mark it as solution so people facing similar issues can directly redirected to the solution.

Hi Madhavi,

Thanks it worked.

How do I include a new line to the output.
Currently the output is:
image

I want the output to have a space after each application and expiry date…like below:

image

1 Like

Inside for each row, on the right hand side of the assign statement, replace the value with

string.Format(“{3}Application Name:{0}{1}Expiry date:{2}{1}{1}”,row(“Application Name”),Environment.NewLine,cdate(row(“Expiry Date”)).ToString(“dd/MM/yyyy”),strApplicationDetails)

{1} will be replaced with New line ‘Environment.NewLine’

Hi Madhavi,

Thanks very Much.
It worked.

Finally, Is there any function in UIPath that I can use to make the data coming from the datatable bold like below?

image

For this you need to send the content as HTML. In Send SMTP mail activity, check the IsBodyHTML property.
Now your input mail format should also be in html. Below is the updated one
Mail_Format.zip (276 Bytes)
In Assign, replace Environment.NewLine with Capture tag and add Capture tag wherever you need value to be bold

Capture

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.