1

I have this data frame:

library(sendmailR)
library(pander)

dput(s)
structure(list(Description = c("ServerA", "ServerB", "ServerC", 
"ServerD", "ServerE", "ServerF"), Value = c("2", "2", "100", 
"100", "80", "20")), .Names = c("Description", "Value"), row.names = c(NA, 
6L), class = "data.frame")

I would like to put this data frame in a nice looking table and email it to some people.

I tried it with pandoc but the table looks very plain:

 t<-pandoc.table.return(s, caption="Server CPU Utilization")

    from <- "user@example.com"
    to <- c("end_users@example.com")
    subject <- paste(Sys.time()," Servers CPU utilization")
    body <- t                
    mailControl=list(smtpServer="mailhost.example.net")

    sendmail(from=from,to=to,subject=subject,msg=body,control=mailControl)

Is there any other way to format a data frame into nice looking table to send as email? Table has to be in the email body, not as an attachment.

user1471980
  • 10,127
  • 48
  • 136
  • 235

2 Answers2

5

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(' ', '&nbsp;', 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))
Community
  • 1
  • 1
daroczig
  • 28,004
  • 7
  • 90
  • 124
  • I executed the exact code you poste here. I am getting this error: Error in wait_for(code) : SMTP Error: 5.7.1 Unable to relay for – user1471980 May 05 '14 at 15:38
  • @user1471980 you might have to pass some custom options to `control` like you did in your original example: `mailControl=list(smtpServer="mailhost.example.net")` as a further parameter to `sendmail`. The error message has nothing to do with the content or the formatting of the body. – daroczig May 05 '14 at 15:45
  • I've tried to used css within the head section of the above html to format my table, for some reason, when I get the mail, the table looks still the same. Is it possible to insert some css for table formating and seeing it on when you receive the email. For example background colors of tr etc. – user1471980 May 06 '14 at 20:48
  • @user1471980 the e-mail clients tend to ignore the CSS in the header section, and you have to use inline CSS styles to color the cells. – daroczig May 06 '14 at 21:42
  • from the above code you've provided, this line creates the table: print(xtable(s), type = 'html'). How would you put inline styling? A quick example really would be very useful. – user1471980 May 07 '14 at 01:24
  • @user1471980 I'm not aware of any R package that let you define inline CSS of cells, but you may update your cells to e.g. have a `span` HTML tag to specify the color of the text etc. before passing to `xtable`. So convert your values to text and add the required HTML code before calling `xtable`, or apply some regexp afterwards. – daroczig May 07 '14 at 13:21
0

if dput is your data.frame.

I use this.

Date=sys.Date()-1
date2 <- paste("My subject of mail", Date, sep = " - ")
setwd("/xyz")
newdir <- paste("output", Sys.time(), sep = "_")
dir.create(newdir)#, showWarnings = FALSE)
setwd(newdir)

######
mydoc = bsdoc( title = 'my document')
options( "ReporteRs-fontsize" = 8 )
mydoc = addParagraph(mydoc, value = "Hi All, \n\nPlease check attached summary.")
mydoc = addParagraph(mydoc, value = "Summary:")
MyFTable = FlexTable( data = dput, add.rownames = FALSE, header.cell.props = cellProperties( background.color = "#FAEBD7" )
                      , header.par.props = parProperties(text.align = "center" ))
MyFTable = setColumnsColors( MyFTable, j=1, colors = '#F0F8FF' )
MyFTable[ , ] = parProperties( text.align = 'center')
MyFTable = setColumnsColors( MyFTable, j=ncol(dput), colors = '#F0F8FF' )
mydoc = addFlexTable( mydoc, MyFTable )
writeDoc( mydoc, file = "op2.html" )

send.mail(from = "abc@xyz.com",
          to = c("abc@xyz.com"),
          subject = date2,
          body = "op2.html",
          html = TRUE,
          smtp = list(host.name = "smtp.gmail.com", port = 465, user.name = "abc@xyz.com", passwd = "xyz@123", ssl = TRUE),
          authenticate = TRUE,
          send = TRUE)

But i'm looking even better option to send mail, like as image in mail body or nicely formatted/compacted.

Vishan Rana
  • 307
  • 2
  • 4
  • 9