How to send transactional emails with rows from DB in body

Question

I am working on sending out order confirmations on a web site with a shop functionality - all pretty much custom designed. I would like an email to be sent out once the order has been placed, ideally with a brief summary/overview of the ordered items.

I have managed to get to the point where the submission of the order triggers an email to be sent to the user, so that is all good. However, my question is how can I customize the email body in a way so it would include the list of ordered items?

Happy to add code from my controller, although I don't really think this is adding much value as this is more of a how-to-question rather than an issue I am encountering.

int orderID = order.ID;
var lineItems = DATADB.LineItemList.Where(x => x.OrderNumber == 0 && x.UserID == userID);
lineItems.ForEach(l => l.OrderNumber = orderID);
DATADB.SaveChanges();

// send order confirmation email
var orderConfirmation = DATADB.LineItemList.Where(x => x.OrderNumber == orderID).ToList();
var ordered = string.Join(",", lineItems);
var msg = new SendGridMessage();
msg.From = new System.Net.Mail.MailAddress("orders@freshNclean.ch", "freshNclean");
msg.AddTo(UserManager.FindById(userID).Email);
msg.Subject = "Deine Bestellung bei freshNclean";
msg.Text = "Hi " + UserManager.FindById(userID).FirstName.ToString() + "! Vielen Dank für Deine Bestellung." + ordered;

// SendGrid credentials
var credentials = new NetworkCredential(ConfigurationManager.AppSettings["SGaccount"], ConfigurationManager.AppSettings["SGpassword"]);
var transportWeb = new Web(credentials);
if (transportWeb != null)
{
    transportWeb.DeliverAsync(msg);
}
else
{
    Trace.TraceError("Web Transport konnte nicht generiert werden - die Nachricht wurde nicht versandt.");
    Task.FromResult(0);
}

This is an opinionated question as there is no single answer. I have, however, used a lot in the past simple HTML files that contain the body of the message with placeholders. As you are using ASP.NET MVC, you can take that further and create a view with Razor and then render that view to HTML to send as the body — Camilo Terevinto, Jan 10 '19 at 16:24
send html emails. use something like [this](https://stackoverflow.com/a/19683749) to build the body — user326608, Jan 10 '19 at 16:26
@user326608 Nobody in 2019 should be using DataTable :) even MarcGravel says so. Let that old stuff die already — Camilo Terevinto, Jan 10 '19 at 16:27
lol yes you're quite correct, its more the iterating a collection into an html table aspect i was after — user326608, Jan 10 '19 at 16:30
Thx, that is more in my comfort zone as it seems to be built in a similar way as a typical site. — Ken-F, Jan 10 '19 at 16:42

Manoj Choudhari · Answer 1 · 2019-01-10T16:26:04.357

1

Step 1: Get the data in form of objects (if possible in XML format).

Step 2: Create XSLT template to convert this XML format into HTML

Reference: Simplest way to transform XML to HTML with XSLT in C#?

public static string TransformXMLToHTML(string inputXml, string xsltString)
{
    XslCompiledTransform transform = new XslCompiledTransform();
    using(XmlReader reader = XmlReader.Create(new StringReader(xsltString))) {
        transform.Load(reader);
    }
    StringWriter results = new StringWriter();
    using(XmlReader reader = XmlReader.Create(new StringReader(inputXml))) {
        transform.Transform(reader, null, results);
    }
    return results.ToString();
}

STEP 3: Set HTML contents to output of previous method:

        var apiKey = some-api-key;
        var client = new SendGridClient(apiKey);
        var from = new EmailAddress("test@example.com", "Example User");
        var subject = "Sending with SendGrid is Fun";
        var to = new EmailAddress("test@example.com", "Example User");
        var plainTextContent = "and easy to do anywhere, even with C#";
        var htmlContent = GetHTMLFromXML();
        var msg = MailHelper.CreateSingleEmail(from, to, subject, plainTextContent, htmlContent);
        var response = await client.SendEmailAsync(msg);

Hope this helps.

edited Jan 10 '19 at 16:26

answered Jan 10 '19 at 16:14

Manoj Choudhari

5,277
2
26
37

2

I love that the copy-pasted code is so wrong it doesn't even care about the basics of SMTP. `UseDefaultCredentials = true` is *the opposite* of setting `Credentials` to anything other than `null`. Please learn to NOT copy-paste bad code. Also, did you even notice the question is about SendGrid, so the entire block of code regarding SMTP is unwated? – Camilo Terevinto Jan 10 '19 at 16:17
1

@CamiloTerevinto- thanks. I have updated the send grid code - from SendGrid documentation. But conceptually, i believe it is the same steps. You will have to use XSLT to get the HTML and include that in email. Hope this works. – Manoj Choudhari Jan 10 '19 at 16:27
1

Nah, noone should build HTML from XML, that's thinking in the 2000's. When you can use the beauty of Razor to build proper HTML with CSS, this is just wrong – Camilo Terevinto Jan 10 '19 at 16:29
Thx manojchoudhari and @camiloterevinto - can you provide an example? not sure I follow... – Ken-F Jan 10 '19 at 16:38
2

@Ken-F I was thinking of something like this: https://stackoverflow.com/questions/33123998/razor-view-page-as-email-template – Camilo Terevinto Jan 10 '19 at 16:40
We can use many approaches - one suggested above, or if you are able to run angular then you can use angular to generate html template. Hope this helps. – Manoj Choudhari Jan 10 '19 at 17:28

How to send transactional emails with rows from DB in body

1 Answers1