Hello,
A couple of things to note about the code below
We have this run every Monday morning at 6am, it loops through all open invoices and checks the due date, if they are due this coming week it groups them into a table, if they are overdue it groups them into another table
If the balance value of the Invoice is below a certain value it skips it, if the invoice is a CM it skips it too
If you only want the code to work for overdue invoices, I’m sure you know how to comment out code
We found that a vast majority of our customers had dedicated email addresses for their accounts department, some had automatic invoice handlers, so we added some UD fields to our Customers record.
Most have moved away from using personal email addresses for their accounts dept. due to the fact that people move on from jobs
- BTAccntEmail_c string x(200) you can fit more than one email address in it
- BTStmntEmail_c
- BTInvEmail_c
We also added a checkbox to determine whether to send these invoice reminders or not
- SendReminders_c (true/false)
To get around using the email UD fields you could add a foreach on the customers contacts to find the contact that is set as a billing contact, and to get around using the added checkbox you could set the condition off another checkbox
First up I set up a System Agent Schedule that runs at 6am every Monday (best to run this type of code at an off peak time)
Then I set up a Standard Data Directive on SysAgentSched
Then I added an Execute Custom Code widget with the following code in it
Note: if you end using this code you will need to update the number of the AgentSchedule in the code…. And a few other bits specific to your company
And make sure you test it in a test environment with your email address hardcoded into the To: field
bool tvSYS = ttSysAgentSched.Any(s => s.Updated() && ttSysAgentSched.Any(s1 => s1.Unchanged() && s1.SysRowID == s.SysRowID && s1.LastRunOn != s.LastRunOn && s.AgentSchedNum == 209860));
if (tvSYS == true) {
var sunday = DateTime.Today.AddDays(-(int) DateTime.Today.DayOfWeek);
var nextsunday = DateTime.Today.AddDays(-(int) DateTime.Today.DayOfWeek).AddDays(7);
string emailSubject = "";
string emailBody = "";
bool noContact = false;
var InvCusts = Db.InvcHead.Where(i => i.Company == Session.CompanyID && i.Posted == true && i.OpenInvoice == true && i.CreditMemo == false && i.UnappliedCash == false && i.InvoiceBal > 1 && i.DueDate < nextsunday && Db.Customer.Any(c => c.CustNum == i.CustNum && c.NoContact == false && c.SendReminders_c == true)).Select(i => i.CustNum).Distinct().ToList();
if (InvCusts != null) {
foreach(var InvCust in InvCusts) {
////////Cust details////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
var style = "<style> table, th, td {border: 1px solid #000; border-collapse: collapse; padding-left: 5px; padding-right: 5px;} th {background-color: #c0c0c0;} th {width:110px; text-align:centre;}</style>";
string emailBodyDueThisWeek = "";
string emailBodyOverDue = "";
var emailSub = "";
var emailTo = "";
var emailFooter = "<a href=\"https://www.yourcompany.com.au\"><img src=\"https://yourcompany.com.au/es19/banner.png\" alt=\"Treadwell Logo\"></a>";
var oBillTo = Db.Customer.Where(c => c.Company == Session.CompanyID && c.CustNum == InvCust).Select(x => new {
x.BTAccntEmail_c, x.BTStmntEmail_c, x.EMailAddress, x.BTInvEmail_c, x.Name
}).FirstOrDefault();
var custName = oBillTo.Name;
var accntEmail = oBillTo.BTAccntEmail_c;
if (!accntEmail.Contains("@")) {
accntEmail = oBillTo.BTStmntEmail_c;
}
if (!accntEmail.Contains("@")) {
accntEmail = oBillTo.EMailAddress;
}
if (!accntEmail.Contains("@")) {
accntEmail = oBillTo.BTInvEmail_c;
}
if (!accntEmail.Contains("@")) {
accntEmail = "ar@yourcompany.com.au";
}
////////End Cust details////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
System.Text.StringBuilder messageLine = new System.Text.StringBuilder();
System.Text.StringBuilder messageLineOD = new System.Text.StringBuilder();
decimal balInv = 0;
decimal totalInv = 0;
decimal balInvOD = 0;
decimal totalInvOD = 0;
int DTW = 0;
int ODNum = 0;
string DTWText = "invoice is";
string ODText = "invoice is";
///////////////////Due this week////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
var Invs = Db.InvcHead.Where(i => i.Company == Session.CompanyID && i.Posted == true && i.InvoiceBal > 1 && i.OpenInvoice == true && i.CreditMemo == false && i.UnappliedCash == false && i.CustNum == InvCust && i.DueDate < nextsunday).ToList();
if (Invs != null) {
foreach(var Inv in Invs) {
if (Inv.DueDate > sunday) {
DTW = DTW + 1;
balInv = balInv + Inv.InvoiceBal;
totalInv = totalInv + Inv.InvoiceAmt;
var invnUMBER = Inv.InvoiceNum;
var InvoiceAmt = string.Format("{0:C2}", Inv.InvoiceAmt);
var InvoiceBal = string.Format("{0:C2}", Inv.InvoiceBal);
var InvoiceDue = string.Format("{0:dd/MM/yyyy}", Inv.DueDate);
var InvoiceTot = string.Format("{0:C2}", totalInv);
var InvoiceBalT = string.Format("{0:C2}", balInv);
messageLine.AppendLine($"<tr><td style=\"text-align:right;\">{invnUMBER}</td><td style=\"text-align:right;\">{InvoiceAmt}</td><td style=\"text-align:right;\">{InvoiceBal}</td><td style=\"text-align:right;\">{InvoiceDue}</td></tr>");
var totalLine = $"<tr><td style=\"text-align:right;\">Totals:</td><td style=\"text-align:right;\">{InvoiceTot}</td><td style=\"text-align:right;\">{InvoiceBalT}</td><td style=\"text-align:right;\"></td></tr>";
if (DTW > 1) {
DTWText = "invoices are";
}
emailBodyDueThisWeek = "<br><br>The following " + DTWText + " due to be paid this week:<br><br><table> <tr><th>Invoice</th> <th>Invoice Amount</th> <th>Invoice Balance</th> <th>Due Date</th></tr>" + messageLine + totalLine + "</table><br><br>";
///////////////////END Due this week////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
}
///////////////////OVERDUE////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
if (Inv.DueDate <= sunday) {
balInvOD = balInvOD + Inv.InvoiceBal;
totalInvOD = totalInvOD + Inv.InvoiceAmt;
ODNum = ODNum + 1;
var invnUMBEROD = Inv.InvoiceNum;
var InvoiceAmtOD = string.Format("{0:C2}", Inv.InvoiceAmt);
var InvoiceBalOD = string.Format("{0:C2}", Inv.InvoiceBal);
var InvoiceDueOD = string.Format("{0:dd/MM/yyyy}", Inv.DueDate);
var InvoiceTotOD = string.Format("{0:C2}", totalInvOD);
var InvoiceBalTOD = string.Format("{0:C2}", balInvOD);
messageLineOD.AppendLine($"<tr><td style=\"text-align:right;\">{invnUMBEROD}</td><td style=\"text-align:right;\">{InvoiceAmtOD}</td><td style=\"text-align:right;\">{InvoiceBalOD}</td><td style=\"text-align:right;\">{InvoiceDueOD}</td></tr>");
var totalLineOD = $"<tr><td style=\"text-align:right;\">Totals:</td><td style=\"text-align:right;\">{InvoiceTotOD}</td><td style=\"text-align:right;\">{InvoiceBalTOD}</td><td style=\"text-align:right;\"></td></tr>";
if (ODNum > 1) {
ODText = "invoices are";
}
emailBodyOverDue = "<br><br>The following " + ODText + " overdue:<br><br><table> <tr><th>Invoice</th> <th>Invoice Amount</th> <th>Invoice Balance</th> <th>Due Date</th></tr>" + messageLineOD + totalLineOD + "</table>";
}
}
///////////////////END OVERDUE////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
emailBody = "<html>" + style + "<body><p><b>" + custName + "</b><br><br>Dear Customer,<br><br>This correspondence is to advise you of your current account status: " + emailBodyOverDue + emailBodyDueThisWeek + "Thank you and we look forward to your prompt payment.<br><br>Should you have any questions or require a copy of any invoices, please reply to this email or contact me on the details below.<br>If payment has already been made, thank you and please disregard this email.<br><br>Kind regards,<br><br><b style=\"color:red;\">FirstName LastName</b> | Accounts<br>P 1800 246 800<br>ar@yourcompany.com.au</p><BR>" + emailFooter + "<BR><p style=\"color: #c0c0c0;\">This email and any attached files may contain confidential information and/or copyright material and is intended for the use of the addressee only. If you have received this transmission in error you may not reproduce, copy, distribute or disclose its contents to anyone else; please notify Your Company by return email to: mail@yourcompany.com.au immediately and delete this email from your computer system. Any views expressed in this email are those of the individual sender and may not necessarily reflect the views of Your Company.</p></body></html>";
emailSubject = "Your Account Summary from Your Company";
}
var conSt = Db.ConnectionString;
if (conSt.Contains("Pilot") || conSt.Contains("Test"))
{
accntEmail = "test@yourcompany.com";
}
var mailer = this.GetMailer(async :false);
var message = new Ice.Mail.SmtpMail();
message.SetFrom("Accounts Receivable | Your Company<do-not-reply@yourcompany.com.au>"); //Accounts Receivable ("do-not-reply@yourcompany.com.au");
message.SetTo(accntEmail);
message.SetReplyTo("ar@yourcompany.com.au");
// message.SetCC("");
// message.SetBcc("lrb@yourcompany.com.au");
message.SetSubject(emailSubject);
message.SetBody(emailBody);
//message.IsBodyHtml = true;
message.Priority = System.Net.Mail.MailPriority.High;
mailer.Send(message);
}
}
}
You can use the above for a lot of different things
We also use it to follow up with suppliers for overdue Purchase orders
This does not send any attachments
Happy coding!!