Access VBA to save and strip attachment from linked outlook email table.

<<Is this possible?>>

 Don't see any reason why not, but you may find it easier if you buy one of the 3rd party libraries out there for working with Outlook (example:  http://www.everythingaccess.com/vbMAPI.asp )

<<  Should I use a form timer to run the procedure or is there an event that will fire it?  Can anyone point me in the right direction? >>

 Recurring jobs means either:

a. form timer

b. using task scheduler to kick off a DB, then closes when done.

 I typically prefer the later as it avoids issues with memory leaks (you have a "clean slate" each time the job runs).  

 Been years since I tried linking directly to Outlook folders and that may not be the best approach.  You may find you need to work through MAPI or the Outlook object model.

I am seriously short on time at the moment (it's 3:00 am and I'm working<g>) or I'd provide more detail, but you sounded like you needed some direction at least.  

Helen has quite a few Outlook Examples:

http://www.helenfeddema.com/Code%20Samples.htm

Look at:
"Working with Outlook folders"
and
"Copy Attachment.oft Extracting an Attachment from a Task and Mailing It"

 between those two, you should be pretty close to what you need.   Mail messages are just a different folder and extracting an attachment should be no different.

Not sure what your time frame is, but if your on this for more than a few days, or no one else responds, then I could try and get more specific sometime next week.

 No points please.

Jim.

Thanks, Jim.  I will be on this for a while, and I do have some time, but have to keep after it.  I'll surely take your advice and update this post if/when the light comes on.

This question is not neglected, just temporarily eclipsed.  We can't always set our own priorities. :-)

OK, I took your advice, Jim, and found a solution that involved taking a macro code from Outlook, dropping it in an Access Module and running it from there.  Right away I got a GetNameSpace error: "Method or data member not found."  The whole namespace and MAPI thing eludes me.  Sure, I'd like a solution, too, but I really want to understand this whole concept.  Where can I find a good tutorial?  Here's my code:

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

Select allOpen in new window

Any advice would be greatly appreciated!

Thanks, Jim!  Does this look better?  At least it compiles.  I see I didn't set the With block. Would that be with objOL or oMail?  Where would that go?  After rethinking, I probably shouldn't strip the attachment, so that's no longer an issue.

Option Compare Database

Public Sub RunChecksRequests()

    Dim objOL As Object
    Dim myItems As Object
    Dim myNameSpace As Object
    Dim oMail As Outlook.MailItem
    
    Set objOL = CreateObject("Outlook.Application")
    Set myItems = myNameSpace.Folders("XE_IPP").Folders("Inbox").Items
    Set myNameSpace = objOL.GetNamespace("MAPI")

    On Error GoTo notfoundFolder

    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:
    Call RequestsTurnaround
Exit Sub
    
ChecksTurnaround:
    Call ChecksTurnaround
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 = objOL.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 = objOL.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

Select allOpen in new window

I've requested that this question be deleted for the following reason:

Another method was chosen.

Thanks again, Jim.  Your solution worked, it moved this project--and me--forward.  It is a pleasure to associate with someone who is both professional and kind.

Glad to be of help Jay.  Best of luck with your project.

Jim.