Strategy Needed - Import from Email

[MediaDoc]

Hi everyone,

Here is the setup.

People submit 'work requests' from the web, which gets sent to our admin person via an email. Currently they retype or copy - paste each field into the internal database. We would like to automate as much of this as possible (without putting the database on the web / .asp etc..)

One solution I can think of (whether this is viable or not, who knows - well hopefully you do!).

Format the email to contain text / field delimiters like
FirstName:"John"
LastName:"Public"
Title:"Director"
Organiztion:"City of Toronto"
etc..
etc..

The admin person would copy and paste the entire email into a form, then press a command button. Tied to that command button is some code that parses the form and pulls out the proper info, and inserts a record in the appropriate table.

Is this the 'best' way to go about something like this? (Other options I can think of is to have Access scan the email system for attachements with a particular name, and automagically parse them, but I have even less idea about how to accomplish that).

This must be a common function, Im just not sure what the best most reliable way to go about something like this is.

Any suggestions appreciated.

Regards,

Matts

 

[JonH]

What email software are you using?

Jon.

 

[MediaDoc]

Email is Netscape Navigator 6.x series email. Ugh!

Slim possibility I could get them to Outlook if I had a very convincing argument.

 

[JonH]

We use Outlook which has the ability to 'talk to' Access or vice versa. I have some code at the office which enables Access to import from your Inbox information from an eform. It does this by first identifying the eforms from either the name of the eform or by identifying some text in the Subject field. It then imports the information from the fields in the eform into Access. I'll get the code and Post it tonight probably at around 21:00 our time. I've no idea how eforms work with Netscape Navigator but wouldn't have thought Access could communicate with it.

Jon.

 

[The_Doc_Man]

At least on my version of Netscape I don't see where it has published anything that exposes the elements of the Component Object Model (COM) that would make this easy.

If you had access to the COM, you could open Netscape as an application, then find the attachment, then open the attachment in its appropriate application, then read and import the contents.

But without the exposure of COM to your other programs, Netscape will not support you. Now it IS possible that I have an older copy of Netscape, and that newer ones expose their COM elements. I would suggest that you look into Netscape's help files (NOT Access) to determine if it supports COM concepts. If it does not, then you are not going to get very far.

On the other hand, what you really wanted to do is not an unreasonable thing to ask for. I personally have a couple of apps here at my site that directly open Word documents to import data from them. That part isn't really THAT hard. Tedious, maybe, but not HARD.

 

[UniqueTII]

Here is the code to add your inbox:

Function AttachMail()
      Dim db As Database
      Dim td As TableDef
      On Error GoTo Errorhandler
         Set db = CurrentDb()
         Set td = db.CreateTableDef("tblInbox")

         ' Substitute your own MAPILEVEL, DATABASE and PROFILE arguments.
         td.Connect = "Outlook 9.0;MAPILEVEL=Mailbox - Wiedmann, Ed|;"
         td.Connect = td.Connect & "DATABASE=H:\sfina\groups\FINAID\"
         td.Connect = td.Connect & "Stud_Emp\Database\DBtoWEB\Job_Board_be.mdb;"
         td.Connect = td.Connect & "PROFILE=!eww"

         ' Substitute the name of the folder you want to attach.
         td.SourceTableName = "Inbox"

         db.TableDefs.Append td
         Application.RefreshDatabaseWindow
         MsgBox "Table Appended!"
      Exit Function
Errorhandler:
         MsgBox "Error " & Err & " " & Error
         Exit Function
      End Function

I just wrote queries with InStr to find the data fields, then got the data using mid() etc...