Attaching one spreadsheet per email from R using RDCOMClient and for loop?

Question

I asked this question a couple of days ago here on this link. The solution partially worked but the issue is that the logic sent out a lot of emails with all the attachments instead of one attachment per email. Below is my code/logic. Ideadlly, I would like one email per attachment

#Create vector of all sheetname that are in my excel paths 
sheet_names <-  paste0(sheet_names_pre$Vendor_Name, ".xlsx")

#Sample of sheet_names
 [1] "60036819-SO PALMER DONOVAN.xlsx"       "60080204-SO BEACON SUPPLY.      .xlsx" "60009795-SO SRS DISTRIBUTION    .xlsx"
 [4] "60808049-SO GUARDIAN BLDG PROD.xlsx"   "60003551-SO OWENS CORNING SALES,.xlsx" "60292531-SO GAF BUILDING MATER..xlsx" 
 [7] "60004526-MERCHANTS METALS.xlsx"        "60360196-SO HENRY CO.xlsx"             "60660525-SO SIMPSON NO STRONG WL.xlsx"


#Directory name    
path_names <- "C:/Users/santi/Documents/"

#Concatenate directory and path 
attachments <-  c(paste0(path_names, sheet_names))

#Sample of attachments variable 
 [1] "C:/Users/santi/Documents/60036819-SO PALMER DONOVAN.xlsx"       "C:/Users/santi/Documents/60080204-SO BEACON SUPPLY.      .xlsx"
 [3] "C:/Users/santi/Documents/60009795-SO SRS DISTRIBUTION    .xlsx" "C:/Users/santi/Documents/60808049-SO GUARDIAN BLDG PROD.xlsx" 

#Logic to send out email
for(sheet in sheet_names) {
  #Concatenated and full path name       
  attachments = c(paste0(path_names, sheet_names))
  
  OutApp <- COMCreate("Outlook.Application")
  outMail = OutApp$CreateItem(0)
  outMail[["To"]] = paste("[email protected]"
                          , sep=";", collapse=NULL)
  outMail[["subject"]] = " Open Quotes"
  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()
  
}

The visualize the issue here is what happening, it is attaching all the emails instead of one email per spreadsheet :

Answer 1

Maybe you can consider using the following function :

send_email <- function(vec_to = "",
                       vec_cc = "",
                       vec_bcc = "",
                       char_subject = "",
                       char_body = "",
                       char_htmlbody = "",
                       vec_attachments = "") {

  Outlook <- RDCOMClient::COMCreate("Outlook.Application")
  Email <- Outlook$CreateItem(0)
  Email[["to"]] <- vec_to
  Email[["cc"]] <- vec_cc
  Email[["bcc"]] <- vec_bcc
  Email[["subject"]] <- char_subject

  if (char_body != "" && char_htmlbody != "") {
    stop("Error")
  }

  if (char_htmlbody == "") {
    Email[["body"]] <- char_body
  } else {
    Email[["htmlbody"]] <- char_htmlbody
  }

  if (vec_attachments[1] != "") {
    for (i in seq_along(vec_attachments)) {
      Email[["attachments"]]$Add(vec_attachments[i])
    }
  }
}

You can combine a loop with this function.