What I ultimately want to achieve is to be able to send “html reports” by email as email content. In fact such a node would be a very helpful addition. Because sending reports as attachments isn’t so nice if they should give the end-user a quick overview. Attachment means opening the attachment.This issue however is deals with trying to work around this not being possible.
All I want to send is a knime table as html that contains images. Images meaning the Lhasa table to html node doesn’t work. I even made a columns that contains html img with base64 png (or svg). Turns out gmail does not render either of these (and many other clients also don’t do that but gmail compatibility is all I need).
So for gmail to show the images correctly according to my research they need to be send as embedded attachments like shown here.(not the accepted answer but the on e below it)
So the question is how can I use KNIME to send an email in the format described in the link?
Answering my own question, it’s possible but complex.
Sort table in the desired order
convert your pngs to binary objects
(there would probably be a different option of doing it by saving them to disk)
Java Snippet
This does 2 things.
It converts the binary objects(pngs) to base64 and it replaces (or adds) a column that will be used to contains the images in the html table. The content is alreay html (as string).
try {
ByteArrayOutputStream buffer = new ByteArrayOutputStream();
int nRead;
byte[] data = new byte[4096];
while ((nRead = c_Structure.read(data, 0, data.length)) != -1) {
buffer.write(data, 0, nRead);
}
out_base64_image = Base64.getEncoder().encodeToString(buffer.toByteArray());
out_html_image = "<img src=\"cid:image-" + ROWINDEX + "\">";
} catch (IOException ex) {
throw new Abort(ex);
}
cid = content id which is needed to display attached images inline of the email. So the content id simply is image-0, image-1 etc. Now you see why sorting at the start is required.
Table to HTML node and list of base64 images
The Table to html node is used to convert the table to html. A second branch uses groupby node without group column to generate a list column containing all the base64 encoded pngs. Then the 2 branches are joined again with column appender.
This is done because the email is sent in a java snippet and hence we only want 1 row to send the email only once. Same is done for email adresses. they are appended as a list column to this table. This is a good point to show the workflow as it’s probably getting confusing by now:
Java Snippet to send email
Here we use java mail to send the email. You will need the javax.mail.jar with must be added as additional library to the snippet. The code inside the snippet see below:
try {
// adjust accordingly to your smtp server
Properties mailProps = new Properties();
mailProps.put("mail.transport.protocol", "smtp");
mailProps.put("mail.host", "smtp.mydomain.com");
mailProps.put("mail.smtp.port", "25");
mailProps.put("mail.smtp.auth", "false");
Session session = Session.getDefaultInstance(mailProps);
Message m = new MimeMessage(session);
MimeMultipart content = new MimeMultipart();
MimeBodyPart mainPart = new MimeBodyPart();
mainPart.setText(c_HTML, "UTF-8", "html"); // set html table as main email text
content.addBodyPart(mainPart);
// attach all the images
int idx = 0;
for (String b64 : c_base64_image) { // this is the list of base64 images
MimeBodyPart imgPart = new PreencodedMimeBodyPart("base64"); // we already have base64 encoded images
imgPart.setContent(b64, "image/png");
imgPart.setContentID("<image-" + idx + ">"); // this is the cid from first java snippet
imgPart.setDisposition(MimeBodyPart.INLINE); // tell that the images are displayed inline
content.addBodyPart(imgPart);
idx++;
}
m.setContent(content);
m.setSubject("My Subject");
m.setFrom(new InternetAddress("knime@knime.com"));
for (String email : c_email) { // list of email addreses
m.addRecipient(RecipientType.TO, new InternetAddress(email));
}
// It seems knime/eclipse already has loaded some different java mail library
// This solution to that problem was found on Stackoverflow
// if this is omitted sending fails with a nonsensical/confusing error message
Thread t = Thread.currentThread();
ClassLoader ccl = t.getContextClassLoader();
t.setContextClassLoader(session.getClass().getClassLoader());
try {
Transport.send(m);
} finally {
t.setContextClassLoader(ccl);
}
} catch (MessagingException ex) {
throw new Abort(ex);
}
This will send your knime table per email with images correctly displayed inside the table.
If above doesn’t work mainly because the generated HTML is very plain, another option instead of the Table to HTML node is to use a Python Scripting node. The node already puts your table into a pandas dataframe. From there you can use the pandas styling feature to style your table (general look and feel, conditional formatting etc).
This style then renders to html with internal css. However again gmail and many other mail clients work with internal css. They only work with inline styles. Luckily this is a common issue and there is a python module premailer which converts internal styles to inline styles.
With these blocks one can build nicely styled html tables and send them per email.
In fact having this natively in KNIME server would be a huge enhancement or I’m missing it. Instead of sending PDF reports, the reports should be able to be send as HTML email. Especially for alerts. Else the users must click (and potential download) an attachment to see the content.
I recently updated the Table to HTML (String) node to add some styling to the table. I’ll migrate the change to the release branches (it’s in trunk/nightly only at the moment).
I did have an internal change that accepted PNG columns and created the base64 img tags but I stopped working on it when I realised email clients refused to render them. Maybe it’s worth taking another look with what you’ve been able to work out!
The png/image part is problematic. I don’t think you can solve that on your side only. To make it work the email node would have to be enhanced, a lot.
Following is actually more for the knime guys than you:
I see a couple of changes needed, some might already be there but it’s not always easy to keep knime server on newest version when it’s out of your own control. The automatic email after worklfow completion can’t be customized (maybe it can in newest version?) so what we need to do is use a Call local/remote workflow node and have it return the report and then use send email to send a custom email.
So for Server it would be great if the sent email could be more customized and allows including the report as html in the email.
The call workflow nodes can’t return the report as html. This would also be helpful
Improve Send email node
If the email content is set to HTML, the node should itself apply the changes I explained above:
images should be converted to inline attachments via cid
all html styles must be inlined
This would make it easy to send table contents via email especially if they contain images. Also one could generated html reports outside of BIRT and also send them.
Yes that is true, but I think the changed help with the styling of the table at least? Calling out to python for styled tables isn’t ideal if you don’t have to?
In any case the initial update with hard coded pre-defined inline styles should now be in KNIME 4.0 and 4.1 releases.
Currently building for nightly/trunk is a new addition allowing a user to specify styling of certain elements of the table as requested by @natanzi.
Ah that’s a shame. But thank you very much for your work and Java Snippet. I’ve been able to use it and adapt the snippet along with the Table to HTML String node to create a KNIME 4.1 component (probably works in KNIME v3.x as a wrapped meta node).
This feeds into your Java snippet and will send an email that outlook renders like this (presumably Gmail will to - it does based on my forwarded email):
