CODEWITHSUNDEEP
vbaloopsemailms-accesssplit
Mark Johnson
Mark Johnson

Reputation: 103

VBA data from email loop

Please can someone complete this code it's driving me insane. I am scraping data from emails into access. At this stage I can only scrape the Top line into access

I just can't seem to get the Loop Array to work to drop to the next line, and so on until EOF

Email body looks like this, (could be 700 lines)
(I'd expect the below to catch 4 lines and add to the database.) 

N,F,AWB,932,50960003,5,MARKBR1,20160820,9300
N,F,AWB,175,04391936,4,MARKBR1,20160824,0004
N,M,AWB,195,04990293,4,JEFFBR1,20160824,0004
N,F,AWB,245,04415213,4,ALLANBR1,20160824,0004

Code as follows (thanks in advance) 

Option Compare Database

Sub getEmails()

Dim olApp As Outlook.Application
Dim olNamespace As Outlook.NameSpace
Dim k
Dim j
Dim x
Dim xXx
Dim olFolder As Outlook.MAPIFolder
Dim lngCol As Long
Dim olItem As Object
Dim whichAccount As String
Dim objNS As Outlook.NameSpace 
Dim BodyTxt As String
Dim BodyRow As String
Dim db As dao.Database
Dim rst As dao.Recordset
Dim strData As String

Set olApp = New Outlook.Application
Set olNamespace = olApp.GetNamespace("MAPI")
Set objNS = olApp.GetNamespace("MAPI")
Set olFolder = objNS.GetDefaultFolder(olFolderInbox).Folders("MarkDrafts")
Set rst = CurrentDb.OpenRecordset("SELECT * FROM email WHERE 1=0", dbOpenDynaset)

'table is called Email, field is called emaildata
For Each msg In olFolder.Items
Debug.Print msg.Body
BodyTxt = msg.Body

ArrayVariable = Split(BodyTxt, vbCrLf)

'loop ArrayVariable    <<< this bit don’t work so it doesn’t drop to the next line

rst.AddNew
rst!EmailData = ArrayVariable(x)
rst.Update

'loop

Next

rst.Close

Set olApp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set Mailobject = Nothing
Set TempRst = Nothing

End Sub

Upvotes: 2

Views: 52

Answers (1)

dbmitch
dbmitch

Reputation: 5386

I don't know if you're just trying to use pseudocode because that is not loop code.

You're not incrementing x at all

Subsitute

'loop ArrayVariable    <<< this bit don’t work so it doesn’t drop to the next line

rst.AddNew
rst!EmailData = ArrayVariable(x)
rst.Update

'loop

With this actual loop

For x = lBound(ArrayVariable) to uBound(ArrayVariable)

   rst.AddNew
   rst!EmailData = ArrayVariable(x)
   rst.Update

Next x

Upvotes: 3

Related Questions