X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Getting Xlsx Attachments From An Email And Saving To A Folder, Access 2016    
post Jul 11 2018, 02:25 AM

Posts: 664
Joined: 7-April 08
From: Dubai


I am trying to get this code to compile but it kicks out on all the Dim statements and I have no idea why.

Any ideas?

'************************** ???- **************************
'*** Code by Martin Green ******** martin@fontstuff.com ***
'******* Office Tips Web Site - www.fontstuff.com *********

Public Sub SaveAttachmentsToFolder()
' This Outlook macro checks a named subfolder in the Outlook Inbox
' (here the "Sales Reports" folder) for messages with attached
' files of a specific type (here file with an "xlsx" extension)
' and saves them to disk. Saved files are timestamped. The user
' can choose to view the saved files in Windows Explorer.
' NOTE: make sure the specified subfolder and save folder exist
' before running the macro.
    On Error GoTo SaveAttachmentsToFolder_err
' Declare variables
    Dim ns As NameSpace
    Dim Inbox As MAPIFolder
    Dim SubFolder As MAPIFolder
    Dim Item As Object
    Dim Atmt As Attachment
    Dim FileName As String
    Dim i As Integer
    Dim varResponse As VbMsgBoxResult
    Set ns = GetNamespace("MAPI")
    Set Inbox = ns.GetDefaultFolder(olFolderInbox)
    Set SubFolder = Inbox.Folders("Test") ' Enter correct subfolder name.
    i = 0
' Check subfolder for messages and exit of none found
    If SubFolder.Items.Count = 0 Then
        MsgBox "There are no messages in the Test folder.", vbInformation, _
               "Nothing Found"
        Exit Sub
    End If
' Check each message for attachments
    For Each Item In SubFolder.Items
        For Each Atmt In Item.Attachments
' Check filename of each attachment and save if it has "xlsx" extension
            If Right(Atmt.FileName, 4) = "xlsx" Then
            ' This path must exist! Change folder name as necessary.
                FileName = "C:\EmailAttachments\" & _
                    Format(Item.CreationTime, "yyyymmdd_hhnnss_") & Atmt.FileName
                Atmt.SaveAsFile FileName
                i = i + 1
            End If
        Next Atmt
    Next Item
' Show summary message
    If i > 0 Then
        varResponse = MsgBox("I found " & i & " attached files." _
        & vbCrLf & "I have saved them into the C:\EmailAttachments folder." _
        & vbCrLf & vbCrLf & "Would you like to view the files now?" _
        , vbQuestion + vbYesNo, "Finished!")
' Open Windows Explorer to display saved files if user chooses
        If varResponse = vbYes Then
            Shell "Explorer.exe /e,C:\EmailAttachments", vbNormalFocus
        End If
        MsgBox "I didn't find any attached files in your mail.", vbInformation, "Finished!"
    End If
' Clear memory
    Set Atmt = Nothing
    Set Item = Nothing
    Set ns = Nothing
    Exit Sub
' Handle Errors
    MsgBox "An unexpected error has occurred." _
        & vbCrLf & "Please note and report the following information." _
        & vbCrLf & "Macro Name: GetAttachments" _
        & vbCrLf & "Error Number: " & Err.Number _
        & vbCrLf & "Error Description: " & Err.Description _
        , vbCritical, "Error!"
    Resume SaveAttachmentsToFolder_exit
End Sub
Go to the top of the page
post Jul 11 2018, 04:19 AM

UtterAccess VIP
Posts: 10,908
Joined: 6-December 03
From: Telegraph Hill

Hi Chris,

>> but it kicks out on all the Dim statements <<

If possible, when asking a question, please try and include the error number or description text!

My guess is that you do not have a reference set to the Outlook object library.

In the VBA editor, go to: Tools > References

Scroll down the list until you find Microsoft Outlook Object Library, and click the checkbox next to it.

Close the references dialog and re-compile.


Go to the top of the page
post Jul 11 2018, 04:38 AM

Posts: 664
Joined: 7-April 08
From: Dubai

Hi d

That certainly did help and apologies for not including the compile error message.

The compile now gets through the Dim statements but now kicks out on

                Atmt.SaveAsFile FileName

Saying that "Method or data member not found"

Any ideas?


I have actually managed to get the module to work in Outlook but the plan is to get it to work from Access.

Go to the top of the page
post Jul 12 2018, 04:22 PM

UtterAccess VIP
Posts: 10,908
Joined: 6-December 03
From: Telegraph Hill

Hmmm... curious - I can't see anything ostensibly wrong with the code.

However, I do not have Outlook installed to test with, so we'll have to hope others with pitch in with their thoughts.

One thing, ensure you have Option Explicit declared at the top of every code module and then try compiling again. It may bring up some different errors which are causing this one to appear.


Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    23rd February 2019 - 04:08 AM