Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    88

    Unanswered: New project calls for two-way email - pointers wanted!

    Hi everyone

    A new project is just starting to take shape ... you know, there are some bright ideas floating around but no-ones put anything in writing yet. Consequently I have no spec and no half-working code yet! But as this looks like it will go into completely new territory for me, I'm looking for some help as to what's possible so that the spec I end up with isn't impossible!

    Solution will be implemented in Access 2007.

    The easier part will probably be sending an email: based on available data, adjust a template and send out an email with attachments. I know this is an FAQ but would appreciate a pointer to a recommended solution.

    The harder and more interesting part is on the input side. Emails will be arriving in a mail box. Let's say the mail client is Outlook 2007 (but it could be something else if needed). Some of the emails arriving in this box will be automated outputs from another system over which I have no control, they contain (reasonably consistently formatted) information which I want to import into my database (name, address, email address, and some other stuff). Note that unrelated emails will also be arriving in the same box.

    A person will look at the incoming email, and decide that it needs to be processed. Ideally I'd like them to be able to press a button (Alternatively: a macro is triggered by the message Title) which passes the email to an Access 2007 program, where some VBA code will probably parse the email and stuff the data into the right places.

    I'm not too worried about writing the VBA code to do the parsing. I am very puzzled, though, about the best way to get the email content into Access.

    Is this a job for an Outlook plugin? Should I be approaching this with some VBA code in Outlook (never done that before but I guess it's much the same as Access VBA)?

    I've spent the last hour googling and am now goggle-eyed. Is there some experience out there to point me in the right direction?

    TIA

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how many people are going to receive the external emails
    is it one person or many?

    one option could be to get the recipients to forward the email onto holding account, and get access to strip it down off that holding account. ie if they see something that should be processed they send it on to be be processed. you could do the downloading + processing on a single machine, possibly a server using a batch job run under windows scheduler. or run the processing on your own PC, use say an outlook rule to shift incoming email on that account to a specific folder in Outlook.

    I'd import the emails into Access using Access VBA processing the emails, then as they are processed move them from incoming folder. alternatively you could look for a 'not read' flag. however because Im a cynic I prefer not to trust such things, so I'd process in Access, then move now processed email to another folder in outlook. I wouldn't delete, just in case the import got screwed up and you also have an audit log of which emails have been processed
    Why in Access: because that's how I've done this sort of thing in the past. I'm pretty certain you could do it from Outlook VBA but I don't know how.

    one issue you are going to have to face is how you identify what is the relevant data in the email. my process was form a confirmation email from an online shop so the format was known. if you don't know the incoming format then you need to work out some form of delimiter or scanning code that identifies what columns are where. thats where you problem lies, the rest is straighforward if time consuming
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Posts
    88
    @healdem: Thanks for the response.

    The incoming emails will all be directed at one email address and could be picked up at one computer. It might be possible to arrange for all the incoming mail to go to a unique mailbox which is used for no other purpose, which I guess would make things easier!

    If I go for the option of having Access VBA process the emails, then that takes me (I think) towards a fully automated route (not necessarily a bad thing!). I'm wondering if the code would have to start Outlook, receive mails, then scan the inbox to process each mail it sees there? I've not yet found any examples of Access VBA doing that, however - can you point me towards any?

    On the other hand, there is a worry in my mind that some human intervention might be necessary: it would be a very bad thing if an automated response were sent out that contained some silly error - so that it was obviously an automated response gone wrong. This makes me think that I'd rather have some human quality control, even if it does mean clicking a button per event (to say yes, ok , you can send that one).

    I guess that still leaves open the question as to whether to drive the whole thing from Outlook or from Access. My original concept, of selecting an item in an Outlook inbox and clicking a button, would be one way of doing it, though I still haven't worked out how to get that code to actually insert something in an Access DB. The other way, of getting Access to scan the inbox and do stuff, makes it easy to get data into the DB but means that Access VBA has to open up and process an Outlook inbox (as in the fully automated option mentioned above).

    More ideas very welcome.

    TIA

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    within Access VBA you can create an Outlook object, access virtually any property and email folders and so on. you can delete emails, you can send emails (however sometiems the email sending requires manual intervention. there may well be workarounds on that manual intervention using something like set warnings off but I don't know Ive never sent an email via Access.

    I don't think it matters if you drive it from Access or Outlook, my own experience has been from Access and I know its relatively straightforward just time consuming.

    The only thing Im not certain about is if you can tell outlook to get new emails from Access. the application I wrote was in the days of dial up lines so we didn't want to run up comms costs uneccesary and it wasn't that time critical. the process ran every 12 hours and scanned through the designated input folder and processed what was there

    being realistic you don't need a unique email address, as said before you could set up an outlook rule which moved the emails to a specific folder for processing.

    as regards the quality of data, I assume you mean the outgoing response. I'd create a flag or table which identified what was reasonable and approved to go out. you can use the same technique on incoming data, either read it into access stuff it into a holding tank there shadowing your live details. once you are happy the data is valid release the record from the holding tank into the live db. Im not a fan of entirely automated systems with data that may be iffy. creating external interface tables which require manual intervention to release either side of the firewall is a pragmatic approach assuming the volume of data wanting to cross the boundary is reasonable. if it gets too much then either checks wont get done or it will become such a time consuming task that its benefits are dubious despite the holy grail in the realtional db world of protecting the data's integrity
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •