Sending an Automated Email at Shipment Finalization

This uses a predefined email message and replaces placeholders in the message with information from the shipment. Either add as an action on Shipments or as an event after shipment finalisation.

Process Event Code

DECLARE @Shipment bigint, @SystemType char(1)
SELECT @Shipment = Shipment, @SystemType = SystemType 
FROM Shipments WHERE Shipment = [Identifier]
IF @SystemType <> 'F'
	BEGIN
	select 'Stop','This shipment has not been finalised'
	END
ELSE
BEGIN
	DECLARE @EmailAddress nvarchar(400)

	SELECT
	@EmailAddress = ISNULL(SalesOrders.NotifyShip,Customers.EmailAddress)
	FROM 
	Shipments
	INNER JOIN SalesOrders ON SalesOrders.SalesOrder = Shipments.SalesOrder
	INNER JOIN Customers ON Customers.Customer = Shipments.Customer
	WHERE Shipment = @Shipment 

	-- Select predefined message
	DECLARE @Body nvarchar(max), @Subject nvarchar(1000)
	select @Body = Body, @Subject = Subject from emailmessages WHERE EmailMessageId = 'SHIPHTML'

	-- create replacements table
	DECLARE  @X_REPLACEMENTS TABLE (
	string NVARCHAR(200),
	replacement NVARCHAR(4000));

	-- add replacement identifiers
	INSERT INTO @X_REPLACEMENTS SELECT '{%CUSTOMERNAME%}',CustomerName FROM Shipments INNER JOIN Customers ON Customers.Customer = Shipments.Customer WHERE Shipment = @Shipment;
	INSERT INTO @X_REPLACEMENTS SELECT '{%ORDERNUMBER%}',ISNULL(SalesOrderId,'') FROM Shipments INNER JOIN SalesOrders ON SalesOrders.SalesOrder = Shipments.SalesOrder WHERE Shipment = @Shipment;
	INSERT INTO @X_REPLACEMENTS SELECT '{%DESPATCHDATE%}',ISNULL(EffectiveDate,'') FROM Shipments WHERE Shipment = @Shipment;
	INSERT INTO @X_REPLACEMENTS SELECT '<<freightmethodname>>',ISNULL(FreightMethodDescription,'our courier') FROM Shipments INNER JOIN FreightMethods ON FreightMethods.FreightMethod = Shipments.FreightMethod WHERE Shipment = @Shipment;

	DECLARE @ShippingAddress nvarchar(4000)
	SELECT @ShippingAddress = 
		CASE WHEN LTRIM(ISNULL(DeliveryAddresses.DeliveryName,'')) <> '' THEN ISNULL(DeliveryAddresses.DeliveryName,'') + '<br>' ELSE '' END
		+ CASE WHEN LTRIM(ISNULL(DeliveryAddresses.Address,'')) <> '' THEN ISNULL(DeliveryAddresses.Address,'') + '<br>' ELSE '' END
		+ CASE WHEN LTRIM(ISNULL(DeliveryAddresses.City,'')) <> '' THEN ISNULL(DeliveryAddresses.City,'') + '<br>' ELSE '' END
		+ CASE WHEN LTRIM(ISNULL(DeliveryAddresses.Region,'')) <> '' THEN ISNULL(DeliveryAddresses.Region,'') + '<br>' ELSE '' END
		+ CASE WHEN LTRIM(ISNULL(DeliveryAddresses.PostalCode,'')) <> '' THEN ISNULL(DeliveryAddresses.PostalCode,'') + '<br>' ELSE '' END
		+ CASE WHEN LTRIM(ISNULL(Countries.CountryId,'')) <> '' THEN ISNULL(Countries.CountryId,'') + '<br>' ELSE '' END
	FROM Shipments 
	INNER JOIN DeliveryAddresses ON DeliveryAddresses.DeliveryAddress = Shipments.DeliveryAddress
	LEFT JOIN Countries ON DeliveryAddresses.Country = Countries.Country
	WHERE Shipment = @Shipment
	INSERT INTO @X_REPLACEMENTS SELECT '{%SHIPPINGADDRESS%}',ISNULL(@ShippingAddress,'') 

	DECLARE @BillingAddress nvarchar(4000)
	SELECT @BillingAddress = 
		CASE WHEN LTRIM(ISNULL(Customers.CustomerName,'')) <> '' THEN ISNULL(Customers.CustomerName,'') + '<br>' ELSE '' END
		+ CASE WHEN LTRIM(ISNULL(Customers.Address,'')) <> '' THEN ISNULL(Customers.Address,'') + '<br>' ELSE '' END
		+ CASE WHEN LTRIM(ISNULL(Customers.City,'')) <> '' THEN ISNULL(Customers.City,'') + '<br>' ELSE '' END
		+ CASE WHEN LTRIM(ISNULL(Customers.Region,'')) <> '' THEN ISNULL(Customers.Region,'') + '<br>' ELSE '' END
		+ CASE WHEN LTRIM(ISNULL(Customers.PostalCode,'')) <> '' THEN ISNULL(Customers.PostalCode,'') + '<br>' ELSE '' END
		+ CASE WHEN LTRIM(ISNULL(Countries.CountryId,'')) <> '' THEN ISNULL(Countries.CountryId,'') + '<br>' ELSE '' END
	FROM Shipments 
	INNER JOIN Customers ON Customers.Customer = Shipments.Customer
	LEFT JOIN Countries ON Customers.Country = Countries.Country
	WHERE Shipment = @Shipment
	INSERT INTO @X_REPLACEMENTS SELECT '{%BILLINGADDRESS%}',ISNULL(@BillingAddress,'') 

	DECLARE @OrderDetail nvarchar(4000)
	SELECT @OrderDetail = ISNULL(@OrderDetail,'') + CAST(FLOOR(SalesOrderItems.Quantity) AS varchar(10)) + ' of ' + ISNULL(SalesOrderItems.ItemDescription,'') + '<BR>'
	FROM Shipments 
	INNER JOIN SalesOrders ON SalesOrders.SalesOrder = Shipments.SalesOrder 
	INNER JOIN SalesOrderItems ON SalesOrderItems.SalesOrder = SalesOrders.SalesOrder 
	WHERE Shipment = @Shipment AND SalesOrderItems.ItemType = 'P'
	INSERT INTO @X_REPLACEMENTS SELECT '{%PRODUCTLIST%}',ISNULL(@OrderDetail,'') 

	INSERT INTO @X_REPLACEMENTS SELECT '<<trackingurl>>',REPLACE(ISNULL(TrackingURL,''),'{0}',ISNULL(DeliveryReference,'')) FROM Shipments INNER JOIN FreightMethods ON FreightMethods.FreightMethod = Shipments.FreightMethod WHERE Shipment = @Shipment;
	INSERT INTO @X_REPLACEMENTS SELECT '{%SHIPPINGNOTE%}','';

	-- replace placeholders in the body and subject
	SELECT @Body = REPLACE(@Body,string,replacement)
	FROM @X_REPLACEMENTS;

	SELECT @Subject = REPLACE(@Subject,string,replacement)
	FROM @X_REPLACEMENTS;

	IF @EmailAddress <> ''
	BEGIN

		DECLARE @mailitem_id int
		EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'WINMAN',
		@recipients = @EmailAddress,
		@copy_recipients = '',
		@blind_copy_recipients = '',
		@subject = @Subject,
		@body = @Body,
		@body_format = 'HTML',
		@file_attachments = '',
		@importance = 'NORMAL',
		@mailitem_id  = @mailitem_id OUTPUT


		IF @mailitem_id > 0
		BEGIN
		  select 'InformationCanHide','ShipEmailCustomerOK','Email Sent Successfully'
		END

	END
END

HTML Email

<TABLE border=0 cellSpacing=0 cellPadding=5 width=600>
<TBODY>
<TR>
<TD style="BORDER-BOTTOM: black 1px solid; PADDING-BOTTOM: 10px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: black 1px solid; PADDING-TOP: 10px" vAlign=middle colSpan=2>
<P style="FONT-SIZE: 120%; FONT-WEIGHT: bold">Your Order Has Been Despatched</P></TD></TR>
<TR>
<TD style="TEXT-ALIGN: left"><BR>
<TABLE width="100%">
<TBODY>
<TR>
<TD style="TEXT-ALIGN: left" vAlign=bottom><STRONG>Order number:</STRONG> </TD>
<TD style="TEXT-ALIGN: right; PADDING-RIGHT: 10px">{%ORDERNUMBER%} </TD></TR></TBODY></TABLE><BR></TD>
<TD style="TEXT-ALIGN: left"><BR>
<TABLE width="100%">
<TBODY>
<TR>
<TD style="TEXT-ALIGN: left" vAlign=bottom><STRONG>Despatch date:</STRONG> </TD>
<TD style="TEXT-ALIGN: right; PADDING-RIGHT: 10px">{%DESPATCHDATE%} </TD></TR></TBODY></TABLE><BR></TD></TR>
<TR>
<TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: left; VERTICAL-ALIGN: top; BORDER-TOP: black 1px solid; PADDING-TOP: 10px" width="50%"><STRONG>Billing Address</STRONG> <BR><BR>
<TABLE>
<TBODY>
<TR>
<TD style="PADDING-LEFT: 10px">{%BILLINGADDRESS%} </TD></TR></TBODY></TABLE><BR></TD>
<TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: left; VERTICAL-ALIGN: top; BORDER-TOP: black 1px solid; PADDING-TOP: 10px" width="50%"><SPAN style="FONT-WEIGHT: bold">Shipping Address</SPAN> <BR><BR>
<TABLE>
<TBODY>
<TR>
<TD style="PADDING-LEFT: 10px">{%SHIPPINGADDRESS%} </TD></TR></TBODY></TABLE><BR></TD></TR>
<TR>
<TD colSpan=2><BR>The following items were despatched on this shipment<BR></TD></TR>
<TR>
<TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: left; PADDING-BOTTOM: 10px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 10px" colSpan=2>{%PRODUCTLIST%} </TD></TR>
<TR>
<TD style="TEXT-ALIGN: left; PADDING-TOP: 10px" colSpan=2>
<DIV style="TEXT-ALIGN: right"> </DIV></TD></TR>
<TR>
<TD colSpan=2>
<TABLE width="100%">
<TBODY>
<TR>
<TD style="TEXT-ALIGN: left">{%SHIPPINGNOTE%} </TD></TR></TBODY></TABLE></TD></TR>
<TR>
<TD style="FONT-SIZE: 80%; PADDING-TOP: 20px" colSpan=2>My Company<BR>Unit 99, This Industrail Park,<BR>High Street,<BR>London<BR>SW1 2AB</TD></TR>
</TBODY>
</TABLE>