Shipment notification with contents to customer

kp11 · March 8, 2021, 4:20pm

Has anyone set up a shipment notification to send to the customer that contains an HTML email of the part number, quantities, tracking etc that were shipped to them? I know what I would do to initiate the email, BPM that looks for changes on the tracking number, maybe a checkbox that signifies that the tracking has sent.

The actual HTML contents of the email is what I am unsure of. If anyone has a snip of code I could look at or an article this would be helpful. When I figure it out I will post the BPM here for anyone else to use.

ckrusen · March 8, 2021, 6:49pm

I made a daily shipping notice process that builds an HTML based email of the packer’s contents, previously shipped items , and outstanding items.

It does not include the Packer from SSRS.

It pretty much is all in an Execute Custom Code widget. I’d post the code but I’d have to do a lot of redacting.

ckrusen · March 8, 2021, 7:07pm

okay … here it is

It’s from a DD on UD05 (how I trigger it to run at 4:00 PM each day)

Exec Custom Code

/* 2017-02-23 CRK - Added ship from plant to body */
/* 2017-06-01 CRK - Added jduncan to ccList */
/* 2017-07-13 CRK - Added code for displaying prior shipments and open lines */
/* 2017-08-03 CRK - Added ShipVia */
/* 2017-10-22 CRK - Added Carlos (xxxx@xxxxx.com) to CC list */
/* 2018-07-24 CRK - Removed jeb duncan from CC list */
/* 2018-09-22 CRK - Added kegner to CC list */
/* 2019-01-24 CRK - Removed ejacob, and added thuck to CC list */

this.PackNumList = "this.TPN set in ECC #1";

//Erp.Tables.Part Part;
string body=String.Empty;
string NewEmailBody = String.Empty;
var today = DateTime.Today.AddDays(0);  // change from -2 to 0 after development
var yesterday = today.AddDays(-1);
string tn = String.Empty;
int ordNum = 0;
int ordCount = 0;
int lastOrdNum = 0;
string jeNum = String.Empty;
string pm = String.Empty;
string pmEmail  = String.Empty;
string ccList = "allice@example.com; bob@example.com;";

this.autoPrintCCList = ccList;
int thisPackNum = 0;
int lastPackNum = 0;
string opLineRel = String.Empty;
string ts  = String.Empty;
// var td;
string cc = String.Empty;
string Email_subject = "Init";
string shipViaCode = "";
string shipViaDesc = "";


// For each Packer with status = SHIPPED && (shipdate = today || (shipdate = yesterday &&  shiptime > 4:00 PM))
Erp.Tables.ShipHead ShipHead;
foreach (var ShipHead_iterator in (from ShipHead_Row in Db.ShipHead
	where ShipHead_Row.Company == Session.CompanyID 
	 && ShipHead_Row.ShipStatus == "SHIPPED" 
	 && (ShipHead_Row.ShipDate == today || (ShipHead_Row.ShipDate == yesterday && ShipHead_Row.ChangeTime >= ((12+4) * 3600)))
	orderby ShipHead_Row.PackNum
	select ShipHead_Row)){
	

	tn = ShipHead_iterator.TrackingNumber;
	shipViaCode = ShipHead_iterator.ShipViaCode;	
	this.TestPackNum = ShipHead_iterator.PackNum;

/******************/
	Erp.Tables.ShipVia ShipVia;
	ShipVia = (from ShipVia_Row in Db.ShipVia
		where ShipVia_Row.Company == Session.CompanyID && ShipVia_Row.ShipViaCode == shipViaCode
		select ShipVia_Row).FirstOrDefault();

	if (ShipVia != null){
		shipViaDesc = ShipVia.Description;
		}
/*******************/

	thisPackNum = ShipHead_iterator.PackNum;
	pmEmail = "ckrusen@matcor.com";
	Email_subject = "Packer# " + thisPackNum.ToString("0") + " Tracking #: " + tn;
	
	NewEmailBody = "<html><body><pre style='font: monospace'>";
	NewEmailBody = NewEmailBody + "<style>table, th, td {border: 1px solid black;border-collapse: collapse; font-family: monospace; font-size: 12px;}";
	NewEmailBody = NewEmailBody + "th, td {padding: 5px;text-align: left;}</style>";


	NewEmailBody = NewEmailBody + "(This is an automated message. Please do not reply)\n\n";
	NewEmailBody = NewEmailBody + "<b>FOR INTERNAL USE ONLY !!!</b>\n";

	/* added Source Plant 2/23/17 */
	NewEmailBody = NewEmailBody + "<b>Shipment from: ";
	if (ShipHead_iterator.Plant == "MfgSys")
		NewEmailBody = NewEmailBody + "CHALFONT";
	
	if (ShipHead_iterator.Plant == "GUTH")
		NewEmailBody = NewEmailBody + "GUTHRIE";
	
	if (ShipHead_iterator.Plant == "HOUST")
		NewEmailBody = NewEmailBody + "HOUSTON";
	
	if (ShipHead_iterator.Plant == "ROCKSP")
		NewEmailBody = NewEmailBody + "DENVER";
		
	NewEmailBody = NewEmailBody + "</b>\n";
	/* end of add source plant */


/* added Ship Date 3/7/17 */
	NewEmailBody = NewEmailBody + "<b>Shipment Date:: ";
	string newDate = String.Format("{0:d}", ShipHead_iterator.ShipDate);
	NewEmailBody = NewEmailBody + newDate + "</b><br><br>";
/* added Ship Date 3/7/17 */

	ordCount = 0;
	lastOrdNum = -1;

	/********************************************************************/
	/* Get related Order info - J&E Num, Vista Num, PM, PM's email, ... */
	Erp.Tables.ShipDtl ShipDtl;
	
	ShipDtl = (from ShipDtl_Row in Db.ShipDtl
		where ShipDtl_Row.Company == Session.CompanyID && ShipDtl_Row.PackNum == thisPackNum
	select ShipDtl_Row).FirstOrDefault();
	if (ShipDtl != null){
		ordNum = ShipDtl.OrderNum;

		Erp.Tables.OrderHed OrderHed;
		OrderHed = (from OrderHed_Row in Db.OrderHed
			where OrderHed_Row.Company == Session.CompanyID && OrderHed_Row.OrderNum == ordNum
			select OrderHed_Row).FirstOrDefault();

		if (OrderHed != null){
			pm = OrderHed.ProjManager_c;
			jeNum = OrderHed.JE_JobNum_c;
			}

		// Get Proj Manager info from UD05
		Ice.Tables.UD05 UD05;

		UD05 = (from UD05_Row in Db.UD05
			where UD05_Row.Company == Session.CompanyID && UD05_Row.ShortChar01 == pm
			select UD05_Row).FirstOrDefault();		
		if (UD05 != null){
			pmEmail = UD05.ShortChar02;  // PM's email address
			}
		Email_subject = "Shipment Notification.  Order#: " + jeNum + " / " + ordNum.ToString("0") + ", Packer#: " + thisPackNum.ToString("0");

		
		// get customer info
		string cust="";
		Erp.Tables.Customer Customer;
		Customer = (from Customer_Row in Db.Customer
			where Customer_Row.Company == Session.CompanyID && Customer_Row.CustNum == ShipHead_iterator.CustNum
			select Customer_Row).FirstOrDefault();		
		if (Customer != null){
			cust = Customer.Name;
			}

		// get shipto info
		string stName = "";
		Erp.Tables.ShipTo ShipTo;
		ShipTo = (from ShipTo_Row in Db.ShipTo
			where ShipTo_Row.Company == Session.CompanyID && ShipTo_Row.CustNum == ShipHead_iterator.CustNum && ShipTo_Row.ShipToNum == ShipHead_iterator.ShipToNum
			select ShipTo_Row).FirstOrDefault();		
		if (ShipTo != null){
			stName = ShipTo.Name;
			}
	
/*****************************/
/* build order shipment info */

NewEmailBody = NewEmailBody +       "************************ THIS SHIPMENT ************************ " + "\n";
NewEmailBody = NewEmailBody + "Packer: " + thisPackNum.ToString("0") + ",  J&E Job#:" + jeNum + ",  E10 Order#:" + ordNum.ToString("0") + "\n";
NewEmailBody = NewEmailBody + "Ship Via: " + shipViaDesc + "\n";
NewEmailBody = NewEmailBody + "Tracking #(s): " + tn + "\n\n";
NewEmailBody = NewEmailBody + "<b>Ship To:</b> \n";
NewEmailBody = NewEmailBody + ShipTo.Name + " (" + Customer.CustID +")\n";
NewEmailBody = NewEmailBody + ShipTo.Address1 + "\n";
if (ShipTo.Address2.Length > 1) NewEmailBody = NewEmailBody + ShipTo.Address2 + "\n";
if (ShipTo.Address3.Length > 1) NewEmailBody = NewEmailBody + ShipTo.Address3 + "\n";
NewEmailBody = NewEmailBody + ShipTo.City + ", " + ShipTo.State + "  " +  ShipTo.ZIP + "\n";
NewEmailBody = NewEmailBody + "\n";

// build the shippment table
NewEmailBody = NewEmailBody + "<table>";
NewEmailBody = NewEmailBody + "<tr><th>Ord<br>Line</th>";
NewEmailBody = NewEmailBody + "<th>Ship<br>Qty</th>";
NewEmailBody = NewEmailBody + "<th><br>UOM</th>";
NewEmailBody = NewEmailBody + "<th>Part<br>Number</th>";
NewEmailBody = NewEmailBody + "<th>Part<br>Description</th></tr>";

foreach (var ShipDtl_iterator in (from ShipDtl_Row in Db.ShipDtl 
  where ShipDtl_Row.Company == Session.CompanyID && ShipDtl_Row.PackNum == thisPackNum && ShipDtl_Row.PartNum != "FREIGHT"
  select ShipDtl_Row)){
		NewEmailBody = NewEmailBody + "<tr><td>" + ShipDtl_iterator.OrderLine.ToString("0") + "</td>";
		decimal sq = (ShipDtl_iterator.SellingInventoryShipQty + ShipDtl_iterator.SellingJobShipQty);
		string sqFormat;
		sqFormat = (Math.Floor(sq) == sq ? "####0" : "####0.00");
		
		NewEmailBody = NewEmailBody + "<td>" + sq.ToString(sqFormat) + "</td>";
		NewEmailBody = NewEmailBody + "<td>" + ShipDtl_iterator.SalesUM + "</td>";
		NewEmailBody = NewEmailBody + "<td>" + ShipDtl_iterator.PartNum + "</td>";
		string pd;
		pd = ShipDtl_iterator.LineDesc;
		pd = pd.Substring(0,(pd.Length > 50 ? 50 : pd.Length));
		NewEmailBody = NewEmailBody + "<td>" + pd + "</td></tr>";
	}

	NewEmailBody = NewEmailBody + "</table>";
	
	NewEmailBody = NewEmailBody + "\n";
	NewEmailBody = NewEmailBody + "\n\n";
	}

/* find previous shipments for this order  */
bool ff = false;
foreach (var ShipDtl_iterator in (from ShipDtl_Row in Db.ShipDtl 
  where ShipDtl_Row.Company == Session.CompanyID && ShipDtl_Row.OrderNum == ordNum && ShipDtl_Row.PackNum != thisPackNum
  select ShipDtl_Row)){
		if(ff == false){
			NewEmailBody = NewEmailBody + "************************ PREVIOUS SHIPMENTS ************************ \n\n";
			NewEmailBody = NewEmailBody + "<table>";
			NewEmailBody = NewEmailBody + "<tr><th>Ord<br>Line</th>";
			NewEmailBody = NewEmailBody + "<th>Ship<br>Qty</th>";
			NewEmailBody = NewEmailBody + "<th><br>UOM</th>";
			NewEmailBody = NewEmailBody + "<th>Part<br>Number</th>";
			NewEmailBody = NewEmailBody + "<th>Part<br>Description</th></tr>";
	 ff = true;
			}
	}

lastPackNum = 0;
foreach (var ShipDtl_iterator in (from ShipDtl_Row in Db.ShipDtl 
  where ShipDtl_Row.Company == Session.CompanyID && ShipDtl_Row.OrderNum == ordNum && ShipDtl_Row.PackNum != thisPackNum
	orderby ShipDtl_Row.PackNum
  select ShipDtl_Row)){
		if(ShipDtl_iterator.PackNum != lastPackNum){
			NewEmailBody = NewEmailBody + "<th colspan=5>Packer#: " + ShipDtl_iterator.PackNum.ToString("0") + "</th>";
			lastPackNum = ShipDtl_iterator.PackNum;
			}
		NewEmailBody = NewEmailBody + "<tr><td>" + ShipDtl_iterator.OrderLine.ToString("0") + "</td>";
		decimal sq = (ShipDtl_iterator.SellingInventoryShipQty + ShipDtl_iterator.SellingJobShipQty);
		string sqFormat;
		sqFormat = (Math.Floor(sq) == sq ? "####0" : "####0.00");
		
		NewEmailBody = NewEmailBody + "<td>" + sq.ToString(sqFormat) + "</td>";
		NewEmailBody = NewEmailBody + "<td>" + ShipDtl_iterator.SalesUM + "</td>";
		NewEmailBody = NewEmailBody + "<td>" + ShipDtl_iterator.PartNum + "</td>";
		string pd;
		pd = ShipDtl_iterator.LineDesc;
		pd = pd.Substring(0,(pd.Length > 50 ? 50 : pd.Length));
		NewEmailBody = NewEmailBody + "<td>" + pd + "</td></tr>";
	}
if(ff == true) 
	NewEmailBody = NewEmailBody + "</table>";
/*************/

/*************************** OPEN Lines  *************************************/
ff = false;
foreach (var OrderRel_iterator in (from OrderRel_Row in Db.OrderRel 
  where OrderRel_Row.Company == Session.CompanyID && OrderRel_Row.OrderNum == ordNum && OrderRel_Row.OpenRelease
	select OrderRel_Row)){
		if(ff == false){
			NewEmailBody = NewEmailBody + "************************ OPEN ORDERS ************************ \n\n";
			NewEmailBody = NewEmailBody + "<table>";
			NewEmailBody = NewEmailBody + "<tr><th>Line-<br>Rel</th>";
			NewEmailBody = NewEmailBody + "<th>Rel<br>Qty</th>";
			NewEmailBody = NewEmailBody + "<th>Part<br>Number</th>";
			NewEmailBody = NewEmailBody + "<th>Open<br>Qty</th>";
			//NewEmailBody = NewEmailBody + "<th>Req<br>Date</th>";
			ff = true;
			}
		opLineRel = OrderRel_iterator.OrderLine.ToString("0") + "-" + OrderRel_iterator.OrderRelNum.ToString("0");
		NewEmailBody = NewEmailBody + "<tr><td>" + opLineRel + "</td>";
		decimal sq = (OrderRel_iterator.OurReqQty);
		string sqFormat;
		sqFormat = (Math.Floor(sq) == sq ? "####0" : "####0.00");
		
		NewEmailBody = NewEmailBody + "<td>" + sq.ToString(sqFormat) + "</td>";
		NewEmailBody = NewEmailBody + "<td>" + OrderRel_iterator.PartNum + "</td>";
		decimal openQty = (OrderRel_iterator.OurReqQty - (OrderRel_iterator.OurJobShippedQty + OrderRel_iterator.OurStockShippedQty));
		sqFormat = (Math.Floor(openQty) == openQty ? "####0" : "####0.00");
		NewEmailBody = NewEmailBody + "<td>" + openQty.ToString(sqFormat) + "</td>";
		//NewEmailBody = NewEmailBody + "<td>" + OrderRel_iterator.ReqDate + "</td>";
		NewEmailBody = NewEmailBody + "</tr>";
	}
if(ff == true) 
	NewEmailBody = NewEmailBody + "</table>";

/*********************  Close body HTML ******************/
	NewEmailBody = NewEmailBody + "</pre></body></html>";
// NewEmailBody now contains the HTML for the body


	// setup the email header info
	string Email_from = "no-reply@matcor.com";
	string Email_to = "ckrusen@matcor.com";
	string Email_cc = ccList;
	
	
	//  if a PM exists, replace the "TO:" with their email address AND remove them from the "CC:" list
	if(pmEmail.Length > 0) {
		Email_to = pmEmail;
		Email_cc = ccList.Replace(pmEmail+";","");
		}
	
	
	/************** Debugging stuff ***************/
	if(callContextClient.CurrentUserId == "manager"){
		NewEmailBody = NewEmailBody + " /************* start debugging info ************/\n";
		NewEmailBody = NewEmailBody + "CC: " + ccList + "\n";
		NewEmailBody = NewEmailBody + " /************* end debugging info **************/\n";
		}
	/*********** End Debugging stuff **************/

	/***********/
	string Email_body = NewEmailBody;
	var from = Email_from;
	var mailer = this.GetMailer(async: true);
	var message = new Ice.Mail.SmtpMail();
	message.SetFrom(from);
	if(callContextClient.CurrentUserId == "manager"){
		Email_to = "ckrusen@matcor.com";
		Email_cc = "";
		}
		
	message.SetTo(Email_to);
	message.SetCC(Email_cc);
	message.SetSubject(Email_subject);
	message.IsBodyHtml = true;  // allows the use of HTML tags 
	message.Body = Email_body;
	//message.SetBody(Email_body);  // (this is used for HTML format) 

	mailer.Send(message);
	/**************************/
	
	// The following is old debugging stuff.  
	// The message was sent a few lines above, so setting these now does nothing.
	body = NewEmailBody + "<br><br>From: " + from;
	body += "<br>To: " + Email_to;
	body += "<br>CC: " + Email_cc;
	body += "<br>Subject: " + Email_subject;
	this.TestBody = "Hello World! (TestBody)";
	this.autoPrintBody = NewEmailBody;
	this.PackNumList = this.PackNumList + ShipHead_iterator.PackNum.ToString() + "~";
	}
this.TestBody = "Hello World! (TestBody outside of foreach loop)";

edit

Full disclosure - That’s code that was ported from V8 (Progress DB), which was code I wrote a long time ago. While it works for us, there may be bugs, and most likely is a poor programming style/

gpayne · March 8, 2021, 9:03pm

@kp11 ours is a standalone program. here is the code that makes the table.

 System.Text.StringBuilder sb = new StringBuilder();



            //clear the string builder
            sb.Length = 0;
            sb.Append("<br />The following items were shipped from ");

            sb.Append("<br />");
            sb.Append("<br />");
            sb.Append("<br />");

  if (info.Select(custnumfield + " = " + CustNum.ToString() + " and " + packnumfield + " = '" + pack + "' and " + tracknumfield + "<> ''").Length > 0)
                {
                    sb.Append("<br />Package(s)").Append("<table width=\"100%\" border=\"0\">");
                    sb.Append("<tr><td align=\"center\">Date</td><td align=\"left\">Packing Slip</td><td align=\"left\">Tracking Number</td><td align=\"center\">Weight</td><td align=\"left\">Carrier/Service</td></tr>");

                    foreach (DataRow dr in info.Select(custnumfield + " = " + CustNum.ToString() + " and " + packnumfield + " = '" + pack + "' and " + tracknumfield + " <> ''"))
                    {
                        var track = dr[tracknumfield].ToString();
                        if (packs.Contains(pack + ":" + track)) continue;
                        packs.Add(pack + ":" + track);
                        sb.Append("<tr><td align=\"center\">").Append(((DateTime)dr[shipdatefield]).ToString("MM/dd/yyyy")).Append("</td>");
                        sb.Append("<td align=\"left\">").Append(dr[packnumfield].ToString()).Append("</td>");
                        sb.Append("<td align=\"left\">").Append(dr[tracknumfield].ToString()).Append("</td>");
                        sb.Append("<td align=\"center\">").Append(((decimal)dr[weightfield]).ToString("0.00")).Append("</td>");
                        sb.Append("<td align=\"left\">").Append(dr[carrierfield].ToString()).Append("</td></tr>");
                    }

                    sb.Append("</table>");
                }

                //go through all of the lines now for the po/customer and write out the detail information
                sb.Append("<br />Contents<table width=\"100%\" border=\"0\">");
                sb.Append("<tr><td align=\"left\">Item</td><td align=\"left\">Order #</td><td align=\"left\">PO Number</td><td align=\"left\">PO Line</td><td align=\"right\">Qty</td><td align=\"left\">Unit</td></tr>");
                List<string> lines = new List<string>();
                foreach (DataRow dr in info.Select(custnumfield + " = " + CustNum.ToString() + " and " + packnumfield + " = '" + pos[packnumfield].ToString() + "'"))
                {
                    var line = dr[partnumfield].ToString() + ":" + dr[ponumfield].ToString() + ":" + dr[polinefield].ToString();
                    if (lines.Contains(line)) continue;
                    lines.Add(line);
                    sb.Append("<tr><td align=\"left\">").Append(dr[partnumfield].ToString()).Append("</td>");
                    sb.Append("<td align=\"left\">").Append(dr[ordernumfield].ToString()).Append(" </td>");
                    sb.Append("<td align=\"left\">").Append(dr[ponumfield].ToString()).Append(" </td>");
                    sb.Append("<td align=\"left\">").Append(dr[polinefield].ToString()).Append(" </td>");
                    sb.Append("<td align=\"right\">").Append(dr[qtyfield].ToString()).Append(" </td>");
                    sb.Append("<td align=\"left\">").Append(dr[uomfield].ToString()).Append(" </td></tr>");
                }

                sb.Append("</table><br /><br />");

then sending set body
 mail.IsBodyHtml = true;
 mail.Body = sb.ToString();

Hope this helps.

Greg

ckrusen · March 9, 2021, 1:35pm

@gpayne 's is much better. Go with his.

And one thing that I don’t think Greg or I did (although Greg might have, as I didn’t got through his code line-by-line), is to escape any characters that might mess up the HTML.

Like when I use:
pd = ShipDtl_iterator.LineDesc;
should be
pd = Server.HtmlEncode(ShipDtl_iterator.LineDesc);

I also thought about making a template file with replaceable tags. I’d read in the template file, and then just replace the tags with my actual data, instead of building the entire HTML framework.

Topic		Replies	Views
Email HTML Notifications Epicor ERP 9	4	3792	October 25, 2017
Send shipping information to specific customers via email Kinetic ERP bpm	9	1044	August 14, 2024
PostProcessing Method Directive on Customer Shipment Entry Epicor ERP 10 e10 , bpm	9	701	February 16, 2024
Shipping Notice Data Directive to send an email Epicor ERP 10	5	1577	June 19, 2017
Sending an Email via BPM Epicor ERP 10 e10 , bpm	8	1412	March 8, 2024

Shipment notification with contents to customer

edit

Related Topics