Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2008
    Posts
    2

    Unanswered: Import from email to generate job?

    Is it possible with Access (or any other database) to set up an email address to import job requests to a database and automatically generate a job number? I am interested in putting together a facilities maintenance database/management program that allows customers of our service to email their problem ie, "door sticks" / "light has gone out" / "no power to fax machine" etc to a specific email address, for example "repairs@maintenance.com" and for the program to automatically generate a job number, return the job number by email and to register the issue/job on the system.

    Is this possible and what programs do I need, can anyone point me in the right direction?

    Thanks for your help

    Regards

    Vinny

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Most of this can be done with outlook and access.

    Potential Problems:

    Parsing a free-form email sent by customers is, in my opinion, pretty much impossible. I insist on a webform that customers fill out on-line and submit. This allows me to control the subject and body structure.

    Timing could be an issue, but you may be able to get around it using a rule in outlook or a timer in access. I have the user click a button in access to grab and process email from outlook. I've never tried to completely automate this.

    Tips:
    If you use a web form, verify vital information (ie email address) upon submittal and user combo/list boxes wherever possible. The less free form text, the easier/ more flexible the processing.

    Move the emails after processing.

    Capture any emails that so not process correctly, move them to an errors folder and reply to sender that there was a problem with the request.

    If your email server has size limits for email boxes, make sure to move the sent items off the server after sending.

    Disable spell check and email address check in outlook for any automated sending.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'd be looking for an appropriate SMTP API for access to read in the e-mail, add the body to a table memo field and then send a standard e-mail back with that record's primary key as the job number.

    Doesn't sound that tricky as long as a human reads the memo field and works on the job :P
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jun 2008
    Posts
    2
    Quote Originally Posted by StarTrekker
    I'd be looking for an appropriate SMTP API for access to read in the e-mail, add the body to a table memo field and then send a standard e-mail back with that record's primary key as the job number.

    Doesn't sound that tricky as long as a human reads the memo field and works on the job :P

    Thanks for the reply. Is this something that is easy to do? Im not a programmer but I can (slowly) put together a database in access, I do not have a huge budget for the application so I am wondering if this is the domain of a specialist or something someone reasonbly pc literate could implement?

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't know what you know and what you don't so that's pretty hard to answer. You would need to be proficient enough in VBA to handle the job. If you haven't touched VBA before, then this might be a bit too much to bite off. Best answer is "give it a go" and see if you can do it, but if it seems like it's going to take too much time and you're "getting nowhere" then it's probably time to call in someone.

    ..

    You know, the more I think of this, the more I am inclined to look for a e-mail / server / web-based solution. Relying on an Access database to check for mail every minute and process each as suggested is not as efficient as handling it at the server, which should be able to respond INSTANTLY to a received e-mail. If the Access database hangs or someone accidentally shuts down...

    The e-mail server should be able to receive an e-mail, add some data to an Access database (for job processors to deal with) along with an ID number.

    Another way would be to accept the "e-mail" only from a web-page and that page could handle the whole deal.

    Lots of ways to do this, first job would be to decide which way you want to go!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I once made a form using outlook's form creator which linked to an Access backend which worked pretty slick. The form was emailed out and users entered data and sent it back which then populated the mdb.
    Last edited by pkstormy; 06-29-08 at 17:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sounds nice

    I have done almost no coding / development in Outlook. I tend to dodge things that get changed too often.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    ..

    You know, the more I think of this, the more I am inclined to look for a e-mail / server / web-based solution. Relying on an Access database to check for mail every minute and process each as suggested is not as efficient as handling it at the server, which should be able to respond INSTANTLY to a received e-mail. If the Access database hangs or someone accidentally shuts down...
    you should be able to do what you want with the outlook library/object model from within Access.

    as regards to timing. set up your access email reader app as a windows scheduled task at whatever interval you want..... admittedly you wont get the immediate response of say an email server porcess.. but its soemthng thats relatively trivial to set up, and doesn't interfere with the corporate emal server.

    I suppose ultimately it depend on what turnaround you want on the email coming in.

    I'd agree with earlier contributions in that the tighter you can control the email comng in the better.

    I did develop an app which read through emails to extract relevant details, but that was from an email generated form an e commerce app. it din't take too long to iterate through the relevant outlook folder and strip off any relevant emails.. Id certainly recommend that you move rahter than delete emails after receipt
    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
  •