Sending and recieving email through access

[woknick]

Is there a way to send an email using outlook, have that email filtered into a specific folder, and then have an access program extract that email. The situation is that I have sales reps in the field that would like updates on projects that are stored in an access program I designed. The program already sends out auto notifications through outlook, but that is only when I update the specific project. All of the sales reps in the field use blackberry's and do not have time to call in to request an update. So I was wondering if it were possible to have a sales rep send a preformated email that has included the primary key ie Account number, and have access recieve that account number to turn around and send out an auto response.

thanks in advance.

 

[mtairhead]

I don't know if you can...With only Access.

I imagine, however, that if one had lone computer devoted to the constant checking-of-email, processing-of-requests and sending-of-email...This would be possible.

Outlook stores a user's mail in a file that can be opened with Access. (I think.) If this is true, it seems to me you could easily create a linked table to your inbox inside of your dbase. Just put your dbase in a loop, contantly opening and checking for "new records" (new mail) and set Outlook to check for mail every 3-5 minutes automatically.

The emailed requests would have to be simple... Like ONLY the ID number...Or "ID","ProductName" ... That sort of thing.

Obviously, you won't be able to do anything else with that computer. Consider it a server, and leave it alone.

Andrew

 

[The_Doc_Man]

Here is where you have a problem of sorts...

Outlook can indeed be opened by Access. There is the possibility of checking an Outlook library in the References list. Many folks have been able to read Outlook mail from Access.

BUT...

Your issue is that you want to do something with incoming mail. Here is where you run into a tech-check. Access, Outlook, Word, Excel, and just about any other window-oriented program you could name are oriented towards the Windows paradigm of programming, which is to say, they respond to EVENTS. A button click, a mouse move, a mouse-wheel move, a key entry, ... these and many other things trigger events. The Windows O/S "delivers" the events to the program, which must then decide what to do with them.

A "MAIL RECEIVED" event (not its proper name) is an OUTLOOK event. You cannot easily get Outlook to pass that event to Access. Even if you did, Access has no clue as to what a NewMail event means because it does not get one of those normally. It would see the code delivered from Windows O/S and say, in effect, "And what has this to do with me?"

You can program Outlook to respond to such an event because it has a VBA facility. But Access cannot get to the Outlook Event handler. That leaves you with designing a scheme to notify Access (which must be running) that it has mail to receive.

The final implication is that you have Access and Outlook sharing memory and somehow sharing data. But I don't know offhand of a way to make them share EVENTS. So that means your Access code has to do the only other thing available to it... loop.

You can include an invocation of the DoEvents routine to let other programs have a shot. You can create a startup form that self-minimizes and holds a timer that runs every X seconds (units of the timer are milliseconds). You can let Access run with lower priority than Outlook and then just let Access run flat-out forever. But in essence, you have no easy way to get Access to immediately notice new mail.

Now, the next question is, can you do this anyway? Sure. But you will spend many nights reading the manuals and wondering how you let yourself be talking into this project.

The next step is that even if you COULD do this (and I'm not saying you CAN'T), you would have to devise a scheme to identify the mail messages you wanted to see and somehow filter them so you can ignore personal mail to the same person/account. That will involve some text parsing of the subject line and perhaps analysis of the file body.

OK, one last little gotcha... if you happen to get a worm through e-mail and your automation OPENS it (even if only to reject it), you probably just launched a program from an unknown source to run on your machine with YOUR permissions and privileges. Do you really want to do that?

I don't want you to think this can't be done. What I want you to know is the magnitude of the task you have set for yourself.

 

[mtairhead]

I agree... It would be a lot of work.

I imagine though... You probably don't have to worry about sending events from Outlook to Access. If you devoted an entire computer to the task, couldn't you just schedule an Access App to open every minute or so? As long as you had Outlook checking for new email every... minute or so ( ) then there would be no need to tell Access anything, except "Open up and run..And then, please close."

You would need to have a special email account set up specifically for this function.

I agree that it is a lot of work... But so much fun to...

 

[jrjr]

I think you would be better off using the auto respond feature in Outlook. Set up a rule that looks for a specific subject such as an ID number for a rep. If that ID number is found in the incoming mail then Outlook sends a preformatted response to this person that contains the update.

You would either have to make the response yourself at the time you create the update or figure a way for Access to create it.

 

[mtairhead]

Good point... Wouldn't you need a different .eml file (Saved outlook message) for each project? And then, the .eml would have to be updated whenever the the progress on the project (Or, whatever you're tracking) changed.

That sounds a bit more sound. Perhaps a lot of work, though - If you have tens or hundreds of projects.

Andrew

 

[woknick]

Thanks for the quick response. I think ill rethink this project. I might just setup a secure website that can access my database so that the sales reps can goto the site on their blackberry and enter an account number into the field. Thank you to everyone for the help.