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

I have a database with tables linked to Outlook email folders.  As the emails come in, I need to either save and strip the attachments for import or import the attachments directly into Access and then strip them.  Is this possible?  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?
Jay WilliamsOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
Jay WilliamsOwnerAuthor Commented:
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.
0
Jay WilliamsOwnerAuthor Commented:
This question is not neglected, just temporarily eclipsed.  We can't always set our own priorities. :-)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jay WilliamsOwnerAuthor Commented:
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

Open in new window


Any advice would be greatly appreciated!
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Jay,

<< that involved taking a macro code from Outlook, dropping it in an Access Module and running it from there.>>

  That's not going to necessarily work because Access doesn't always have the same internal objects, methods, or properties as Outlook or other applications.

Some VBA code you find on the net might drop straight in, but typically not.  

<<Set myNameSpace = Application.GetNamespace("MAPI")>>

   And yes, Access does not have a GetNamespace method, nor does it internally have a MAPI object.

   In order to do this, you need to start Outlook as an Automation object from Access and control it.  That's the step you missed.   So in Access:

Dim objOL as Object
Dim myNameSpace as Object
Dim myItems as Object

Set objOL = CreateObject("Outlook.Application")

Now, objOL is an Outlook object and a instance of Outlook is running.   Now you can use the namespace method:

Set myNameSpace = objOL.GetNameSpace("MAPI")
Set myItems = myNameSpace.Folders("XE_IPP").Folders("Inbox").Items

 This code then even though it is in Access, is executing against an instance of Outlook.  In other words, anytime you refer to objOL, it must be using properties, methods, and objects within the Outlook object model.
Jim.
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, 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

Open in new window

0
Jay WilliamsOwnerAuthor Commented:
I've requested that this question be deleted for the following reason:

Another method was chosen.
0
Jay WilliamsOwnerAuthor Commented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Glad to be of help Jay.  Best of luck with your project.

Jim.
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.