10

I receive an email every Sunday with an attachment (a zipped folder). The subject of the email never changes. I want to find the latest email with the specified subject line and download the attachment. I am new R user and so far I have only found a way to print the email body based on the subject (from one of the other questions asked on stackoverflow How to retrieve Outlook inbox emails using R RDCOMClient?). Ideally, I want to find the email with the specified subject received on a specified date and then download the attachment. Could some please point me in the right direction. Any help will be greatly appreciated. Thank you.

asmi
  • 449
  • 1
  • 6
  • 16

2 Answers2

18

You can search your inbox, or any other folder, using the AdvancedSearch method: 

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

This is an asynchronous method, so R won't wait for the search to complete before moving on to the next step. While there does exist an AdvancedSearchComplete event to handle this, I haven't been able to work out how to do this with RDCOMClient. As a workaround, a Sys.sleep(5) should give the search enough time to complete.

You can look through these results and query their received times with the ReceivedTime method. To convert these times to dates, use the Microsoft Office base date of December 30, 1899:

results <- search$Results()
results$Item(1)$ReceivedTime() # Received time of first search result
as.Date("1899-12-30") + floor(results$Item(1)$ReceivedTime()) # Received date

We can now look through the results for an email received on a particular date, say August 14, 2017.

for (i in 1:results$Count()) {
    if (as.Date("1899-12-30") + floor(results$Item(i)$ReceivedTime()) 
            == as.Date("2017-08-14")) {
        email <- results$Item(i)
    }
}

We can look through the attachments of an email similar to how we looked through search results. The first attachment will be email$Attachments(1) (watch out for pictures in email signatures; these will also show up!). If you're interested in a particular attachment, you can find it with the FileName method. Once you've found the attachment you want, you can save it to a file and begin to use it as if it were any other file.

attachment_file <- tempfile()
email$Attachments(1)$SaveAsFile(attachment_file)
data <- read.csv(attachment_file)

I've used a temporary file path here, but you could of course save the attachment to a permanent location.

mdneuzerling
  • 388
  • 3
  • 9
  • This worked absolutely perfectly for my need. Thank you so much! So cool – Matt W. Aug 15 '17 at 18:53
  • @mdneuzerling - Thank you. I tried it but the all I get is that search is an object of class COMIDispatch. I don't see the email. Could you please provide an insight to what I might be doing wrong. Thanks! – asmi Aug 18 '17 at 13:57
  • 1
    @asmi You can use the `Results()` method to extract results from a search, and `Item()` to extract an email from the results. For example, `search$Results()$Item(1)` will be the first (usually earliest) email in the search results. It will be a COMIDispatch object, so you have to interrogate it using methods, eg. `search$Results()$Item(1)$Subject()`. See [here](https://msdn.microsoft.com/en-us/vba/outlook-vba/articles/mailitem-object-outlook) for more details. Don't forget the brackets, also: `Subject()` will work but `Subject` won't. – mdneuzerling Aug 19 '17 at 22:45
  • Have you dealt with a situation like this where there is an image in the body of the email that is reading as an attachment as well? https://stackoverflow.com/questions/48695427/extracting-zipcsv-file-from-attachment-w-image-in-body-of-email – nak5120 Feb 09 '18 at 13:54
  • How would you apply the search to a subfolder of the Inbox, since the above code doesn't search subfolders by default? Obvious code like replacing `"Inbox"` with `"Inbox/MySubFolder"` doesn't work. – Mark Neal Jan 06 '20 at 23:14
  • 2
    To find a specific outlook subfolder, you can start by finding the right account using 'outlookNameSpace = outlook_app$GetNameSpace("MAPI"); folder <- outlookNameSpace$Folders(i); folder$Name(1)' and for i=1:n, then say i=2 is the right account, 'folder <- outlookNameSpace$Folders(2)$Folders(j); folder$Name(1)' for j=1:m for the right folder (say j=6), then 'folder <- outlookNameSpace$Folders(2)$Folders(6)$Folders(k); folder$Name(1)' for the right subfolder – Mark Neal Jan 07 '20 at 00:49
  • @MarkNeal I have established the Subfolder as an object but how can I read an attachment from using AdvancedSearch? – Matt Gossett Nov 25 '20 at 16:16
0

Having read the comments in the fantastic original solution by mdneuzerling, I wanted to add an additional answer with an example of how to extract data from a subfolder. This was something I only recently figured out and borrows a lot from the original solution.

Say your primary email was user@outlook.com and within that address you have a folder titled Inbox. Additionally, say you have a subfolder in your Inbox called Important. If you wanted to download an attachment from this subfolder from an email titled 'Email with Attachment', you would define the file path as follows:

path <- outlookNameSpace$Folders("user@outlook.com")$Folders("Inbox")$Folders("Important")$FolderPath()

You would then use the AdvancedSearch method as specified in mdneuzerling's answer through the following code:

   search <- outlook_app$AdvancedSearch(
             paste0("'", path, "'"),
             "urn:schemas:httpmail:subject = 'Email with Attachment'"
)

By following the rest of the method outlined in mdneuzerling's solution, you should be able to extract the attachment from the subfolder.

LouUniSP
  • 1
  • 2