Email dataframe as table in email body with SendMailR

Asked 21/2, 2014 at 8:31 Answered 25/4, 2021 at 18:27

I am trying to send a dataframe with SendMailR. I can send it as an attachment with reasonably good formatting. However I would like to send the dataframe in the body of the email. I tried capture.output, print, sprintf but am not even able to get the format close.

e.g. I tried the following syntax

for (i in 1:nrow(df)){
 MSG = c(MSG,rownames(df)[1],as.character(unlist(df[i,])),'\n')
} 
MSG = sprintf('%-10s',MSG)
sendmail(from,to,subject,msg = list(MSG,attachment1,attachment2 ... ))

In other words, I am thinking that it might be necessary to convert my dataframe into a format with /n and sprintf('s-10%') etc and store it in MSG. Can someone point me in the right direction?

Allergy answered 21/2, 2014 at 8:31 Comment(2)

I would suggest you to create a HTML table, but I believe that you can't send HTML mail with sendmailR. – Bergwall 21/2, 2014 at 8:49

Thanks Julien. Just as an edit, I just need to send the results of a job via email daily. I am happy to use another mail sender / change data frame to data table/matrix etc as long as i can send the content in the email body. Happy to consider any alternatives – Allergy 21/2, 2014 at 9:4

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    <- '<[email protected]>'
to      <- "<[email protected]>"
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/

Cooperate answered 21/2, 2014 at 9:40 Comment(3)

Thank you so much for your help. I didn't manage to get pandoc.return to work in email (format looked good in Rstudio but not in my email ... lines are misaligned) but html works well. – Allergy 23/2, 2014 at 7:18

@Allergy Maybe you could paste(..., collapse = '\n') the stuff from pander.return before adding to the e-mail, or collapse = '<br />' if sending HTML mail. – Cooperate 23/2, 2014 at 12:30

the correct name of the function is pander_return – Lenni 8/6, 2016 at 13:8

library "xtable" will help to attach a data frame as table in email. Try this

   library(xtable)

   body=print(xtable(dataframe,caption = "Heading for the table"), type="html", caption.placement = "top")

Plier answered 21/8, 2016 at 19:13 Comment(1)

I was struggling with the same issue. Thought some one with my condition will get some help. – Plier 22/8, 2016 at 17:19

As an alternative to sendmailR, here's an example using the gt package for converting the table to HTML and the blastula package for sending the email. Both packages are from RStudio: blastula and gt.

library(blastula)
library(gt)

tbl_html <- 
  mtcars %>% 
  gt() %>%
  as_raw_html()

email <-
  compose_email(
    body = blocks(tbl_html)
    )

email %>%
  smtp_send(
    to = '[email protected]',
    from = '[email protected]',
    subject = "Test email with table",
    credentials = creds_file("gmail_creds")
  )

To set the credentials the first time use (in this case using a gmail address):

create_smtp_creds_file(
  file = "gmail_creds",
  user = '[email protected]',
  provider = "gmail"
)

You can also use markdown in the body.

Scurvy answered 25/4, 2021 at 18:27 Comment(0)

Recommended topics

Hot tags