Watch Outlook Folder for e-mail then launch Access-DB using e-mail data, and reply.

What I would like to do is set up a 'watch' on a specific Outlook Mailbox.
If the subject contains, for example: "My Telephone Report: 555-555-5555".  Then it would launch an Access database, and run a report with the "555-555-5555" criteria, and e-mail the results back.

I'm assuming this would consist of watching the e-mails, and saving the variable to a file.  Then launching the Access-DB which would pick up the file/variable, link it into the query, and run the query.  The results would likely have to be always replied to the same person, or a folder where it can be picked up... ?

Any other ideas on how to accomplish this?
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.

The Access database would listen to the mailbox and read every incoming mail, and if the subject = your criteria the report could be ran and emailed.

That is exactly how I would handle it.
David LeeCommented:

You'll need something like the code below.  This code works with a rule.  Set the rule to fire when a message with "My Telephone Report" arrives.  Set the rule's action to "run a script" and set this script as the one to run.  You'll need to edit the filter portion of the script to work with your report.  
Sub TelephoneReport(Item As Outlook.MailItem)
    Dim accApp As Object, _
        accCmd As Object, _
        strNumber As String
    strNumber = Replace(Item.Subject, "My Telephone Report: ", "")
    If strNumber <> "" Then
        Set accApp = CreateObject("Access.Application")
        'Change the file name and path of the database on the following line'
        accApp.OpenCurrentDatabase "C:\eeTesting\MyDatabase.mdb"
        Set accCmd = accApp.DoCmd
        'Change the report name and filter as needed'
        accCmd.OpenReport "Phone List", , , "PhoneNumber = " & strNumber
    End If
    Set accApp = Nothing
    Set accRep = Nothing
End Sub

Open in new window

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
NO_CARRIERAuthor Commented:
That is so much more elegant than what I was working on for 6 hours... saving files to the hard drive, and setting up an access database with a timer to watch a folder for any new files, then to execute...

Is there a way to pass a variable to a query instead?  Also, how would I capture the name of the sender, so I can reply back to them with the results of the query?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

NO_CARRIERAuthor Commented:
Works awesome. Was able to capture the name of the sender using Item.SenderName
David LeeCommented:
Sorry to be slow.  Yes, that will get you the sender name.  You can also create the reply right there with something like this

    Set olkReply = Item.Reply

Yes, I'm sure a variable can be passed to a query, but I'm not deep enough into Access to be able to tell you how to do that.
NO_CARRIERAuthor Commented:
Ah, I wish I knew that before... I spent a few hours last night trying to get past the "Security", ClickYes Express only works when the computer is unlocked.  So I ended up using Redemption to send the reply.  Using With olkReply would've saved me a lot of headache.  Oh well, now I know for next time...

Thank you kindly BlueDevil...
David LeeCommented:
You're welcome.
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 Access

From novice to tech pro — start learning today.