Parse Email Body Text (1 Viewer)

[NickBridgens]

Here we go...

Email comes in to Outlook with the body as colon delimited text (it's from a web site form fill). I want to import the email (easy, linked file). I then want to strip the body text into the 135 colon delimited fields, put the field into another table set up for the purpose and then delete the email. A button or event on the form showing the linked record would do, but fully automatic would be great.

I've read the boards and in theory I can strip the colons out with split function and create an array but how do I do that and then get the contents into a new a record on another table?

Cheers

Nick Bridgens

 

[BukHix]

Why not put the contents from the form directly in an database and skip the email part? Then you could use the email just as a notification.

If you are on a NT server that supports ASP (most do) you have everything you need. Post back if you have more questions.

 

[NickBridgens]

The issue was to take structured text out of an email body and put it into Access in the right fields.
The mail body ended up as delimited with colons and carriage returns so I linked the email box in exchange into Access, created a form based on the email table, just showing the body field and then wrote this code to move it into Access and then delete the email (Exchange had already copied it to another mailbox for security)

Private Sub Import_Record_Click()
' Ensure tools / references / microsoft DAO 3.51 Object Library is enabled!
On Error GoTo Err_Delete_Click
If MsgBox("Are you sure you wish to import this record?", vbYesNo + vbQuestion) = vbYes Then
Dim SearchString, SearchChar1, SearchChar2, ColonPos1(200), ColonPos2(200), FieldData(200), Count%, DataString
' Strip off the header
DataString = Me.Body ' String to search in.
StringLen = Len(DataString) ' length of whole string
SearchChar = "currentWorth" ' Search for "currentWorth".
SearchPos = InStr(1, DataString, SearchChar) ' find the position of "currentworth"
SearchString = Right(DataString, StringLen - (SearchPos - 1)) ' Clean string
SearchChar1 = ":" ' Search for colon.
SearchChar2 = Chr(13) ' Search for carriage return.
ColonPos1(1) = InStr(1, SearchString, SearchChar1) ' find position of the first colon
ColonPos2(1) = InStr(1, SearchString, SearchChar2) ' find position of the first carriage return
DataString = Mid(SearchString, ColonPos1(1) + 1, ColonPos2(1) - ColonPos1(1) - 1) ' find text between the two points
FieldData(2) = LTrim(RTrim(DataString)) ' strip leading / trailing spaces and store in variable
' same again for the next 130 fields...
For Count% = 2 To 131
ColonPos1(Count%) = InStr(ColonPos2(Count% - 1) + 3, SearchString, SearchChar1) ' find position of the next colon
ColonPos2(Count%) = InStr(ColonPos2(Count% - 1) + 3, SearchString, SearchChar2) ' find position of the next carriage return
DataString = Mid(SearchString, ColonPos1(Count%) + 1, ColonPos2(Count%) - ColonPos1(Count%) - 1) ' find text between the two points
FieldData(Count% + 1) = LTrim(RTrim(DataString)) ' strip leading / trailing spaces and store in variable
Next Count%
' open a blank record and feed the variables into the fields
Dim emaildata As DAO.Recordset
Set emaildata = CurrentDb.OpenRecordset("Customer Details")
emaildata.AddNew
emaildata!currentWorth = Val(FieldData(2))
....etc
emaildata!ccj_description = FieldData(130)
emaildata!email_address = FieldData(131)
emaildata.Update
Set emaildata = Nothing
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 ' delete the current record
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If
Exit_Delete_Click:
Exit Sub
Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click
End Sub

It works fine but any critique welcome.

Nick Bridgens

And yes, it would have been easier if Access was on the web site...

 

[SteveA]

An approach that we have used in one of our databases as an interim solution was to set up a Custom Action in Outlook that calls our own VB program which:
* interrogates the e-mail
* updates the appropriate database
* sends a reply to the sender advising action was successful, and
* saves e-mail to a seperate folder in Outlook.

To call our program from Outlook, we installed a Custom Action for Outlook called launcher.dll available from the MSDN.Microsoft site (sorry, but I don't know the exact URL).

N.B. you do need access to regedit as there are manual entries that need adding to the registry to support Custom Actions in Outlook.

Once launcher.dll was installed, we added a rule via the Rules Wizard to Outlook that calls the Custom Action launcher.dll which inturn triggers our own VB program.

When the Rules Wizard in Outlook triggers the launcher.dll, our VBprogram is called and passed a reference to the incoming e-mail. You can then use this reference via the MAPI and interrogate and manipulate any part of the e-mail you like.

If you are interested in looking into this further, please let me know.

HTH
SteveA

[This message has been edited by SteveA (edited 01-25-2002).]