Attachments in Outlook

[LadyDi]

Is there a way to program Access to look in a specific folder in Outlook, find the message with attachments, open those attachments, and pull the updated information from the spreadsheet to the database?

I know this is asking a lot, but I figured if anyone knew how to accomplish this, they would be on this forum.

I'm setting up a database that tracks technician bins and the parts in those bins. Once every year, the technician needs to take an inventory of his bin. When he does that, he sends a spreadsheet into the home office and someone here manually enters that data into a database. The database already houses the bin name and the parts in that bin. The only thing that needs updated is the count of each part.

Is this possible to accomplish?

 

[pbaldy]

I run a number of automated imports. I started with Bill's "Saving an Email Attachment" code here:

http://www.thatlldoit.com/Pages/howtosarticles.aspx

That looks for a particular subject in the inbox, but could be adapted to your needs.

 

[LadyDi]

Thank you very much for the code. I've added it as a module to my database. However, I am having a little trouble getting it to work. This is the call statement that I tried:

Call Outlook_FileAtchmt("FW: Cycle Count for Bin 143607489 ", "\\C:\MY DOCUMENTS\" & sSubjectLn & ".xls")

Have I done something wrong? I would really like it to save the attachment using the subject line (minus the FW). Perhaps, that is my problem. Am I not supposed to specify how I want the file named? How should the path be written? Should it include the colon or the preceding backslashes?

 

[LadyDi]

Okay, I got it to work, as long as I don't try to rename the file.

I would really like to rename the file though, do you have any suggestions?

 

[pbaldy]

I'm not sure that's a valid path for starters (I never use the \\ for a local drive, just C:\), plus the code expects the path without the file name. It uses the name of the attached file. You can change that, but that's how it's written now. Copy the path from Windows Explorer if you're not sure.

 

[pbaldy]

Adapt this line so you can put the full path including file name in the input:

outAttachment.SaveAsFile sSaveAsFilePath & sFile

 

[LadyDi]

Thank you for the advice. It works great .

 

[pbaldy]

Happy to help!

 

[LadyDi]

I have another question regarding this module. It is working great. I have just been asked if the database will flag the e-mail after it downloads the attachment. That way, the user knows for sure which attachments have been downloaded without having to look it up in the database.

Is it possible for the database to flag the e-mail or give it a category (i.e. mark it with a blue category) after it downloads the attachment?

 

[pbaldy]

Haven't tried that. I move them to a subfolder:

outItem.Move (outTargetFolder)

I assume you can change the category or something, but I liked having it moved so there was no doubt it had been handled.

 

[LadyDi]

Moving it will work for me too. Where should I add that line? Would it just go immediately after the line that reads: outAttachment.SaveAsFile sSaveAsFilePath & sFile ?

Also, how is the outTargetFolder declared? Is it another Object? How can you tell it which folder to save to? Can you say, for example outTargetFolder = CountRead?

 

[pbaldy]

The relevant lines (in my case a subfolder of the Inbox):

Dim outTargetFolder
Set outTargetFolder = outFolder.Folders.Item("FolderName")

 

[LadyDi]

I added the verbiage you suggested.

I declared the outTargetFolder as an Object. Then set it to -- Set outTargetFolder = outFolder.Folders.Item("Entered Cycle Counts"), and I added this line : outItem.Move (outTargetFolder) -- to the loop that saves the attachment.

When I try to run the code, I keep getting a "type mismatch" error. What have I done wrong?

 

[pbaldy]

You added more than I suggested, though I'm not sure it's the cause. I didn't declare the variable as Object. I didn't specify, which makes it a Variant. Try changing to that and see if it helps.

 

[LadyDi]

I made the change you mentioned, and it worked the first time through. However, the second time through and every time since then, it is again giving me the "type mismatch" error. Is there anything else I can try?

 

[pbaldy]

What is the full code?

 

[LadyDi]

Here is the full code that I have:

Option Compare Database
Option Explicit
 
 
Function Outlook_FileAtchmt(sSubjectLn As String, _
                            sSaveAsFilePath As String, sBinName As String) As Boolean
'Purpose  : Get attachment from email and save it as a file in the
'           folder specified by sSaveFilePath.
'
'It may be necessary to add a date/time suffix to the file to prevent
'overwriting if the name is always the same.
'DateTime : 4/23/2000 09:59
'Author   : Bill Mosca
'Reference: Uses late-binding to Outlook object library
    Dim objOLApp As Object          'Outlook.Application
    Dim outItem As Object
    Dim outFolder As Object         'Outlook.Folder
    Dim outNameSpace As Object      'Outlook.NameSpace
    Dim outAttachment As Object     'Outlook.Attachment
    Dim AttachmentCopy As Object    'Outlook.Attachment
    Dim sFile As String
    Dim outTargetFolder             'Outlook.Folder
 
    On Error GoTo err_PROC
 
    'make sure file path ends with backslash
    If Right(sSaveAsFilePath, 1) <> "\" Then
        sSaveAsFilePath = sSaveAsFilePath & "\"
    End If
 
    sSubjectLn = UCase(sSubjectLn)    'make it all CAPS
    Set objOLApp = CreateObject("Outlook.Application")
    Set outNameSpace = objOLApp.GetNamespace("MAPI")
 
'***************************************************************************************
'If logIn is to be required, uncomment this section and LogOff line at end of function
'after mailbox is established and change code to prompt for password.
'Otherwise delete section.
'
'    'log on to Outlook as user with access to ATS mailbox.
'    'Show Profile dialog;use new session
'    outNameSpace.Logon "<Profile Name goes here>", "<Password goes here>", True, True
'***************************************************************************************
 
    Set outFolder = outNameSpace.GetDefaultFolder(6)    'olFolderInbox=6
    Set outTargetFolder = outFolder.Folders.Item("Entered Cycle Counts")
 
    'Loop through all items in mailbox. Get email by subject.
    For Each outItem In outFolder.Items
        If Trim(UCase(outItem.Subject)) = sSubjectLn Then
            'Get all attachments and save them as files in designated folder
            For Each outAttachment In outItem.Attachments
                sFile = outAttachment.FileName
                'save attacjment as file to specified folder
                outAttachment.SaveAsFile sSaveAsFilePath & sBinName & sFile
                outItem.Move (outTargetFolder)
            Next
        End If
    Next
 
 
exit_PROC:
    On Error Resume Next
    'clear Outlook objects from memory
    Set outItem = Nothing
    Set outFolder = Nothing
    Set outNameSpace = Nothing
    Set objOLApp = Nothing
    Exit Function
 
err_PROC:
    MsgBox "Error " & Err.Description
    Resume Next
 
End Function