Add data from emails

[johnsm1981]

hi - please can someone correct this code. I am scraping an outlook email into access.
i can see it is doing that part successfully in the debug.print (immediate) window.

i just cant seem to add the recordset to the database file (rst.AddNew)
it is failing on "Do until EOF()" i've tried various code, when i cut this part out it then fails at "rst.AddNew" with a Runtime 91 - Object variable or With Block not set

thanks in advance!

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 Database
Dim rst As 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")

'table in access is called Email, field to update is called EmailData
For Each msg In olFolder.Items
Debug.Print msg.Body
BodyTxt = msg.Body

Do Until EOF() 'failing here "compile - argument not optional"

rst.AddNew 'failing here when i remove "until EOF" from above line
rst!EmailData = strData
rst.Update

Loop

Next
rst.Close

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

End Sub

 

[pbaldy]

Re: Logging Imports

I don't think you need the recordset loop, but the error is because you never set the recordset variable. If you do need the loop, it would be:

Do Until rst.EOF

You should also disambiguate which type of recordset you want, presumably DAO.

 

[johnsm1981]

Re: Logging Imports

Thanks Paul - i updated with your coding but the error remains at line EOF Runtime 91 - Object variable or With Block not set

updated below incase i have missed something from your post..

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")

'table in access is called Email, field to update is called EmailData
For Each msg In olFolder.Items
Debug.Print msg.Body
BodyTxt = msg.Body

Do Until rst.EOF() 'failing here Runtime 91

rst.AddNew
rst!EmailData = strData
rst.Update

Loop

Next
rst.Close

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

End Sub

 

[pbaldy]

Re: Logging Imports

Thanks Paul - i updated with your coding but the error remains at line EOF Runtime 91 - Object variable or With Block not set

You still aren't setting the recordset. Along the lines of:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM TableName WHERE 1=0", dbOpenDynaset)

I also highly doubt you need the recordset loop, and as is you have an endless loop.

 

[pbaldy]

FYI, I split your question and my answers to a new thread. The OP of the original thread took offense.

 

[johnsm1981]

Thanks i'll give this a try.
The reason I add a loop it is because the emails could be 800 lines long, so I thought the loop would drop to the next line each time it adds a recordset, until the email body has ended. I'll also try the code without the loop as sugggested.

If it helps the email body looks like this....
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

I'd expect 4 entries to be added to the database

 

[pbaldy]

Ah, you don't want a recordset loop. Use the Split() function to put the body into an array, and loop the array (split on vbCrLf, the line break). Use the AddNew inside that loop. If the values should be in separate fields, you can split on the comma within the line loop.

 

[johnsm1981]

Thanks again Paul, please could you edit my code and repaste with your code in? I've not used a split before so not sure where I'd add it. Thank you

 

[pbaldy]

Well, the pseudo code would be:

For Each msg In olFolder.Items
Debug.Print msg.Body
BodyTxt = msg.Body

ArrayVariable = Split(BodyTxt, vbCrLf)

  Loop ArrayVariable 

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

  Loop

Next

If you research the Split() function, you should see how the loop of it works. I'm not at my computer right now so don't have the code handy.

 

[Frothingslosh]

One note he should be aware of:

I have, on occasion, received text files where lines terminated with a line feed rather than a carriage return, and splitting on vbCrLf only catches the latter (at least, that's how it worked for me). If using vbCrLf in the split doesn't split them into individual lines, he should try again splitting on Chr(10).

 

[johnsm1981]

Hi Both, it worked (almost)

It didnt like the "Loop ArrayVariable"... ? **expected while or until or end of statement **

So it is just grabbing the 1st line from the email and adding it to the database, but at least its grabbing something now.

I've tried a few do/loops but it just repeats adding the 1st line for eternity.


Heres the latest, am i missing the obvious?

For Each msg In olFolder.Items
Debug.Print msg.Body
BodyTxt = msg.Body

ArrayVariable = Split(BodyTxt, vbCrLf)

'loop ArrayVariable **expected while or until or end of statement **

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

 

[pbaldy]

When I said "pseudo" I wasn't posting actual code, just a conceptual overview. I was addressing "I've not used a split before so not sure where I'd add it". Here's more:

https://support.microsoft.com/en-us/kb/266289

 

[johnsm1981]

thank you, I was after the code as I do not understand the above and how and where to change my code even after reading the split link, I am in unknown territory now so it not sure what to do next.