Sending an Email from R Using For Loop? Using RDCOMClient

Question

I currently have 5 spreadsheets that I need to email out separately into 5 emails.

This is the logic I currently have for the email:

#Script to send out
attachments <- c('C:/Users/santi/Documents/Cost Changes xlsx/spreadsheet.xlsx') #spreadsheet variable
OutApp <- COMCreate("Outlook.Application")
outMail = OutApp$CreateItem(0)
outMail[["To"]] = paste("john_doe@outlook.com"
                        , sep=";", collapse=NULL)
outMail[["subject"]] = " Rebate"
outMail[["body"]] = "Hi - 

Attached is the spreadsheet

Let me know if you have any questions, thanks.

This is an automated message from RStudio please respond if you find any errors.

"
purrr::map(attachments, ~ outMail[["attachments"]]$Add(.))
outMail$Send()

How would I iterate over this to send out 5 separate emails while also attaching the relevant spreadsheet?

Answer 1

You haven't described your spreadsheet naming.

SheetNames <- c("sheet1.xls", "sheet2.xls", "sheet3.xls")
PathName <- "C:/Users/santi/Documents/Cost Changes xlsx/"

for (sheet in SheetNames) {

attachments = c(paste0(PathName, sheet))

OutApp <- COMCreate("Outlook.Application")
outMail = OutApp$CreateItem(0)
outMail[["To"]] = paste("john_doe@outlook.com"
                        , sep=";", collapse=NULL)
outMail[["subject"]] = " Rebate"
outMail[["body"]] = "Hi - 

Attached is the spreadsheet

Let me know if you have any questions, thanks.

This is an automated message from RStudio please respond if you find any errors.

"
purrr::map(attachments, ~ outMail[["attachments"]]$Add(.))
outMail$Send()

}