Email Dataframe as Table in Email Body with Sendmailr

  • Home
  • Languages
  • R
  • Email Dataframe as Table in Email Body with Sendmailr

Email dataframe as table in email body with SendMailR

Although sending HTML mails with sendmailR is not straighforward, but possible based on a mail discussion with the package author last year (thanks again to Olaf Mersmann for his kind help) - with simply overriding the Content-Type header. E.g.:

msg <- mime_part('<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
  <title>HTML demo</title>
  <style type="text/css">
  </style>
</head>
<body>
<h1>HTML demo</h1>
</body>
</html>')

## Override content type.
msg[["headers"]][["Content-Type"]] <- "text/html"

from    <- '<foo@example.com>'
to      <- "<bar@example.com>"
subject <- "HTML test"
body    <- list(msg)
sendmail(from, to, subject, body, ...)

On the other hand, there is no real need for HTML to present tables or a data.frame in a human-readable format. There is e.g. the ascii package or my pander pkg that can turn R objects to markdown. Quick demo:

> library(pander)
> panderOptions('table.split.table', Inf)
> pander(head(iris, 3))

-------------------------------------------------------------------
 Sepal.Length   Sepal.Width   Petal.Length   Petal.Width   Species 
-------------- ------------- -------------- ------------- ---------
     5.1            3.5           1.4            0.2       setosa  

     4.9             3            1.4            0.2       setosa  

     4.7            3.2           1.3            0.2       setosa  
-------------------------------------------------------------------

> pander(head(iris, 3), style = 'grid')

+----------------+---------------+----------------+---------------+-----------+
|  Sepal.Length  |  Sepal.Width  |  Petal.Length  |  Petal.Width  |  Species  |
+================+===============+================+===============+===========+
|      5.1       |      3.5      |      1.4       |      0.2      |  setosa   |
+----------------+---------------+----------------+---------------+-----------+
|      4.9       |       3       |      1.4       |      0.2      |  setosa   |
+----------------+---------------+----------------+---------------+-----------+
|      4.7       |      3.2      |      1.3       |      0.2      |  setosa   |
+----------------+---------------+----------------+---------------+-----------+

If you want to concatenate this to the e-mail body, use pander.return instead that returns character vector instead of writing to the console. And there are some other available table styles, also some useful panderOptions e.g. to set decimal mark, date format etc: http://rapporter.github.io/pander/

how do you convert a data frame to a table to send as an email body

What do you mean under?

the table looks very plain

You may also opt for some other markdown format for the table, like passing style = 'grid' to pandoc.table.return, if you do not like the default multiline format. Or you mean the table falls apart/looks ugly with a non-monospace font? The result will depend on the e-mail client, so I would rather opt for sending a HTML mail and specifying a monospace font family, or render the table in HTML.

A quick demo for the HTML version:

  1. Initialize the required R packages:

    library(sendmailR)
    library(xtable)

  2. Build a HTML body with concatenating the static part with dynamically created HTML table:

    msg <- mime_part(paste('<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
    Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
    </head>
    <body>', print(xtable(s), type = 'html'), ',</body>
    </html>'))

  3. Override the content-type with an undocumented hack:

    msg[["headers"]][["Content-Type"]] <- "text/html"

  4. Send the mail to your specified recipient with the given subject:

    from    <- '<foo@example.com>'
    to      <- '<bar@example.com>'
    subject <- 'HTML table in the body'
    body    <- list(msg)
    sendmail(from, to, subject, body)

Combining the markdown and HTML versions:

msg <- mime_part(paste('<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>
<body><div style="font-family: monospace;">', gsub(' ', ' ', paste(pander.return(s, caption = "Server CPU Utilization", style = 'grid'), collapse = '<br>')), '</div></body>
</html>'))
msg[["headers"]][["Content-Type"]] <- "text/html"
sendmail(from, to, subject, list(msg))

The trick here is to set the font-family to monospace with inline CSS, also replacing all spaces in the document with non-breaking space. Another (and rather more elegnat) workaround might be to put the markdown between pre HTML tags:

msg <- mime_part(paste('<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>
<body><pre>', paste(pander.return(s, caption = "Server CPU Utilization", style = 'grid'), collapse = '\n'), '</pre></body>
</html>'))
msg[["headers"]][["Content-Type"]] <- "text/html"
sendmail(from, to, subject, list(msg))

Adding multiple tables in Email using mailR (send.mail)

Suggested untested Solution: paste multiple pre-formatted tables:

final1 <- print(xtable(df1,caption = "Report"), type = "html", include.rownames = FALSE,
        caption.placement = getOption("xtable.caption.placement", "top"),
        html.table.attributes = getOption("xtable.html.table.attributes","border=1"))

final2 <- print(xtable(df2,caption = "Report2"), type = "html", include.rownames = FALSE,
        caption.placement = getOption("xtable.caption.placement", "top"),
        html.table.attributes = getOption("xtable.html.table.attributes","border=1"))

final3 <- print(xtable(df3,caption = "Report3"), type = "html", include.rownames = FALSE,
        caption.placement = getOption("xtable.caption.placement", "top"),
        html.table.attributes = getOption("xtable.html.table.attributes","border=1"))

final <- paste(final1, final2, final3, sep="\n")

date_of_report<- Sys.Date() - 1

send.mail(from = "no-reply@abc.com",
          to = c('xyz.pqr@abc.com'
          ),
          subject = paste('Report', date_of_report, sep=' '),
          body = final,
          html = TRUE,
          smtp = list(host.name = "aspmx.l.google.com", port = 25),
          authenticate = FALSE,
          send = TRUE,
          debug=TRUE)

GmailR Sending data.frame in body of email

You can use html_body() instead of the body argument.

Load the packages:

library(gmailr)
library(tableHTML)

Create an HTML table using tableHTML: 

msg = tableHTML(mtcars)

Add a paragraph before the table:

html_bod <- paste0("<p> This is a test email. </p>", msg)

Create a MIME message and send it:

mime() %>%
  to("1234@gmail.com") %>%
  from("1234@pharmeasy.in") %>%
  subject("Data City Wise") %>% 
  html_body(html_bod) %>% 
  send_message()

This is how the email looks like in gmail:

gmail_output

Send an email with dataframe as a flextable

This is because print method is displaying the flextable, it does not return the HTML value. The method format(fletable_obj, type = "HTML") return the HTML value.

You should modify the HTML creation as:

msgJP <- try(mime_part(paste('<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<title>HTML demo</title>
<style type="text/css">
</style>
</head>  
<body>',"hello,<br>","check out the data for self audit.","<br>",
format(flextable(samplemondata), type = "html"),
'</body>
</html>')))

sharing 2 data tables through sendmailR

Adding <p><pre>', paste(pander_return(pander(my_data2, style="multiline", caption = "New Caption")), collapse = '\n'), '</pre> between </pre> and </body> should give you the desired result.

When you want to insert some text between the two tables, you can do:

<p>the text you want to insert between the tables</p>
<pre>', paste(pander_return(pander(my_data2, style="multiline", caption = "New Caption")), collapse = '\n'), '</pre>

Send email from outlook by including dataframe as table in R

I finally found a solution to pasting a dataframe as an HTML table in Microsoft outlook. This is using the xtable package. Part of the solution credit goes to @lukeA from here - How to show an excel worksheet in outlook body by R

Below is the solution.

library(RDCOMClient)
library(xtable)

x <- head(mtcars)
y <- print(xtable(x), type="html", print.results=FALSE)

body <- paste0("<html>", y, "</html>")

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

This is how the output looks like in Microsoft outlook.

Screenshot from Microsoft Outlook


Related Topics

Time-Series - Data Splitting and Model Evaluation

How to Group by All But One Columns

How to Find Useful R Tutorials with Various Implementations

Knitr & Rmarkdown Docx Tables

R: Text Progress Bar in for Loop

Note in R Cran Check: No Repository Set, So Cyclic Dependency Check Skipped

Visualise Distances Between Texts

Use Pipe Without Feeding First Argument

Floor a Year to the Decade in R

Extracting Off-Diagonal Slice of Large Matrix

How to Specify "Does Not Contain" in Dplyr Filter

Conditionally Replacing Column Values with Data.Table

Apply Grouped Model Back Onto Data

Ggplot2:Adding Two Errorbars to Each Point in Scatterplot

Why Do Logicals (Booleans) in R Require 4 Bytes

Why Does Median Trip Up Data.Table (Integer Versus Double)

How to Call External R Script from R Markdown (.Rmd) in Rstudio

R List Get First Item of Each Element


Leave a reply



Submit