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

Posted on 2008-11-07
Medium Priority
Last Modified: 2013-11-27
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?
Question by:NO_CARRIER
  • 3
  • 3
LVL 18

Expert Comment

ID: 22911174
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.
LVL 76

Accepted Solution

David Lee earned 1000 total points
ID: 22914062

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


Author Comment

ID: 22925603
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?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 22932208
Works awesome. Was able to capture the name of the sender using Item.SenderName
LVL 76

Expert Comment

by:David Lee
ID: 22932916
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.

Author Comment

ID: 22942167
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...
LVL 76

Expert Comment

by:David Lee
ID: 22944728
You're welcome.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Take a look at these 6 Outlook Email management tools which can augment the working and performance of Microsoft Outlook to give you a more rewarding emailing experience.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question