Forum Discussion
Yovasu
Dec 01, 2021Copper Contributor
Possibility to load excel lists automatically from my outlook to MS Access
I receive every day via Email two excel lists, which have in common the OBDs, date of shipment, name the carrier and postal code.
Is there a possibility to load these lists automatically from my outlook to MS Access? or is ther another possibility?
My goal is to display a single list throug a query with data from both lists, but I want to avoid the manual task of loading these lists daily.
Thank you 😊
- timsCopper ContributorThe Outlook object can be called from within Access via VBA code.
https://docs.microsoft.com/en-us/office/vba/outlook/concepts/getting-started/automating-outlook-from-a-visual-basic-application
For Reference: Outlook Attachment Object
https://docs.microsoft.com/en-us/office/vba/api/outlook.attachment
1. Loop through the Items collection
https://docs.microsoft.com/en-us/office/vba/api/outlook.items
2. Identify the desired email using some criteria (Subject, Sender, SentOn or FileName properties)
https://docs.microsoft.com/en-us/office/vba/api/outlook.mailitem.subject
https://docs.microsoft.com/en-us/office/vba/api/outlook.mailitem.sender
https://docs.microsoft.com/en-us/office/vba/api/outlook.mailitem.senton
https://docs.microsoft.com/en-us/office/vba/api/outlook.attachment.filename
3. Save the email attachment.
https://docs.microsoft.com/en-us/office/vba/api/outlook.attachment.saveasfile
4. Import the two saved files into an Access table using VBA or an Access import specification.
https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet
5. Create a query based on the import table.