Getting shipping address information in email

[Stephen Phillips]

I'm working on creating a notification email when an order submission happens in customer app.

Can I get the shipping address information? Everything I have tried does not work.

What I really want is the full order details like the receipt sent via email.

[Tom Gooding]

Hi Steve,

I'm assuming that you are using the 'Order Submission in Customer Application - Detailed' email template as the starting point?

Unfortunately the SQL queries which retrieve the data for the shipping address aren't available in this template by default, so you will need to add this first.

You will need to make a note of the 'Message Template ID' for the email that you have created. You can find this by going into Presets, then System Setup >  Message Template, and filtering the 'Name' column for the name you have your email template. Make a note of the 'Message Template ID'.

Next, go back into Presets > System Setup, and then select Message Template SQL. You can then filter by 'ShippingAddress' in the Name column, and copy all all of the code in the 'SQL Text' column.

After copying the code, in the same 'Message Template SQL' screen, click the 'Add New' button in top left. Here you simply enter the 'Message Template ID' you made a note of earlier, paste in the SQL text, and give it a name (i.e. ShippingAddress).

Now if you go back into the Trigger Setup and edit your message template, under the 'Insert variable' option you should now see all the shipping address details available to select.

This is something which stumped me too - hope this helps!

[Stephen Phillips]

Well they don't make it easy :) I'll give it a shot.

[Stephen Phillips]

I followed steps but there are no templates with any name of ShippingAddress.

I was looking in database and found addresses but I cannot for the life of me find the order based on order # in the database let alone where they reference the shipping address.

[Stephen Phillips]

I found the SQL in another post. See below in case anyone else needs it.

exec('SELECT TOP (1) td.Ship_Name, td.Ship_Company, td.Ship_Add1, td.Ship_Add2, td.Ship_City, td.Ship_Zip, td.Ship_Phone, td.Ship_Fax, td.DeliveryPrice, td.ShippingTax, c.Name AS Country, p.Name AS State, p.Code AS State_Code, dps.Name AS Shipping_Service FROM DeliveryTentative AS td INNER JOIN Country AS c ON td.Ship_Country = c.CountryID INNER JOIN Province AS p ON td.Ship_State = p.ProvinceID INNER JOIN DeliveryProviderService AS dps ON td.DeliveryServiceID = dps.DeliveryProviderServiceID WHERE td.OrderID = '+@OrderId)  

[Wayne]

Orders may have Split shipping applied or contain external manufacturers so you may need to take this into account when creating a email templates and associated sql queries 

Regards,

Wayne