Match Order Date to Today's Date and Send Email

Build
, ,
#1

Hello,

I have an excel database tracker on which multiple users enter “Order Confirmed” Date on a real time basis. At the end of the day, the bot (Confirm2PI - MainOrder2ShipQuestion.xaml (32.9 KB) ) Daily File.xlsx (11.2 KB) Main.xaml (8.5 KB) will check the file and wherever the Order Date is the same as today, it will mark a “Yes” in column G “Billing Details Requested”. After this it will send an email to the respective POC with subject “Client Name” “Equipment Ordered” Attention “POC First Name” Billing Details Requested, append an excel attachment and write a template message and send the email.

I am able to build the end to end bot if I want to do this just for one row. However, I am stuck when I want to build this for multiple rows (let us say Rows 3 to 10)

Questions:

  1. I have attached the excel file and the bot for multiple rows so far. Why am I not getting a “Yes” in column G even when the Order Date is today. I tried checking the format in which date shows - i.e 11/15/2019 using a message box and using that format in Row 5 and 6 but it still returns a No. What am I doing wrong.

  2. If I want emails to be sent to every with the subject relevant to that client - how do I build that? Should I be using “Read Range” for each column and storing output in variables and the using the variable names to specify the subject line? As a reference I have also attached the bot that I did for a single row (Order2Ship). Please bear with me - these are the very first bots that I have built.

2 Likes
#2

  1. When you assign todays date as Now.Date, the format you will get is 11/15/2019 00:00:00 but in the excel if you get the date as 11/15/2019, so they won’t match. So you have to format variable to todaysDate.toShortDateString.
    Try matching the cell value and variable value again, you will get an “YES”

  2. There are multiple ways to deal this, the easiest would be. Having an column “Subject” in the excel sheet and use a read range to read the whole sheet.
    Use a for loop and in the send email activity (any one), assign subject as row(“Subject”).toString and to, cc as necessary.
    Then for every row, the subject will change depending on the column value for that particular row and for different clients.

Hope this helps.

1 Like
#3

Thank you for your reply. So this will not do the trick in the if condition. I am shortening the date.

image

2 Likes
#4

hi @Rupna80
try this

Cdate(row(“ColumnName”).ToString).equals(Today.ToShort DateString)

Thanks
Ashwin S

1 Like
#5

Fine
Let’s go one by one
Hope this would help you
For the first query if we are getting this as a output date format stored in a variable named str_input then to compare with today date in a IF condition like this

DateTime.ParseExact(str_input.ToString.SubString(0,10),”MM/dd/yyyy”,System.Globalization.CultureInfo.InvarianCulture) > Now
If true it will go to THEN part where can we update the column value with a assign activity like this
row(“yourcolumnname”) = “No” or “Yes” based on your condition
So this will check and update in all the rows one by one

Then finally for this

Once after getting the datatable with READ RANGE named dt pass that variable as input to FOR EACH ROW activity
—inside that only we will be validating as per the above steps
—along with that get the individual values and assign it with a variable
Now we can use SEND OUTLOOK MAIL ACTIVITY inside the for each row loop itself with appropriate variables passed as input
So that for each row the mail will be sent after validating with the above IF condition

Hope this would help you
Kindly try this and let know for any queries or clarification
Cheers @Rupna80

1 Like