Save and strip Outlook email attachments from Access

My database must detect new emails in a shared Outlook inbox, save the attachments to a specified folder, strip the attachments and move the email (by Subject) to specified Outlook subfolders.  I had been trying to do this with an Outlook macro, but Enterprise will not support rules that run script WithEvents.

In my research, I found the possibility that the Outlook script could be run pretty much "as is" from an Access module, but when I try, I get a compile error: "Method or data member not found." on .GetNameSpace.

In my Outlook script, I also start Access, and depending on the email Subject, run different procedures.  Of course now, Access will already be open and monitoring the Inbox, but I still need to run different procedures depending on the email Subject.  My questions are: (1) will this even work?; and (2) How do I correctly reference the Outlook folders and get things cleaned up there?  Here is the Outlook macro, now in an Access module.  How can I fix it?
Public Sub RunChecksRequests()

    Dim oMail As Outlook.MailItem
    Dim myNameSpace As Outlook.NameSpace
    Set myNameSpace = Application.GetNamespace("MAPI")

    On Error GoTo notfoundFolder
    Set myItems = myNameSpace.Folders("XE_IPP").Folders("Inbox").Items

    For Each oMail In myItems
        If TypeName(oMail) = "MailItem" Then
            If oMail.Subject = "IPP Share Request" And LCase(Right(oMail.Attachments.Item(1).FileName, 5)) = ".xlsm" Then
                   oMail.Attachments.Item(1).SaveAsFile "G:\XE_ECMs\IPP Sharing Development\Requests\" & oMail.Attachments.Item(1).FileName
                   Set rqFolder = myNameSpace.Folders("XE_IPP").Folders("Inbox").Folders("Requests")
                   oMail.Move rqFolder
                   GoTo RequestsTurnaround
            End If
            If oMail.Subject = "IPP Share Check" And LCase(Right(oMail.Attachments.Item(1).FileName, 5)) = ".xlsm" Then
                   oMail.Attachments.Item(1).SaveAsFile "G:\XE_ECMs\IPP Sharing Development\Checks\" & oMail.Attachments.Item(1).FileName
                   Set chkFolder = myNameSpace.Folders("XE_IPP").Folders("Inbox").Folders("Checks")
                   oMail.Move chkFolder
                   GoTo ChecksTurnaround
            End If
        End If
    Next
        
    On Error GoTo 0

    Exit Sub

notfoundFolder:
    MsgBox "Unable to process."
            
RequestsTurnaround:
    Set accApp = New Access.Application
    accApp.OpenCurrentDatabase ("G:\XE_ECMs\IPP Sharing Development\Processing.accdb")
    accApp.Run ("RequestsTurnaround")
    accApp.Quit
    Exit Sub
    
ChecksTurnaround:
    Set accApp = New Access.Application
    accApp.OpenCurrentDatabase ("G:\XE_ECMs\IPP Sharing Development\Processing.accdb")
    accApp.Run ("ChecksTurnaround")
    accApp.Quit
    Exit Sub
End Sub

Private Sub myItems_ItemAdd(ByVal Item As Object)
    
    Dim rqFolder As Outlook.MAPIFolder
    Dim chkFolder As Outlook.MAPIFolder
    If TypeName(Item) = "MailItem" Then
        
        If Item.Subject = "IPP Share Request" And LCase(Right(Item.Attachments.Item(1).FileName, 5)) = ".xlsm" Then
                Item.Attachments.Item(1).SaveAsFile "G:\XE_ECMs\IPP Sharing Development\Requests\" & Item.Attachments.Item(1).FileName
               On Error GoTo notfoundFolder
               Set rqFolder = Application.GetNamespace("MAPI").Folders("XE_IPP").Folders("Inbox").Folders("Requests")
               On Error GoTo 0
                
               Item.Move rqFolder
        End If
        If Item.Subject = "IPP Share Check" And LCase(Right(Item.Attachments.Item(1).FileName, 5)) = ".xlsm" Then
                Item.Attachments.Item(1).SaveAsFile "G:\XE_ECMs\IPP Sharing Development\Checks\" & Item.Attachments.Item(1).FileName
               On Error GoTo notfoundFolder
               Set chkFolder = Application.GetNamespace("MAPI").Folders("XE_IPP").Folders("Inbox").Folders("Checks")
               On Error GoTo 0
                
               Item.Move chkFolder
        End If

    End If

    Exit Sub

notfoundFolder:
    MsgBox "Unable to find folder"
End Sub

Open in new window

Surely would appreciate any insight and/or advice.  Thanks!
Jay WilliamsOwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
You need to call the .GetNameSpace using the Outlook application rather than the Access application.

e.g.
Dim myolApp As Outlook.Application
      Dim myNamespace As Outlook.NameSpace
      Set myolApp = CreateObject("Outlook.Application")
      Set myNamespace = myolApp.GetNamespace("MAPI")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jay WilliamsOwnerAuthor Commented:
Thanks.  Then does that mean that since Outlook is already running that I don't need to declare objOL AS Object? Like this?
    Dim myolApp As Outlook.Application
    Dim myItems As Object
    Dim oMail As Outlook.MailItem
    Dim myNamespace As Outlook.NameSpace
    
    Set myolApp = CreateObject("Outlook.Application")
    Set myNamespace = myolApp.GetNamespace("MAPI")
    Set myItems = myNamespace.Folders("XE_IPP").Folders("Inbox").Items
    Set myNamespace = myolApp.GetNamespace("MAPI")

Open in new window

Clearly, I don't get it.  But I did step through the loop With myolApp, and there were no errors.
0
SimonCommented:
You're mixing early binding and late binding, but that doesn't really matter in this instance.
Dim myolApp As Outlook.Application '=Early binding syntax
Dim myolApp As Object'=Late binding syntax

Whether Outlook is already running or not makes no difference to the code you need to use.

See this MSDN link for early v late binding
https://support.microsoft.com/en-us/kb/245115
0
Jay WilliamsOwnerAuthor Commented:
Simon gave me not only a solution, but a lesson in early and late binding.  Thanks for moving this project--and me--forward!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.