Hello there, we have a task to send email reminders to our clients to settle the payment for our services if any of the following two conditions are met:
the number of months of outstanding payment is three or above
the month(s) of outstanding payment is 3 months or more (for example, the current month is June. If the month of outstanding payment is 2021 March or before, we need to send reminders)
Please check the attached photo for easier explanation. Column A-C is the data table. Column E&F are just for your reference to explain which row meets the condition to send the reminders.
I know how to send the reminders if the conditions are met. My question is: how to set the code for each row to tell whether the conditions are met? It is easy to tackle the first condition. Any number higher than 2 would meet the condition to send the reminder. However, the tricky part is the second condition. As you can see from the photo Column C, there is a set date format used - yyyymmm or yyyymmmm. And if there are two or more months, it will be separated with a symbol like semicolon or comma.
The flow should be - for each row → if first condition is met → send reminder; if first condition is not met → check second condition → if second condition is met → send reminder; if both conditions are not met → proceed with next row
Thank you. It works like a champ! I just have a question on the code because I am not familiar.
Could you explain how the following codes work?
→ StringSplitOptions.RemoveEmptyEntries? If there is a “&” to separate between months, will the code be like this: row(2).ToString.Split({",“c,”;“c,”&"c},StringSplitOptions.RemoveEmptyEntries)
->(cdate(item).Month <= now.AddMonths(-3).Month and cint(item.Substring(0,4)) = now.Year) OR cint(item.Substring(0,4)) < now.Year
It Omit array elements that contain an empty string from the result. Please see the below referrence…
row(2).ToString.Split({",“c,”;“c,”&"c},StringSplitOptions.RemoveEmptyEntries) - This is correct…
(if the Month is less than 3 months from the current month and year is 2021) OR (Year is less than 2021) → Then send the email reminder…
If you are done with your testing and everything is working fine please mark my post as solution, that will close this thread and others will benefit from it.
Hello Prasath17, thank you for your explanation! A question on the following code:
(cdate(item).Month <= now.AddMonths(-3).Month and cint(item.Substring(0,4)) = now.Year) OR cint(item.Substring(0,4)) < now.Year
It is working fine now, but my concern is the second part
cint(item.Substring(0,4)) < now.Year
Although it works now, it might not work in the following scenario. Let’s assume the time now is January 2022, and there is a month o/s payment in 2021NOV or even 2021DEC. It will trigger the email reminder although the second condition is not met (only 2 months outstanding for 2021NOV or just 1 month outstanding for 2021DEC). I think for this part to be valid, the current months have to be in or after March. Am I right?
ashokkarale
Anil_G
Yoichi
yangyq10
postwick
chandreshsinh.jadeja
aravindbalineni123
Parvathy
aya
PRASHANT_GABHANE