Getting attachment from outlook using Access VBA

Question

I have a created folder in my outlook named "Reports". This folder contains emails with one attachment in each email. I would like to use ACCESS VBA to save the attachments from the "Reports" folder in Outlook to a local drive in my computer. here is the code I have so far, but gives me errors. Please help:

Sub GetAttachments()

Dim ns As NameSpace
Dim Inbox As Outlook.MAPIFolder
Dim folder As Outlook.MAPIFolder

Dim Item As Object
Dim Atmt As Attachment
Dim FileName As String
Dim i As Integer


Set ns = GetNamespace("MAPI")

Set Inbox = ns.Folders.Item("Reports") // I get an error in this line says an object could not be found 


i = 0

If Inbox.Items.Count = 0 Then
   MsgBox "There are no messages in the Inbox.", vbInformation, _
        "Nothing Found"
   Exit Sub
End If


For Each Item In Inbox.Items
   For Each Atmt In Item.Attachments
     FileName = "C:\Automation\" & Atmt.FileName

     Atmt.SaveAsFile FileName   // here is another error says method is not found
     i = i + 1
   Next Atmt
Next Item

Answer 1

Is your Reports folder within your Inbox folder? You may need to do something like this:

Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
Set RptFolder = Inbox.Folders("Reports") 

Your syntax for saving attachments looks correct (apart from your comments not being correct for VBA). You could print out the Filename that you are creating to see if it's a valid name. And I assume that you have created the Automation folder that you mention.

Update: Try declaring your Atmt as an Outlook.Attachment. There is such a thing as an Access.Attachment which does not have a SaveAsFile method, and it's probably picking that one up first. If you include the library name, you should get the one you need.

Update 2: To get to your Reports folder, one way is to get the Inbox folder as you are currently doing, then get its parent, then get the Reports folder under that.

Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
Set Mailbox = Inbox.Parent
Set RptFolder = Mailbox.Folders("Reports") 

Another way would be to scan the items under "ns" to find the one that starts with "Mailbox", then get the Reports folder under that. It seems a little more cumbersome than getting the parent of the inbox. That also seems cumbersome, but I couldn't find a way to get to the Mailbox folder directly.

Answer 2

Replace

           For Each Item In Inbox.Items
             For Each Atmt In Item.Attachments
               FileName = "C:\Automation\" & Atmt.FileName

               Atmt.SaveAsFile FileName   // here is another error says method is not found
               i = i + 1
            Next Atmt

With.....

          For Each Item In Inbox.Items
            For Each Atmt In Item.Attachments
              FileName = "C:\Automation\" & Atmt.FileName

              Attachments.SaveAsFile FileName   // here is another error says method is not found
              i = i + 1
           Next Atmt

Outlook does not have a problem with atmt in the reference however, MS Access does. This should fix your problem.

Davis Rogers

Answer 3

Replace

Dim Atmt As Attachment

with

Dim Atmt As Outlook.Attachment

It'll make Access find the correct Class for atmt.