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

Posted on 2008-11-07
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
    LVL 18

    Expert Comment

    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


    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

    LVL 1

    Author Comment

    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?
    LVL 1

    Author Comment

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

    Expert Comment

    by:David Lee
    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.
    LVL 1

    Author Comment

    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
    You're welcome.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Sorry for my English and Italian screenshots..... Problem: Every Year the number of E-Mails grow dramatically and the space that they take on Exchange Servers or .PST files, grow much more faster, once it was only text, now our E-Mail Archives …
    Get an idea of what you should include in an email disclaimer with these Top 5 email disclaimer tips.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now