Attachments from Outlook automatically saved for use in Access database (1 Viewer)

Hi All,
I receive several reports daily which I want to automatically rename and save into a folder where Access will read from when I open my access file. This way it automatically gives me the latest information from those files.
Example: I receive a shipping, booking, opportunity, and backlog report all in separate emails in an attachment in excel format.
Each with a unique extension like booking12282018.xls but the beginning is always the same i.e. booking. I have a rule in Outlook that files them in a folder within outlook but I am curious if there is a way to add a file to the rule so that it automatically renames and saves to my c:\\accessfiles folder. I would like it named booking.xls when it is saved.
I realize this is an Access forum and I can't be the only one receiving their data this way. I have found code to download all attachments in Outlook but that is not helpful. I need to rename and save upon receipt.
Thanks for your help.
Razzbarry

I think you have to use VBA code in Outlook or Access to download the files from Outlook folder to your system folder.

Hi June7,
I believe you are correct. I was hoping somebody had already invented the wheel or might have some links to help me.
Have a great day.
Razzbarry

Might find this discussion interesting https://access-programmers.co.uk/forums/showthread.php?t=302556

You can loop though all folders in outlook and filter the mailbox for the email address you want then you should be able to loop though any Attachments for a given item of mail.


I used to do this as had a job a few years ago for a client who wanted to keep his outlook and access clients linked. I just checked the code in that project I never used to interact with attacments except when sending new messages from access I no longer use outlook so can't help futher sorry

Maybe I am looking at this the wrong way. Since there is code out there that increments the file name in some manner then saves it on the hard drive upon receipt. Is there a way to have access look for that file despite the increment like backlog*.xlxs? Right now I have the file backlog.xlsx linked to access and I save the backlogxyz123.xlsx manually as Backlog.xlsx. Any thoughts on that approach?
Thanks in advance.

So you don't really need to download Outlook attachment, you already have the file saved? Always the same folder location? You copy the new file and write over the Backlog.xlsx file? Do you delete the original new file?

the other way you can solve this is add another table on your db and save all the backlog*.xlsx to that table.you will need a code to check if the latest backlog?.xlsx is already in the table. if it is not, add this to the new table above using insert query then modify the connection string of the linked table (backlog) to point to the new .xlsx that you previously saved.

Hi June7,
No, I do need to download it as I haven't already saved the file. The file location is always the same. I do write over the old file. I do eventually delete the original file but I do that in Outlook. I could do that automatically to once I have reasonable confidence that everything is running smoothly.
Hi Arnelgp,
if I add all of the backlog files into another table I would have massive duplication issues as the backlog file is inclusive of everything. Shipping and booking reports are month to date as well. I could definitely do this but it doesn't solve my initial issue of automating the files being saved on my drive for access to see and be used.
Thanks,
Razzbarry

Should be able to adapt code in the referenced link to your situation. Restrict downloads to Backlog*.xlsx files and the line olAttach.SaveAsFile strSaveFldr & strFName would instead have static reference to the path\Backlog.xlsx file.

Hi June7,
I started to look at modifying the code in the link you provided and found myself unsure what code to use and what to delete or comment out. I did a search and changed my search words and found the following link: https://social.msdn.microsoft.com/F...rename-file-with-unknown-name?forum=accessdev

I made modifications to the path, type of file and name.
I tested it and it worked. SInce this is my first time I am pleased. Now I need to figure out how to modify for each of the file names.. backlog, shipping, booking, opportunities, etc. Does it all go in the same script and if so then I imagine I need to check it for the filename and then save it to the specific file name/location.
If not, how do you create a separate script for Outlook to run.
Thank you for your guidance. I am making progress and learning too but I won't be quitting my day job anytime soon to be a Access, outlook or VBA guru.
Merry Christmas,
Razzbarry

The code you found renames a file but does not download from Outlook.

Will have to expand the script to handle each file, possibly with Select Case structure. Only the 4 reports (shipping, booking, opportunity, and backlog) and always with those words in the names? Will they always be .xlsx file extension?

To download and save file, maybe:

Hi June7,
That is correct and it only renames it. Downloading it is a nice plus.
The file names are correct with the exception that there are two opps reports. One is SLX opportunity review and the other is SLX Open opportunities OSR

Where should this code reside? thisoutlooksession or module or class module?
Thanks,
Razzbarry

June7 said:

The code you found renames a file but does not download from Outlook.

Will have to expand the script to handle each file, possibly with Select Case structure. Only the 4 reports (shipping, booking, opportunity, and backlog) and always with those words in the names? Will they always be .xlsx file extension?

Click to expand...

I edited my post to add some code. It would go in a module in Access, maybe even within a button Click procedure.

Those are still Excel files? Modify the Select Case to accommodate.

Thank you, I will try tonight and report back

June7 said:

The code you found renames a file but does not download from Outlook.

Will have to expand the script to handle each file, possibly with Select Case structure. Only the 4 reports (shipping, booking, opportunity, and backlog) and always with those words in the names? Will they always be .xlsx file extension?

To download and save file, maybe:

Code:Copy to clipboard

Sub SaveExcelEmailAttachment()
Dim objOL As Outlook.Application
Dim objMsg As Outlook.MailItem 'Object
Dim objAttachments As Outlook.Attachments
Dim objSelection As Outlook.Selection
Dim objPA As PropertyAccessor
Dim i As Long
Dim lngCount As Long
Dim strFolderpath As String, strSourceFile As String, strDestFile As String
Const PR_ATTACHMENT_HIDDEN = "[URL]http://schemas.microsoft.com/mapi/proptag/0x7FFE000B[/URL]"
' Get the path to your My Documents folder
strFolderpath = CreateObject("WScript.Shell").SpecialFolders(16)
On Error Resume Next
' Instantiate an Outlook Application object.
Set objOL = CreateObject("Outlook.Application")
' Get the collection of selected objects.
Set objSelection = objOL.ActiveExplorer.Selection
' Set the Attachment folder.
strFolderpath = strFolderpath & "\Attachments"
' Check each selected item for attachments. If attachments exist,
' save them to the strFolderPath folder and strip them from the item.
For Each objMsg In objSelection
    ' This code only strips attachments from mail items.
    ' If objMsg.class=olMail Then
    ' Get the Attachments collection of the item.
    Set objAttachments = objMsg.Attachments
    lngCount = objAttachments.Count
    If lngCount > 0 Then
        ' We need to use a count down loop for removing items
        ' from a collection. Otherwise, the loop counter gets
        ' confused and only every other item is removed.
        For i = lngCount To 1 Step -1
            Set objPA = objAttachments.Item(i).PropertyAccessor
            If objPA.getProperty(PR_ATTACHMENT_HIDDEN) = False Then
                strSourceFile = objAttachments.Item(i).FileName
                strDestFile = ""
                If strSourceFile Like "*.xls*" Then
                    Select Case True
                        Case strSourceFile Like "Backlog*.xls*"
                            strDestName = "Backlog.xlsx"
                        Case strSourceFile Like "Shipping*.xls*"
                            strDestName = "Shipping.xlsx"
                        Case strSourceFile Like "Booking*.xls*"
                            strDestName = "Booking.xlsx"
                        Case strSourceFile Like "Opportunity*.xls*"
                            strDestName = "Opportunity.xlsx"
                    End Select
                    If strDestFile <> "" Then objAttachments.Item(i).SaveAsFile "C:\somepath" & strDestFile
                End If
            End If
        Next i
    End If
Next
ExitSub:
Set objAttachments = Nothing
Set objMsg = Nothing
Set objSelection = Nothing
Set objOL = Nothing
End Sub

Click to expand...

Hi June7,
Happy New Year!
I gave it a go and wasn't successful.
I updated the file names as you advised plus the path.
Do I also need to update strFolderpath = strFolderpath & "\Attachments" statement or is that ok? Did I miss anything else?
I set it up as a macro button and also as a test case so the output file name sticks out. I would like to run it as a script in a rule whenever I receive any of those files it can run automatically but I saw that isn't a good idea. Any thoughts on that?
Warmest wishes,
Razzbarry

What does "wasn't successful" mean - error message, wrong results, nothing happens?

strFolderpath should be fine.

No idea how to set up a 'rule'.

Hi June7,
Sorry... nothing happened.
I first tried to put it in the thisoutlooksession but nothing happened. I then moved the code to a module and setup a macro button with the same result.
Here is the code in case you see something I missed.

Thanks,
Razzbarry

-----------------------------------------------------
Sub SaveExcelEmailAttachment()
Dim objOL As Outlook.Application
Dim objMsg As Outlook.MailItem 'Object
Dim objAttachments As Outlook.Attachments
Dim objSelection As Outlook.Selection
Dim objPA As PropertyAccessor
Dim i As Long
Dim lngCount As Long
Dim strFolderpath As String, strSourceFile As String, strDestFile As String
Const PR_ATTACHMENT_HIDDEN = "http://schemas.microsoft.com/mapi/proptag/0x7FFE000B"
' Get the path to your access documents folder
strFolderpath = CreateObject("WScript.Shell").SpecialFolders(16)
On Error Resume Next
' Instantiate an Outlook Application object.
Set objOL = CreateObject("Outlook.Application")
' Get the collection of selected objects.
Set objSelection = objOL.ActiveExplorer.Selection
' Set the Attachment folder.
strFolderpath = strFolderpath & "\Attachments"
' Check each selected item for attachments. If attachments exist,
' save them to the strFolderPath folder and strip them from the item.
For Each objMsg In objSelection
' This code only strips attachments from mail items.
' If objMsg.class=olMail Then
' Get the Attachments collection of the item.
Set objAttachments = objMsg.Attachments
lngCount = objAttachments.Count
If lngCount > 0 Then
' We need to use a count down loop for removing items
' from a collection. Otherwise, the loop counter gets
' confused and only every other item is removed.
For i = lngCount To 1 Step -1
Set objPA = objAttachments.Item(i).PropertyAccessor
If objPA.GetProperty(PR_ATTACHMENT_HIDDEN) = False Then
strSourceFile = objAttachments.Item(i).FileName
strDestFile = ""
If strSourceFile Like "*.xls*" Then
Select Case True
'Case strSourceFile Like "Backlog*.xls*"
'strDestName = "Backlog.xlsx"
'Case strSourceFile Like "Shipping*.xls*"
'strDestName = "Shipping.xlsx"
Case strSourceFile Like "Booking*.xls*"
strDestName = "Booking_test.xls"
'Case strSourceFile Like "SLX Open Opportunity OSR*.xls*"
'strDestName = "Opportunity_OSR.xlsx"
'Case strSourceFile Like "SLX Opportunity review*.xls*"
'strDestName = "Opportunity_Review.xlsx"

End Select
If strDestFile <> "" Then objAttachments.Item(i).SaveAsFile "C:\Users\barryb\Documents\Access files\Files from Outlook" & strDestFile
End If
End If
Next i
End If
Next
ExitSub:
Set objAttachments = Nothing
Set objMsg = Nothing
Set objSelection = Nothing
Set objOL = Nothing
End Sub