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?
Jeffrey CoachmanConnect With a Mentor MIS 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)

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
lancegallagher_expertsexchangeAuthor Commented:
Excellent, thanks for the help !!
Jeffrey CoachmanMIS LiasonCommented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.