Extracting Zip+CSV file from attachment w/ Image in Body of Email

Question

I receive daily emails where there is an attachment containing 1 zip file containing 1 csv file.

In the body of my email, there is an image that is being recognized as another attachment I am pretty sure.

The below script works when there is only text in the body of the email but with the "Adobe Marketing Cloud" image, it is screwing up the script.

Is there a way I can only read maybe the first attachment read (assuming that will be the zip file)?

Here is my script:

library(readr)
library(RDCOMClient)
outlook_app <- COMCreate("Outlook.Application")
search <- outlook_app$AdvancedSearch(
  "Inbox",
  "urn:schemas:httpmail:subject = 'SUBJECTNAME'"
)

Sys.sleep(5) # Wait a hot sec!


results <- search$Results() # Saves search results into results object

Sys.sleep(5) # Wait a hot sec!

results$Item(1)$ReceivedTime() # Received time of first search result

as.Date("1899-12-30") + floor(results$Item(1)$ReceivedTime()) # Received 
date

# Iterates through results object to pull out all of the items
for (i in 1:results$Count()) {
  if (as.Date("1899-12-30") + floor(results$Item(i)$ReceivedTime()) 
      == as.Date(Sys.Date())) {
    email <- results$Item(i)
  }
}

attachment_file <- tempfile()
email$Attachments(1)$SaveAsFile(attachment_file)

##Automatically Determine csv file name
file_name<-unzip(attachment_file,list=TRUE)
csv_file<-file_name$Name

##Read CSV File
newly_read_data <- read_csv(unz(attachment_file,csv_file))

The error comes here:

file_name<-unzip(attachment_file,list=TRUE)
Error in unzip(attachment_file, list = TRUE) : 
  zip file 'C:\Temp\Rtmp86Gnzp\file29904a23387b' cannot be opened

Any help would be great, thanks!

Trews

@mdneuzerling any experience with this? – Trews

Answer 1

email$Attachments(1)$SaveAsFile(attachment_file) will save the first attachment to the file path defined by attachment_file, according to some unknown ordering. If you're sure there'll only be one file with a ".zip" extension then you can use the FileName method to check that the name of the attachment contains ".zip":

attachment_file <- tempfile()

for (i in 1:email$Attachments()$Count()) {
    attachment <- email$Attachments(i)
    if (grepl(".zip", attachment$FileName(), ignore.case = TRUE)) {
        attachment$SaveAsFile(attachment_file)
    }
}

Malonis

Hi @mdneuzerling, thanks for you help! There is an error coming up saying: Error in email.Attachments.Count() : could not find function "email.Attachments.Count" I tried the other way you mentioned also above and it through a syntax error. – Trews

Also, after I run above, should I still run #Automatically Determine csv file name section? – Trews

Darn. I'll look up the correct code tomorrow and amend my answer. Sorry about that! The property certainly exists, but the line between property and method is very blurry here. I'm afraid I don't know anything about the package you're using to unzip files, but yes, you would then go through to unzip the attachment and load the .csv in it. – Malonis

Ok sounds good, thanks for looking into this @mdneuzerling. I'll do some research also in the meantime on the link you provided. – Trews

@NickKnauer Try that now. There were a few errors, but the big one was using grep instead of grepl. – Malonis

That worked perfectly! Thanks for the help @mdneuzerling. Appreciate you spending time looking into this. – Trews