Importing from Outlook and populating more than one field 1

[tractorvix]

Hi all,

Hope you can help. I have a form in Excel which, when completed sends a mail to a specified mailbox with data such as
Bob, Yes, No, Yes, No

I then need to import this data into an access table BUT put the information in seperate fields, such as
Name Employed Permanent Manager reports
Bob Yes No Yes No

I've got it importing all the information into one field using

Function ImportEmail()
'Import Email From The Inbox>Test SubFolder Into tbltest

Dim rst As Recordset
Dim db As Database
Dim strSearchString As String
Dim strSearchChar As String
Dim strChrPos As String
Dim strBaseMessage As String

'Set up Outlook Objects
Dim Outlook As New Outlook.Application
Dim OutlookNS As Outlook.NameSpace
Dim cf As Outlook.MAPIFolder
Dim MyFolder As Outlook.MAPIFolder
Dim MailItem As Outlook.MailItem
Dim objItems As Outlook.Items
Dim iNumContacts As Integer
Dim i As Integer

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbltest")

Set OutlookNS = Outlook.GetNamespace("MAPI")
Set cf = OutlookNS.GetDefaultFolder(olFolderInbox)
Set MyFolder = cf.Folders("test")

Set objItems = MyFolder.Items
iNumContacts = objItems.Count
If iNumContacts <> 0 Then
For i = 1 To iNumContacts
If TypeName(objItems(i)) = "MailItem" Then
Set MailItem = objItems(i)
rst.AddNew
rst!To = MailItem.To
rst!From = MailItem.SenderName
rst!Subject = MailItem.Subject

strBaseMessage = MailItem.Body

'Empty The strChrPos String
strChrPos = ""

'While There Are Line Breaks In The String Remove Them & Then Exit The Loop
Do While strChrPos <> "0"
strSearchString = strBaseMessage
strSearchChar = Chr(13)
strChrPos = InStr(1, strSearchString, strSearchChar, vbTextCompare)
If strChrPos = "0" Then Exit Do
Mid(strBaseMessage, strChrPos, 2) = ", "
Loop

rst!Message = strBaseMessage
rst!Received = MailItem.ReceivedTime
rst.Update
End If
Next i
rst.Close
MsgBox "All Email Has Been Imported"
Else
MsgBox "There Is No Email To Be Imported"
End If

End Function

taken from a previous post on here.

How do I either
a) seperate the text out before uploading to the table
or
b) seperate the field out after importing to table test into another table with the correct fields

Hope this makes sense!

TV

 

[PHV]

Have a look at the Split function (ac2k or above).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tractorvix]

Thanks PHV, that's exactly what I was looking for!

TV