word vba

[rcollins]

I have made a form on my website that emails me when submitted. I want to turn this into a readable format for HR. as now, I have:
date:1/17/2007
position:any
last:blow
first:joe
What is the best way for me to do this, xml, import to form? any help would be great. Thanks

[nico5038]

I assume you want to import this into a table.
Access will allow you to create a linked table to an outlook folder.
The problem hoever will be the fact that the data is in multiple rows.
Having a format:
Data,: 01-01-2007, Joe, Blow
Would allow to use the Split() function to get the pieces and drop that into a table.
Multiple rows complicate this.

Nic;o)

[NeoPa]

I have made a form on my website that emails me when submitted. I want to turn this into a readable format for HR. as now, I have:
date:1/17/2007
position:any
last:blow
first:joe
What is the best way for me to do this, xml, import to form? any help would be great. Thanks

I can't see how Access or Word come into this.
Would this not be about the capabilities of the server side script engine (of which I sadly know v little)? VB Script or Java?
I may be on the wrong lines completely - I'm afraid the question itself isn't very clear. If I'm wrong then please ignore me, otherwise I would think that posting in the Java forum might help. If you're using VB Script then they may have some expertise in there for that too.
They may be able to tell you what is the simplest format to attach it to the e-mail with. It may be that it's MS Word I suppose - if libraries are available from there. I don't know.

[rcollins]

these fields actually come in on seperate lines. I never thought about bringing it into Access. I could build a simple database for HR to use so they can import and make a report to our liking. I will look into the Split function

[nico5038]

The split will fill an array, use:

dim arr
dim arr2

arr = split(messagebody,vbCRLF)
This will give an array containing all lines.
With:

for intI = 0 to Ubound(arr)
    if left(arr(intI),5) = "Data:" then
      arr2 =split(arr(intI),",")
      ' here process arr2(1) (being the first field when you have "Data:," as start
    endif
next intI

Nic;o)

[rcollins]

Is there a way I can import this info into a table that would be workable?

[nico5038]

Yes, just link a table to the Outlook folder. (Chose the link table option when opening a new table and navigate to the folder)
Show this table in a datasheet subform and add a button to process the current record like:

dim arrLine
dim intI as Integer
dim rs as dao.recordset
 
set rs = currentdb.openrecordset("your table")
arrLine = split(me.subformname.form.body,chr(13) & chr(10))
 
rs.addnew
for intI = 1 to ubound(arrLine)
    select case left(arrSplit(intI),INSTR(arrLine,":"))
     case "Date:"
      rs!DateIn =  mid(arrSplit(intI),INSTR(arrLine,":")+1)
     case "Name:"
      rs!DateIn =  mid(arrSplit(intI),INSTR(arrLine,":")+1)
    end select
next
rs.update

That's the drill for one row per message.

Nic;o)

[rcollins]

What about multiple row? All my stuff comes in on individual lines.

date:    01/22/2007
position:    Any
last:    Doe
first:    John
middle:    
former:    
street:    123 anywhere
city:    nowhere
state:CO

[rcollins]

So I have modified the code to fit , here is the error I get "Object doesn't support this property or method"
Table is Applications
form is Applications
Subform is Applications subform

Dim arrLine
 
Dim arrSplit
Dim intI As Integer
Dim rs As dao.Recordset
 
Set rs = CurrentDb.OpenRecordset("Applications")
 
arrLine = Split(Me.Applications_subform.Form.Application, Chr(13) & Chr(10))
rs.AddNew
For intI = 1 To UBound(arrLine)
    Select Case Left(arrSplit(intI), InStr(arrLine, ":"))
     Case "Date:"
      rs!DateIn = Mid(arrSplit(intI), InStr(arrLine, ":") + 1)
     Case "Name:"
      rs!DateIn = Mid(arrSplit(intI), InStr(arrLine, ":") + 1)
    End Select
Next
rs.Update

[nico5038]

Try:

Dim arrLine
Dim intI As Integer
Dim rs As dao.Recordset
 
Set rs = CurrentDb.OpenRecordset("Applications")
 
arrLine = Split(Me.Applications_subform.Form.Application, Chr(13) & Chr(10))
rs.AddNew
For intI = 1 To UBound(arrLine)
    Select Case Left(arrLine(intI), InStr(arrLine, ":"))
     Case "Date:"
      rs!DateIn = Mid(arrLine(intI), InStr(arrLine, ":") + 1)
     Case "Name:"
      rs!DateIn = Mid(arrLine(intI), InStr(arrLine, ":") + 1)
' add here the other fields you need to fill in the table
    End Select
Next
rs.Update
 

Would be better to use:
frmApplication
tblApplication
sfrmApplication
etc. to make the code easier to check.

Nic;o)

[rcollins]

I got them named right. Your code had no error thistime. Now, for the other part. In the table, there is a field 'Contents". I am assuming this is where all of the message would go if it was one line message. What about the multiple line?

[nico5038]

The Contents should hold all lines, thus the Split looking for the different lines.
Just check the linked outlook table by making the row heigher or create a form with large textboxes to see multiple lines appear.

Nic;o)