I have a form on my web page that allows a user to request information from me. When they fill out the form, I get an e-mail that I then want to import into my Access database. I am not sure how to do it.

Here is what I get from the web site:

-----Original Message-----
From: [email protected] [mailto:[email protected]]
Sent: Wednesday, September 03, 2003 9:25 AM
To: me@my_domain.com
Subject: Inquiry from Website


Name: John Doe
Title: DESIGN ENGINEER
Company: ABC CORP.
Address1: 2007 KEN PLACE
Address2:
City: MIDDLETOWN
ST: OHIO
Zip: 12345
Country: USA
Phone: 513-555-1234
Extension:
Fax: 513-555-1234
Email: [email protected]
Website:
Option_Editor: ON
Option_Virtualcnc: ON
CD: ON
30_60days: ON
Num_CNC: 8
SIC_Code:
Num_EE: 110
Describe_Business: ELECTRODE MANUFACTURER
PSI_Website: ON

__________________
Mitch

Hi Mitch,

I leave it to you to automate this from your mail client, but once you get your email into a text file, the following will parse it for you and write the new order to your base table. My text file (order.txt) looks like this:

Name: John Doe
Title: DESIGN ENGINEER
Company: ABC CORP.
Address: 2007 KEN PLACE
City: MIDDLETOWN
ST: OHIO
Zip: 12345
Country: USA

The code first converts the text file to one big 'ol string using basic I/O functions, then passes the string and the field name constants to a function which does the field value extractions.

I put this together based on some functions I ran across in a Smart Access article by Gary Robinson and Scott McManus ("Processing E-mail Orders Using Outlook and Access", May 2002). Their modules contain a bunch of code to automate the whole process using an instance of Outlook. I just sifted out the file I/O, data access bits.

Sub Main()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    ' convert text file to one big 'ol long string with
    ' a little basic I/O
    textstring = TextFileToString("c:\temp\order.txt")

    ' Debug.Print textstring

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblOrders", dbOpenDynaset)

    ' extract field values by passing big 'ol string and known
    ' constants to function, and add new record to base table
    rst.AddNew
    rst("Name") = ExtractFieldValue(textstring, "Name: ")
    rst("Title") = ExtractFieldValue(textstring, "Title: ")
    rst("Company") = ExtractFieldValue(textstring, "Company: ")
    rst("Address") = ExtractFieldValue(textstring, "Address: ")
    rst("City") = ExtractFieldValue(textstring, "City: ")
    rst("ST") = ExtractFieldValue(textstring, "ST: ")
    rst("Zip") = ExtractFieldValue(textstring, "Zip: ")
    rst("Country") = ExtractFieldValue(textstring, "Country: ")

    rst.Update

End Sub

Function TextFileToString(filename As String) As String

Dim linesfromfile, nextline As String
Dim file As Integer

  TextFileToString = ""

  file = FreeFile
  Open filename For Input As file

  Do While Not EOF(1)

    Line Input #1, nextline
    linesfromfile = linesfromfile + nextline _
      + Chr(13) + Chr(10)
  Loop

  Close file

  TextFileToString = linesfromfile

End Function

Function ExtractFieldValue(textstring As Variant, orderitem As String) As String

Dim startstring As Variant, endstring As Variant, fieldvalue As Variant


startstring = InStr(textstring, orderitem)
If startstring > 0 Then

  startstring = startstring + Len(orderitem)
  endstring = InStr(startstring, textstring, Chr(13))
  fieldvalue = Mid(textstring, startstring, endstring - startstring)

End If
If Len(fieldvalue) = 0 Then
  fieldvalue = " "
End If

ExtractFieldValue = fieldvalue

End Function

HTH,

Bob

Hi Mitch,

Actually that article is from the May 2003 issue of Smart Access.

Bob

Bob,

Cool code. One question...

    This makes the meat of that one e-mail into one long string embedded with CRLFs to separate out one field from the next. In Access VBA the datatype STRING can hold a very long string, but the datatype TEXT in a field in a table is limited to 255 characters. It's unlikely given the data Mitch presented that any one field will hold that much, but it's possible (esp. description of business). In general, how would you handle that possibility?


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks Bob, for the code, I will try it.

Here is a piece of code that I put together that should work, if I can get the last bug out of it. I get an error about the number of values not matching the fileds, but as many times as I have counted it matches.

Public Function GetWebFormData()
    Dim lFileHandle As Long
    Dim sFileName As String
    Dim sLine As String
    Dim sData() As String
    Dim sFieldName As String
    Dim sFieldData As String
    Dim sSQL As String

    Dim sAllFieldName As String
    Dim sAllFieldData As String


    lFileHandle = FreeFile()
    sFileName = "C:\web_form.txt"

    Open sFileName For Input As lFileHandle

    Do While Not EOF(lFileHandle)

        Line Input #lFileHandle, sLine

        sData = Split(sLine, ":")

        sFieldName = Trim$(sData(0))
        sFieldData = Trim$(sData(1))

        sAllFieldName = sAllFieldName & sFieldName & ", "
        sAllFieldData = sAllFieldData & "'" & sFieldData & "', "
     Loop

     'need to strip last comma and space from strings

     sAllFieldName = Left(sAllFieldName, Len(sAllFieldName) - 2)
     'MsgBox sAllFieldName

     sAllFieldData = Left(sAllFieldData, Len(sAllFieldData) - 2)

     sSQL = "INSERT INTO table1 ([" & sAllFieldName & "]) VALUES (" & sAllFieldData & ")"
        MsgBox sSQL
        CurrentDb.Execute sSQL, dbFailOnError

End Function

I found it! Each field needs to be enclosed in brackets [].

So, here is the code that works.

Public Function GetWebFormData()
    Dim lFileHandle As Long
    Dim sFileName As String
    Dim sLine As String
    Dim sData() As String
    Dim sSQL As String
    Dim sAllFieldName As String
    Dim sAllFieldData As String

    lFileHandle = FreeFile()
    '**Path and name of text file
    sFileName = "C:\web_form.txt"

    Open sFileName For Input As lFileHandle

    Do While Not EOF(lFileHandle)

        Line Input #lFileHandle, sLine

        sData = Split(sLine, ":") '**Look for a : in each line and split from there

        sFieldName = Trim$(sData(0))
        sFieldData = Trim$(sData(1))

        sAllFieldName = sAllFieldName & "[" & sFieldName & "], " '**Need [] around field name otherwise we get an error about number of values not matching the number of fields
        sAllFieldData = sAllFieldData & "'" & sFieldData & "', "
     Loop

     '**Need to strip last comma and space from strings
     sAllFieldName = Left(sAllFieldName, Len(sAllFieldName) - 2)
     sAllFieldData = Left(sAllFieldData, Len(sAllFieldData) - 2)

    sSQL = "INSERT INTO table1 (" & sAllFieldName & ") VALUES (" & sAllFieldData & ")"
        'MsgBox sSQL

    CurrentDb.Execute sSQL, dbFailOnError

End Function

Hi Greg,

The code can handle a text file line (and return as field value) of what, 2 billion characters is it. So I guess I'd ask my self the standard question when storing any large character string in a table: do I care if it gets truncated? If I do, the only option is to use a larger field data type, which in Access means 'memo'. If I don't the text data type will do.

Regards,

Bob

Nice Mitch! That's a keeper. Didn't know
Split() returned an array. That'll be useful.

Bob:)