Outlook VBA Automation

Hello Experts, help please !!

I'd like some help with some vba automation to be triggered from receiving an email. I receive a weekly email and in the body of the email is a link to a spreadsheet.

Ideally I'd like to automate the following:

# Where the email is from "John" and the subject is "Weekly Extract" then open the spreadsheet from the link within the email body
# Save as "Weekly data.xlsx" into our default directory "C:\Project Docs\"
# Then some how run a vba procedure or macro from an access database "Dashboard.mdb" in the "C:\Project Docs\" directory.

Is this even possible ? If so, any help and suggestions will be greatly appreciated
Who is Participating?
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.

Jeffrey CoachmanMIS LiasonCommented:
Note that you really have multiple questions here, not just one.

- vba automation to be triggered from receiving an email.
- # Where the email is from "John" and the subject is "Weekly Extract"
- save/open the attachment
- run some automation form Access ...?

So what you are asking for here is more of a "Complete project", than a simple question requiring a single straightforward "answer"

Please consider narrowing the focus of this question to "Detecting when an email is sent".

lancegallagher_expertsexchangeAuthor Commented:
OK, thanks boag2000, point taken.

I guess the main thing that I need to know is when I receive an email how can I trigger an event in Access.

Thanks, Lance
Jeffrey CoachmanMIS LiasonCommented:
It will be hard to trigger Access when emails are sent (db must be open at all times, ...etc)

So,  Typically, you would link the Inbox into Access, then get the date of the most recent email.
Then set that as your "Reference" day/time

Then store this value.
Then whenever you need the recent emails, you can just filter out the emails that came in after the reference date.

Follow these steps:
Create a table called: tblDates
RefDateTimeID (Primary Key, autonumber)
RefDateTime (DateTime, formatted for General Date)
(Set he first record in this table to something like 1/1/2013 12:01AM)

Create a link to the Inbox
External Data-->Import&Link-->More--Outlook-->Link to datasource...
Leave the name as :  Inbox
Create a data sheet view form from this table, ...name it: frmInbox

Create a form, name it something like frmGetEmails
But a button on this form.
Put code like this on the click event of this button:

    'Open the form with all emails since the reference date
    DoCmd.OpenForm "frmInbox", acFormDS, , "Received>" & "#" & DMax("refDateTime", "tbldates") & "#"
    'Set the new ref date in the table, from the newest email listed from the form.
    CurrentDb.Execute "INSERT INTO tblDates (RefDateTime) VALUES(" & "#" & DMax("Received", "Inbox") & "#" & ")", dbFailOnError

This will list all the most recent emails, ...each time you click the button.

But just a note, ...all that you are trying to do with this system (beyond what I have posted here,) ...is extremely complicated and wrought with pitfalls (both known and unknown)


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
lancegallagher_expertsexchangeAuthor Commented:
Excellent, thanks for the help !!
Jeffrey CoachmanMIS LiasonCommented:
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 Applications

From novice to tech pro — start learning today.