Html table output formatting when sending email from Microsoft Outlook using R

Question

I am trying to convert a dataframe into an html table using the htmlTable package and then send an email using Microsoft Outlook as the email client using RDCOMClient package by appending the html table as the body of the email. I am new to HTML coding so I am not very familiar with how to format the table with HTML tags. Below is an example of what I am trying to do.

# Library to send email from Microsoft Outlook
library(RDCOMClient)
# Library to create HTML table
library(htmlTable)

# Reading data from inbuilt 'mtcars' dataset
x <- head(mtcars)

# Create HTML table
y <- htmlTable(x,
               rnames = FALSE,
               caption="This is from htmlTable package",
               align = paste(rep("c", ncol(x)), collapse = "|"),
               align.header = paste(rep("c", ncol(x)), collapse = "|"))

# add the table as body of the email
body <- paste0("<html>", y, "</html>")

## init com api
OutApp <- COMCreate("Outlook.Application")
## create an email 
outMail = OutApp$CreateItem(0)
## configure  email parameter 
outMail[["To"]] = "test@test"
outMail[["subject"]] = "TEST"
outMail[["HTMLbody"]] = body
## send it                     
outMail$Send()

This above code works and I even get the email output as shown below.

Output from email sent using above code in Microsoft Outlook

My question is, how to I format this table? I want the output to be in a nice table format with row and columns separated by lines. I can add column separator lines as seen in the output, but I am unable to add row separator lines. I also want to adjust the line spacing between the rows and columns, and change the formatting of the fonts to calibri 11. Below is the output that I am looking for.

Desired Output with rows and columns formatted

Any help on how I can achieve this using htmlTable package or any other workaround would be really appreciated. Thanks a lot in advance.

UPDATE: Below is the solution, thanks to valuable inputs provided by @Syfer.

library(RDCOMClient)
library(htmlTable)

x <- head(mtcars)
html_y <- htmlTable(x, rnames = FALSE)

body <- paste0("<html><head>
               <style>
               body{font-family:Arial, \"sans-serif\";}
               table{border-left:1px solid #000000;border-top:1px solid #000000;}
               table td{border-right:1px solid #000000;border-bottom:1px solid #000000;font-size:12px; font-weight:normal;}
               table th{border-right:1px solid #000000;border-bottom:1px solid #000000;font-size:14px;}
               </style>
               </head><body>",
               html_y, 
               "</body></html>")

OutApp <- COMCreate("Outlook.Application")
outMail = OutApp$CreateItem(0)
outMail[["To"]] = "test@test.com"
outMail[["subject"]] = "TEST EMAIL"
outMail[["HTMLbody"]] = body
outMail$Send()

Final output in Microsoft Outlook likes this.

Syfer · Accepted Answer · 2017-11-14T05:17:23.317

2

I havent done 'r' but the syntax looks logical so I will take a stab at the solution:

body <- paste0("<html><head><style>body{font-family:Arial, "sans-serif";}table{border-left:1px solid #000000;border-top:1px solid #000000;}table td{border-right:1px solid #000000;border-bottom:1px solid #000000;font-size:16px; font-weight:normal;}table th{border-right:1px solid #000000;border-bottom:1px solid #000000;font-size:16px; font-weight:bold;}</style></head><body>", y, "</body></html>")

Basically i have added a few things to your code:

head of the html document which contains the style for the HTML document that you will sending out.
start and end tag for body.

<style>
    body{font-family:Arial, "sans-serif"}
    table{border-left:1px solid #000000;border-top:1px solid #000000;}
    table td{border-right:1px solid #000000;border-bottom:1px solid #000000;font-size:16px; font-weight:normal;}
    table th{border-right:1px solid #000000;border-bottom:1px solid #000000;font-size:16px; font-weight:bold;}
</style>

From the above CSS you should be able to the border colors and fonts to suit your liking. Once the table is rendered in emails it should show something similar to:

Let me know if that worked for you.

Cheers

edited Nov 14 '17 at 05:17

answered Nov 13 '17 at 00:51

Syfer

4,262
3
20
37

Thanks a lot for taking the time to answer this @Syfer. I personally do not have much experience working with HTML code. I will give your solution a try and see how I can fit it into my R program. I will let you know how it goes. – Code_Sipra Nov 13 '17 at 07:24
Hey @Syfer! Your solution works for the most part. I used this code - `body <- paste0("", y, "")`. However, the table headers do not seem to have any borders. Also, the caption for the table should not get the borders. Any way to fix it? I am not sure how to paste a screenshot here (I'm a newbie) Thanks again for the help. – Code_Sipra Nov 14 '17 at 05:06
1

if you are using th for headers then use this CSS: `table th{border-right:1px solid #000000;border-bottom:1px solid #000000;}`. Let me know if this works. – Syfer Nov 14 '17 at 05:08
Wow! This worked. I never knew HTML/CSS and R can get along so well! I just removed the caption out from the body and it works fantastic. Just one last question. How do i adjust the font sizes? If you could help me with this piece too, you would solve this problem for me. – Code_Sipra Nov 14 '17 at 05:15
I updated my answer and added font size for td and th for you to change individually ;-) – Syfer Nov 14 '17 at 05:17
Thanks a lot, sir! You taught me something new today. I am going to share this knowledge with my colleagues as we have been turning the world upside down solving this outlook table problem. I am updating my post with the final solution. It may help someone else just like me who got stuck with formatting tables in email body that was generated in R. – Code_Sipra Nov 14 '17 at 05:27

Html table output formatting when sending email from Microsoft Outlook using R

1 Answers1

Linked