blastula
blastula copied to clipboard
rstudio
{gt} table missing colours in Outlook Desktop due to missing PR_HTML MAPI property
gt table renders as designed when viewed in mobile devices and Outlook365, but loses colour in Outlook Desktop.
Reprex
library(blastula)
library(glue)
library(tidyverse)
library(gt)
# create raw data
df <- tibble(`Non Risky` = c(9, 12, 21), `Risky` = c(1, 0, 1)) %>%
mutate(
Total = `Non Risky` + Risky,
`At Risk Percentage` = round(Risky / Total * 100, 2)
)
# create table object for email attachment
tbl <- df %>%
gt() %>%
tab_style(
style = cell_text(color = "#f8f8ff"),
locations = cells_column_labels(columns = everything())
) %>%
tab_options(
row.striping.include_table_body = FALSE,
column_labels.font.weight = "bold",
column_labels.background.color = "#000000"
) %>%
# fmt_percent(vars(`At Risk Percentage`), decimals = 1) %>%
data_color(
columns = vars(Risky),
colors = scales::col_bin(
c("#ff0000", "#458B00"),
bins = 2,
domain = c(0, 100)
),
alpha = 0.8
) %>%
as_raw_html()
date_time <- add_readable_time()
email <-
compose_email(
body = tbl,
footer = md(c("Email sent on ", date_time, "."))
)
email
Outlook365
Outlook Desktop
Issue
According to Microsoft support team:
The MAPI Property PR_HTML is missing on the attached message.
Without it, the Outlook application is unable to load the "HTML" message and actually display it, this means that we will rely on the PR_BODY_W property to display the body, which in this case is not going to be able to perform as we expect. Applying the PT_HTML content of the message to the message, it now displays correctly.
This MAPI property needs to be created when generating the email message.
References
It looks like the mail should be sent not using the body attribute, but as a html attachment and that is used for the body currently.
https://stackoverflow.com/questions/15828226/sending-messages-with-html-contents-using-the-mapi-control-in-vb6
It looks like the mail should be sent not using the body attribute, but as a html attachment and that is used for the body currently.
https://stackoverflow.com/questions/15828226/sending-messages-with-html-contents-using-the-mapi-control-in-vb6
@yonicd : thanks for comment; seems like you may have a solution 😆 Any guidance would be 🙏
I tried the below previously, but with no success; when tested on mobile and desktop mail client, attachment doesn't render on its own, like some .html would.
library(ggplot2)
library(blastula)
plot <-
ggplot(
data = mtcars,
aes(
x = disp, y = hp,
color = wt, size = mpg
)
) +
geom_point()
plot_html <-
add_ggplot(plot_object = plot)
htmltools::save_html(plot_html, "plot_html")
email <-
compose_email(
body = ""
)
email <- add_attachment(email,
"plot_html",
content_type = mime::guess_type("plot_html")
)
email %>%
smtp_send(
to = "[email protected]",
from = "[email protected]",
subject = "Test",
credentials = creds_key("me")
)
looks like this is a more basic problem.
The email is sent out internally by curl::send_mail which converts the message into a format called RF 2822
Searching for that format with HTML gave back this hit in wiki
Email software that complies with RFC 2822 is only required to support plain text, not HTML formatting. Sending HTML formatted emails can therefore lead to problems if the recipient's email client does not support it. In the worst case, the recipient will see the HTML code instead of the intended message.
In my case this was what happened with % that i made with gt::fmt_percent not being rendered in the outlook client.
Which is a long winded way of saying that this issue probably wont be resolved any time soon.
Maybe @jcheng5 or @jeroen the maintainers of {curl} would have more insight into this problem.
option b would be to port the table to an image and embed the image in the mail a la {reprex}
library(dplyr)
library(gt)
df <- tibble(`Non Risky` = c(9, 12, 21), `Risky` = c(1, 0, 1)) %>%
mutate(
Total = `Non Risky` + Risky,
`At Risk Percentage` = round(Risky / Total, 2)
)
# create table object for email attachment
img_file_html <- df %>%
gt() %>%
fmt_percent(vars(`At Risk Percentage`), decimals = 1)%>%
gt::gtsave('tbl2.png')%>%
add_image(file = 'tbl.png')
email <-
compose_email(
body = md(
c(
"Hello,
Here is an table:\n",
img_file_html
)
)
)
email%>%
blastula::smtp_send(
to = '[email protected]',
from = '[email protected]',
subject = 'html test',
credentials = blastula::creds_key('me')
)
option b would be to port the table to an image and embed the image in the mail a la {reprex}
I was thinking along the same line, similar to gt::gtsave(), somehow using webshot.
Just got to do more experimenting, since I'm generating the table from raw html in this case, not gt object.
I don't think curl does any conversion? The manual just states that you need to use RFC2822 for the email header format, i.e. the from, to, cc, bcc, subject. But I think curl will send whatever body content that you give it....
@jeroen would converting the content to raw do anything to the formating? (that sounds implausible)
library(dplyr)
library(gt)
df <- tibble(`Non Risky` = c(9, 12, 21), `Risky` = c(1, 0, 1)) %>%
mutate(
Total = `Non Risky` + Risky,
`At Risk Percentage` = round(Risky / Total, 2)
)
# create table object for email attachment
tbl <- df %>%
gt() %>%
fmt_percent(vars(`At Risk Percentage`), decimals = 1)
email <-
compose_email(
body = tbl
)
Output in Outlook Office 365
If I open the email into the browser:
session info
- Session info ----------------------------------------------------------------------------
setting value
version R version 4.0.2 (2020-06-22)
os Windows 10 x64
system x86_64, mingw32
ui RStudio
language (EN)
collate English_United States.1252
ctype English_United States.1252
tz America/New_York
date 2020-10-29
- Packages --------------------------------------------------------------------------------
! package * version date lib source
P assertthat 0.2.1 2019-03-21 [?] CRAN (R 4.0.2)
backports 1.1.10 2020-09-15 [1] CRAN (R 4.0.2)
P base64enc 0.1-3 2015-07-28 [?] CRAN (R 4.0.0)
P blastula * 0.3.2 2020-05-19 [?] CRAN (R 4.0.2)
P callr 3.4.3 2020-03-28 [?] CRAN (R 4.0.2)
P cellranger 1.1.0 2016-07-27 [?] CRAN (R 4.0.2)
P checkmate 2.0.0 2020-02-06 [?] CRAN (R 4.0.2)
P cli 2.0.2 2020-02-28 [?] CRAN (R 4.0.2)
P clipr 0.7.0 2019-07-23 [?] CRAN (R 4.0.2)
P codetools 0.2-16 2018-12-24 [?] CRAN (R 4.0.2)
P colorspace 1.4-1 2019-03-18 [?] CRAN (R 4.0.2)
commonmark 1.7 2018-12-01 [1] CRAN (R 4.0.2)
P crayon 1.3.4 2017-09-16 [?] CRAN (R 4.0.2)
P curl 4.3 2019-12-02 [?] CRAN (R 4.0.2)
P data.table 1.13.0 2020-07-24 [?] CRAN (R 4.0.2)
P desc 1.2.0 2018-05-01 [?] CRAN (R 4.0.2)
P details 0.2.1 2020-09-08 [?] local
digest 0.6.27 2020-10-24 [1] CRAN (R 4.0.3)
P dplyr * 1.0.2 2020-08-18 [?] CRAN (R 4.0.2)
P ellipsis 0.3.1 2020-05-15 [?] CRAN (R 4.0.2)
P evaluate 0.14 2019-05-28 [?] CRAN (R 4.0.2)
P fansi 0.4.1 2020-01-08 [?] CRAN (R 4.0.2)
P farver 2.0.3 2020-01-16 [?] CRAN (R 4.0.2)
P flextable 0.5.11 2020-09-09 [?] CRAN (R 4.0.2)
P forcats 0.5.0 2020-03-01 [?] CRAN (R 4.0.2)
P fs 1.5.0 2020-07-31 [?] CRAN (R 4.0.2)
P gdtools 0.2.2 2020-04-03 [?] CRAN (R 4.0.2)
P generics 0.0.2 2018-11-29 [?] CRAN (R 4.0.2)
P getPass 0.2-2 2017-07-21 [?] CRAN (R 4.0.2)
P ggplot2 3.3.2 2020-06-19 [?] CRAN (R 4.0.2)
P ggrepel 0.8.2 2020-03-08 [?] CRAN (R 4.0.2)
P glue * 1.4.2 2020-08-27 [?] CRAN (R 4.0.2)
P gt * 0.2.2 2020-08-05 [?] CRAN (R 4.0.2)
P gtable 0.3.0 2019-03-25 [?] CRAN (R 4.0.2)
P gtsummary 1.3.5 2020-09-29 [?] CRAN (R 4.0.2)
P haven 2.3.1 2020-06-01 [?] CRAN (R 4.0.2)
P here 0.1 2017-05-28 [?] CRAN (R 4.0.2)
P hms 0.5.3 2020-01-08 [?] CRAN (R 4.0.2)
P htmltools 0.5.0 2020-06-16 [?] CRAN (R 4.0.2)
P httr 1.4.2 2020-07-20 [?] CRAN (R 4.0.2)
P igraph 1.2.5 2020-03-19 [?] CRAN (R 4.0.2)
P jsonlite 1.7.1 2020-09-07 [?] CRAN (R 4.0.2)
P keyring 1.1.0 2018-07-16 [?] CRAN (R 4.0.3)
P knitr 1.30 2020-09-22 [?] CRAN (R 4.0.3)
P labelled 2.7.0 2020-09-21 [?] CRAN (R 4.0.3)
P lifecycle 0.2.0 2020-03-06 [?] CRAN (R 4.0.2)
P magrittr * 1.5 2014-11-22 [?] CRAN (R 4.0.2)
P mime 0.9 2020-02-04 [?] CRAN (R 4.0.0)
P munsell 0.5.0 2018-06-12 [?] CRAN (R 4.0.2)
P officer 0.3.14 2020-09-07 [?] CRAN (R 4.0.2)
P pillar 1.4.6 2020-07-10 [?] CRAN (R 4.0.2)
P pkgconfig 2.0.3 2019-09-22 [?] CRAN (R 4.0.2)
P plyr 1.8.6 2020-03-03 [?] CRAN (R 4.0.2)
P png 0.1-7 2013-12-03 [?] CRAN (R 4.0.0)
P processx 3.4.3 2020-07-05 [?] CRAN (R 4.0.2)
P ps 1.3.4 2020-08-11 [?] CRAN (R 4.0.2)
P purrr 0.3.4 2020-04-17 [?] CRAN (R 4.0.2)
P qs 0.23.3 2020-10-05 [?] CRAN (R 4.0.3)
P R6 2.4.1 2019-11-12 [?] CRAN (R 4.0.2)
P RApiSerialize 0.1.0 2014-04-19 [?] CRAN (R 4.0.0)
P Rcpp 1.0.5 2020-07-06 [?] CRAN (R 4.0.2)
PD RcppParallel 5.0.2 2020-06-24 [?] CRAN (R 4.0.2)
P readr 1.3.1 2018-12-21 [?] CRAN (R 4.0.2)
P readxl 1.3.1 2019-03-13 [?] CRAN (R 4.0.2)
P rematch2 2.1.2 2020-05-01 [?] CRAN (R 4.0.2)
P remedy 0.1.1 2020-08-26 [?] github (thinkr-open/remedy@eb8fbdd)
P renv 0.12.0 2020-08-28 [?] CRAN (R 4.0.2)
rlang 0.4.8 2020-10-08 [1] CRAN (R 4.0.3)
rmarkdown 2.4.4 2020-10-15 [1] Github (rstudio/rmarkdown@64e8307)
P rprojroot 1.3-2 2018-01-03 [?] CRAN (R 4.0.2)
P rstudioapi 0.11 2020-02-07 [?] CRAN (R 4.0.2)
sasdrive 0.0.1 2020-10-15 [1] Github (sagerx/sasdrive@a788cda)
P sass 0.2.0 2020-03-18 [?] CRAN (R 4.0.2)
P scales 1.1.1 2020-05-11 [?] CRAN (R 4.0.2)
P sessioninfo 1.1.1 2018-11-05 [?] CRAN (R 4.0.2)
P snakecase * 0.11.0 2019-05-25 [?] CRAN (R 4.0.2)
P stringfish 0.14.2 2020-09-05 [?] CRAN (R 4.0.3)
P stringi 1.4.6 2020-02-17 [?] CRAN (R 4.0.0)
P stringr 1.4.0 2019-02-10 [?] CRAN (R 4.0.2)
P systemfonts 0.3.2 2020-09-29 [?] CRAN (R 4.0.2)
targets * 0.0.0.9002 2020-10-27 [1] Github (wlandau/targets@fd9b7a4)
P tibble 3.0.3 2020-07-10 [?] CRAN (R 4.0.2)
P tidyr 1.1.2 2020-08-27 [?] CRAN (R 4.0.2)
P tidyselect 1.1.0 2020-05-11 [?] CRAN (R 4.0.2)
P utf8 1.1.4 2018-05-24 [?] CRAN (R 4.0.2)
P uuid 0.1-4 2020-02-26 [?] CRAN (R 4.0.0)
P vctrs 0.3.4 2020-08-29 [?] CRAN (R 4.0.2)
P webshot 0.5.2 2019-11-22 [?] CRAN (R 4.0.2)
withr 2.3.0 2020-09-22 [1] CRAN (R 4.0.2)
P xfun 0.18 2020-09-29 [?] CRAN (R 4.0.3)
P xml2 1.3.2 2020-04-23 [?] CRAN (R 4.0.2)
P yaml 2.2.1 2020-02-01 [?] CRAN (R 4.0.2)
P zip 2.1.0 2020-08-10 [?] CRAN (R 4.0.2)
[2] C:/Users/jonathan.sidi/AppData/Local/Temp/Rtmp0MRN9C/renv-system-library
P -- Loaded and on-disk path mismatch.
D -- DLL MD5 mismatch, broken installation.
Hi all - any progress on resolving this, i'm running into the same issues with kable, kableExtra , and formattable. Using raw html seems to work for gt (and scales well) but not for the gtextras package of which the css is stripped on outlook. Would prefer not to add image as solution as this doesn't scale well on mobile.
Well after looking a lot into it, it seems that the issue is outlook 365 not rendering in html but in rich text files which don't have the same CSS propeties.
More here -> https://stackoverflow.com/questions/38890039/office365-outlook-mail-html-email-render
So following up on this in 2023, are there any R packages that reliably produce formatted tables (especially conditional formatting) that will render properly in outlook 365? My experiences so far:
- Kable+KableExtra are semi-functional. Most things are broken (including spacing and separations) but you will get conditional formatting on what looks like plain text.
- GT seems about the same.
- xtable will produce something that actually looks like a table, but will be somewhat mangled in terms of spacing/wrapping and has very little customizability.
About the only thing I've found reliable with outlook365 is attaching an html file, or embedding a non-interactive image.
