In large-scale multicentre reseach, communication with data collectors in a meaningful way can be challenging. Often, group-specific emails (with group-specific attachments) can be desired (for example reports of missing data for a particular site). Yet there is a limited number of non-proprietary softwares that allow this to be automated at scale, and often this is required to be done on a manual basis.
CollaboratoR has several functions that have been designed to work together to faciliate the process of sending group-specific emails (including attachments). This has been developed with interoperability with REDCap in mind, but the “email_” functions do not require REDCap to work.
Why would you choose to do this over mailmerge or other equivalent software?
The first step is to define the groups of people that will be emailed.
For projects on REDCap, all users with access rights to each data access group (DAG) can be accessed via the API.
This can be done via the user_role()
CollaboratoR function (see
Redcap User Management: 1. Explore Current
Users
for more details), alongside several other functions from other packages
(
redcapAPI
/
REDCapAPI
/ RCurl
).
df_user_all <- collaborator::user_role(redcap_project_uri = Sys.getenv("collaborator_test_uri"),
redcap_project_token = Sys.getenv("collaborator_test_token"))$full
knitr::kable(head(df_user_all, n=10)) # Please note all names / emails have been randomly generated
| role | username | email | firstname | lastname | expiration | data_access_group | data_access_group_id | design | user_rights | data_access_groups | data_export | reports | stats_and_charts | manage_survey_participants | calendar | data_import_tool | data_comparison_tool | logging | file_repository | data_quality_create | data_quality_execute | api_export | api_import | mobile_app | mobile_app_download_data | record_create | record_rename | record_delete | lock_records_all_forms | lock_records | lock_records_customization | forms | | ---: | :----------- | :---------------------- | :-------- | :-------- | :--------- | :------------------ | ----------------------: | -----: | -----------: | -------------------: | -----------: | ------: | -----------------: | ---------------------------: | -------: | -----------------: | ---------------------: | ------: | ---------------: | --------------------: | ---------------------: | ----------: | ----------: | ----------: | --------------------------: | -------------: | -------------: | -------------: | ------------------------: | ------------: | ---------------------------: | :------------------------------ | | 1 | a_barker | a_barker@email.com | Aleesha | Barker | NA | hospital_a | 4117 | 0 | 0 | 0 | 2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | example_ | | 1 | a_hanna | a_hanna@email.com | Aleesha | Hanna | NA | hospital_d | 4120 | 0 | 0 | 0 | 2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | example_ | | 1 | a_hicks | a_hicks@email.com | Alyssa | Hicks | NA | hospital_e | 4121 | 0 | 0 | 0 | 2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | example_ | | 1 | a_lees | a_lees@email.com | Aleesha | Lees | NA | hospital_h | 4124 | 0 | 0 | 0 | 2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | example_ | | 1 | a_nicholson | a_nicholson@email.com | Alyssa | Nicholson | NA | hospital_i | 4125 | 0 | 0 | 0 | 2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | example_ | | 1 | c_avila | c_avila@email.com | Chanice | Avila | NA | NA | NA | 0 | 0 | 0 | 2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | example_ | | 1 | c_gould | c_gould@email.com | Chanice | Gould | NA | hospital_b | 4118 | 0 | 0 | 0 | 2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | example_ | | 1 | c_kent | c_kent@email.com | Chanice | Kent | NA | hospital_f | 4122 | 0 | 0 | 0 | 2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | example_ | | 1 | c_michael | c_michael@email.com | Chanice | Michael | NA | hospital_h | 4124 | 0 | 0 | 0 | 2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | example_ | | 1 | f_almond | f_almond@email.com | Fleur | Almond | NA | NA | NA | 0 | 0 | 0 | 2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | example_ |
However, these do not produce the correct data format as the data
(email) must be summarised by group (data_access_group). This can be
done directly via the user_summarise()
function. This produces data in
the exact format required by the subsequent “email_” functions
(alongside some additional summarised data which may be of interest).
This is the recommended option for handing REDCap user data for this
purpose.
df_user <- collaborator::user_summarise(redcap_project_uri = Sys.getenv("collaborator_test_uri"),
redcap_project_token = Sys.getenv("collaborator_test_token"),
user_exclude = "y_o’doherty")
knitr::kable(df_user) # Please note all names have been randomly generated
| data_access_group | user_n | user_usernames | user_fullnames | user_email | | :------------------ | ------: | :------------------------------------------- | :---------------------------------------------------------- | :--------------------------------------------------------------------------------------- | | hospital_a | 3 | a_barker; f_galindo; l_cervantes | Aleesha Barker; Fleur Galindo; Leroy Cervantes | a_barker@email.com; f_galindo@email.com; l_cervantes@email.com | | hospital_b | 3 | c_gould; k_gibbons; s_beech | Chanice Gould; Kester Gibbons; Shanelle Beech | c_gould@email.com; k_gibbons@email.com; s_beech@email.com | | hospital_c | 1 | r_bradford | Ralph Bradford | r_bradford@email.com | | hospital_d | 3 | a_hanna; h_herman; y_cameron | Aleesha Hanna; Hailie Herman; Yara Cameron | a_hanna@email.com; h_herman@email.com; y_cameron@email.com | | hospital_e | 4 | a_hicks; l_jensen; s_hardy; y_holder | Alyssa Hicks; Leroy Jensen; Shanelle Hardy; Yara Holder | a_hicks@email.com; l_jensen@email.com; s_hardy@email.com; y_holder@email.com | | hospital_f | 2 | c_kent; s_knights | Chanice Kent; Samiha Knights | c_kent@email.com; s_knights@email.com | | hospital_g | 2 | f_livingston; y_mackie | Fleur Livingston; Yaseen Mackie | f_livingston@email.com; y_mackie@email.com | | hospital_h | 4 | a_lees; c_michael; k_marks; s_moses | Aleesha Lees; Chanice Michael; Kester Marks; Shanelle Moses | a_lees@email.com; c_michael@email.com; k_marks@email.com; s_moses@email.com | | hospital_i | 4 | a_nicholson; h_mustafa; r_hodge; r_ochoa | Alyssa Nicholson; Hailie Mustafa; Ralph Hodge; Ralph Ochoa | a_nicholson@email.com; h_mustafa@email.com; r_hodge@email.com; r_ochoa@email.com | | hospital_j | 3 | l_paine; m_owens; y_odoherty | Leroy Paine; Martha Owens; Yara O’Doherty | l_paine@email.com; m_owens@email.com; y_odoherty@email.com |
While these email functions were developed for the intent of integration with REDCap, the subsequent “email_” functions are build to not require REDCap to work. However, the same minimum input format is required:
One unique group per row (listed within a single column).
A string of group-specific email addresses, separated by a semicolon (listed within a single column).
For example (using the data above to illustrate):
df_user_other <- df_user %>%
dplyr::select("group" = data_access_group, "group_specific_emails_recipients" = user_email)
knitr::kable(df_user_other)
| group | group_specific_emails_recipients | | :---------- | :--------------------------------------------------------------------------------------- | | hospital_a | a_barker@email.com; f_galindo@email.com; l_cervantes@email.com | | hospital_b | c_gould@email.com; k_gibbons@email.com; s_beech@email.com | | hospital_c | r_bradford@email.com | | hospital_d | a_hanna@email.com; h_herman@email.com; y_cameron@email.com | | hospital_e | a_hicks@email.com; l_jensen@email.com; s_hardy@email.com; y_holder@email.com | | hospital_f | c_kent@email.com; s_knights@email.com | | hospital_g | f_livingston@email.com; y_mackie@email.com | | hospital_h | a_lees@email.com; c_michael@email.com; k_marks@email.com; s_moses@email.com | | hospital_i | a_nicholson@email.com; h_mustafa@email.com; r_hodge@email.com; r_ochoa@email.com | | hospital_j | l_paine@email.com; m_owens@email.com; y_odoherty@email.com |
There may be any number of additional columns present that can be used to “mail-merge” within the email subject or body.
At this stage, we have a dataframe of the grouped recipents of the emails. Now we can begin to build the group-specific components of the emails.
The email_field()
function wrangles the dataframe of grouped recipents
(e.g. df_user
above) into the format required by email_send()
. There
are two aspects of email fields that can be customised:
Recipients: We define who will recieve the email. Different columns
of emails can be specified as the main recipents (recipient_main
),
cc’d (recipient_cc
), or bcc’d (recipient_bcc
). For example, you
may want to specify the primary investigator for a site as the
recipient_main
and others involved at that site as recipient_cc
.
Subject: We define what the name of the email will be. This can be
the same for all emails, or the subject can be made group-specific
by incorporting column names within the string. These names must be
within square brackets e.g. "[colname]"
.
df_email <- collaborator::email_field(df_email = df_user,
group = "data_access_group",
recipient_main = NULL,
recipient_cc = NULL,
recipient_bcc = "user_email", # we want all the recipients to be "BCC".
subject = "Hello to [user_n] collaborators at [data_access_group]")
knitr::kable(df_email)
| group | recipient_main | recipient_cc | recipient_bcc | subject | user_n | user_usernames | user_fullnames | | :---------- | :-------------- | :------------ | :--------------------------------------------------------------------------------------- | :-------------------------------------- | ------: | :------------------------------------------- | :---------------------------------------------------------- | | hospital_a | | | a_barker@email.com; f_galindo@email.com; l_cervantes@email.com | Hello to 3 collaborators at hospital_a | 3 | a_barker; f_galindo; l_cervantes | Aleesha Barker; Fleur Galindo; Leroy Cervantes | | hospital_b | | | c_gould@email.com; k_gibbons@email.com; s_beech@email.com | Hello to 3 collaborators at hospital_b | 3 | c_gould; k_gibbons; s_beech | Chanice Gould; Kester Gibbons; Shanelle Beech | | hospital_c | | | r_bradford@email.com | Hello to 1 collaborators at hospital_c | 1 | r_bradford | Ralph Bradford | | hospital_d | | | a_hanna@email.com; h_herman@email.com; y_cameron@email.com | Hello to 3 collaborators at hospital_d | 3 | a_hanna; h_herman; y_cameron | Aleesha Hanna; Hailie Herman; Yara Cameron | | hospital_e | | | a_hicks@email.com; l_jensen@email.com; s_hardy@email.com; y_holder@email.com | Hello to 4 collaborators at hospital_e | 4 | a_hicks; l_jensen; s_hardy; y_holder | Alyssa Hicks; Leroy Jensen; Shanelle Hardy; Yara Holder | | hospital_f | | | c_kent@email.com; s_knights@email.com | Hello to 2 collaborators at hospital_f | 2 | c_kent; s_knights | Chanice Kent; Samiha Knights | | hospital_g | | | f_livingston@email.com; y_mackie@email.com | Hello to 2 collaborators at hospital_g | 2 | f_livingston; y_mackie | Fleur Livingston; Yaseen Mackie | | hospital_h | | | a_lees@email.com; c_michael@email.com; k_marks@email.com; s_moses@email.com | Hello to 4 collaborators at hospital_h | 4 | a_lees; c_michael; k_marks; s_moses | Aleesha Lees; Chanice Michael; Kester Marks; Shanelle Moses | | hospital_i | | | a_nicholson@email.com; h_mustafa@email.com; r_hodge@email.com; r_ochoa@email.com | Hello to 4 collaborators at hospital_i | 4 | a_nicholson; h_mustafa; r_hodge; r_ochoa | Alyssa Nicholson; Hailie Mustafa; Ralph Hodge; Ralph Ochoa | | hospital_j | | | l_paine@email.com; m_owens@email.com; y_odoherty@email.com | Hello to 3 collaborators at hospital_j | 3 | l_paine; m_owens; y_odoherty | Leroy Paine; Martha Owens; Yara O’Doherty |
The email_body()
function will perform a mailmerge using a specified
rmarkdown file (e.g. “vignette_email_body.Rmd”) and the output from
email_field()
. This will form the email body to be sent via
email_send()
.
The rmarkdown file must have the YAML specified as “output: html_document” and fields to be mailmerged must be specified as “x$colname” (see “vignettes/vignette_email_body.Rmd”). The text format / spacing/ etc can be edited as usual for html rmarkdown documents.
The output from email_body()
is the original dataframe with up to
2 columns appended (based on the html_output
parameter):
“file” - The path for group-specific html file produced (can be
viewed to verify the rendered Rmd document displays as desired).
These will only be saved if html_output
includes “file”.
Note: All files will be placed in a subfolder specified by
subfolder
(default = “folder_html” within current working
directory), and will be named according to the group name (this
can be customised using file_prefix
and file_suffix
).
“code” - The html code for the mailmerged html document
produced. Note: at present, GmailR does not allow allow html
files to be directly attached as the email body. Therefore,
html_output should always include “code” and this should be
used as the email body in email_send()
.
df_email <- collaborator::email_body(df_email, group = "group",
html_output = "code",
rmd_file = here::here("vignettes/vignette_email_body.Rmd"))
tibble::as_tibble(df_email)
## # A tibble: 10 x 9
## group recipient_main recipient_cc recipient_bcc subject user_n user_usernames
## <chr> <chr> <chr> <chr> <chr> <int> <chr>
## 1 hosp… "" "" a_barker@ema… Hello … 3 a_barker; f_g…
## 2 hosp… "" "" c_gould@emai… Hello … 3 c_gould; k_gi…
## 3 hosp… "" "" r_bradford@e… Hello … 1 r_bradford
## 4 hosp… "" "" a_hanna@emai… Hello … 3 a_hanna; h_he…
## 5 hosp… "" "" a_hicks@emai… Hello … 4 a_hicks; l_je…
## 6 hosp… "" "" c_kent@email… Hello … 2 c_kent; s_kni…
## 7 hosp… "" "" f_livingston… Hello … 2 f_livingston;…
## 8 hosp… "" "" a_lees@email… Hello … 4 a_lees; c_mic…
## 9 hosp… "" "" a_nicholson@… Hello … 4 a_nicholson; …
## 10 hosp… "" "" l_paine@emai… Hello … 3 l_paine; m_ow…
## # … with 2 more variables: user_fullnames <chr>, code <chr>
The group2csv()
function will split a tibble/dataframe by “group”
variable, then save grouped data in a subfolder as individual CSV files.
This can be used as a group-specific attachment to be sent via
email_send()
.
The groups in the tibble/dataframe supplied (data
) must
exactly match the groups within the email_field()
function
output.
The output from email_body()
is a tibble with the “group”:
subfolder
(default =
“folder_csv” within current working directory), and will be
named according to the group name (this can be customised using
file_prefix
and file_suffix
).# Generate patient-level / anonomysed missing data report
report <- collaborator::report_miss(redcap_project_uri = Sys.getenv("collaborator_test_uri"),
redcap_project_token = Sys.getenv("collaborator_test_token"))$record
attach <- collaborator::group2csv(data = report,
group = "redcap_data_access_group",
subfolder = here::here("vignettes/folder_csv"), file_prefix = "missing_data_")
knitr::kable(attach)
| group | file | | :---------- | :------------------------------------------------------------------------------ | | hospital_a | /home/kmclean/collaborator/vignettes/folder_csv/missing_data_hospital_a.csv | | hospital_b | /home/kmclean/collaborator/vignettes/folder_csv/missing_data_hospital_b.csv | | hospital_c | /home/kmclean/collaborator/vignettes/folder_csv/missing_data_hospital_c.csv | | hospital_d | /home/kmclean/collaborator/vignettes/folder_csv/missing_data_hospital_d.csv | | hospital_e | /home/kmclean/collaborator/vignettes/folder_csv/missing_data_hospital_e.csv | | hospital_f | /home/kmclean/collaborator/vignettes/folder_csv/missing_data_hospital_f.csv | | hospital_g | /home/kmclean/collaborator/vignettes/folder_csv/missing_data_hospital_g.csv | | hospital_h | /home/kmclean/collaborator/vignettes/folder_csv/missing_data_hospital_h.csv |
The output from group2csv()
and any other attachments must be appended
to the output from email_field()
as additional columns.
df_email <- df_email %>%
dplyr::left_join(attach, group=c("redcap_data_access_group" = "group")) %>%
dplyr::mutate(file2 = here::here("man/figures/collaborator_logo.png"))
Note: Only group2csv()
will be supported as a function for creating
group-specific files en mass (e.g. rather than pdf, word, etc) as these
are too heterogeneous and specific in their purpose. However, once
created via rmarkdown, their pathways can be joined to the correct group
and will be attached if included in the email_send()
function.
We now have created our four components to the group-specific email:
Group (Essential): Column created via email_field()
(“group”).
Email fields (Essential): Columns created via email_field()
(“recipient_main”, “recipient_cc”, “recipient_bcc”, “subject”)
.
Email body (Essential): Column created via email_body()
(“code”).
Attachments (Optional): Column created via group2csv()
(e.g.
“file”) or added manually (e.g. “file2”).
The email_send()
function is currently built for use with gmail via
the gmailr package. To function, a
connection to the Gmail API must be established in advance (find step by
step process here).
gmailr::gm_auth_configure(path = "gmail.json")
The email_send()
will allow automated sending of the prepared
group-specific emails and their attachments. The following parameters
can be specified:
sender
: The email account from which the emails will be sent (must
be a gmail account and match the )
body
: The column containing the html code produced by the
email_body()
function for each group.
attach
: An (optional) list of columns containing the paths of all
files to be attached (including those which are group-specific). If
zip = TRUE (default = FALSE), then these files will be compressed
into a zip folder.
draft
: To prevent premature sending of emails, the default setting
is to send emails to the gmail draft folder (draft = FALSE). When
ready to send emails automatically this should be changed to draft =
TRUE. *Note: gmail allows a maximum of 500 emails to be sent per day
collaborator::email_send(df_email = df_email,
sender = "email@gmail.com",
email_body = "code",
attach = c("file", "file2"), zip = T,
draft = FALSE)
The email_send()
function will print the number + group of emails as
they are sent. This facilitates troubleshooting in the event of errors.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.