Help with MailItems

[ck4794]

Dim olNS As NameSpace
Dim item As Object
Dim Inbox As MAPIFolder
Dim SubInbox As MAPIFolder
Set olNS = GetNamespace("MAPI")
Set Inbox = olNS.GetDefaultFolder(olFolderInbox)
Set SubInbox = Inbox.Folders("TEST")
For Each item In SubInbox.Items
    If TypeOf item Is MailItem Then
        With item
            DoCmd.RunSQL "INSERT INTO TEST(subject, to) SELECT '" & .Subject & "','" & .To & "'"
        End With
    End If
Next item
Set ns = Nothing
Set olNS = Nothing
Set Inbox = Nothing
Set SubInbox = Nothing

So I'm using the above code to try and download my outlook emails into a table.


Table Name: TEST
SubInbox Name : TEST

Problem: I can't get anything past the subject to work. I can successfully download the subject, so I have atleast determined access is recognizing outlook properly and is going to the right folder, and pulling in the emails. I only need the highlights, to, from, date/time, subject, body, cc (maybe). Some things I'm running into problems with anything containing ' or " characters. Any advice?

[hapm]

So you can't get the data from the MailItem, or is it the quote and doublequote problem that prefents you from inserting the data in your table?

The quoting problem can be easily solved by using the following function on text data to add quotes as needed:

Function Quote(Text) As String
     Quote = """" & Replace(CStr(Text), """", """""") & """"
End Function

Your query would look like:

DoCmd.RunSQL "INSERT INTO TEST(subject, to) SELECT " & Quote(.Subject) & ", " & Quote(.To)

[ck4794]

first and most pressing problem is can't get the data from mailitem. it's telling me method body (or whatever) of object _mailitem has failed.

[rpeare]

have you put in the debug.print statements I originally gave you as part of the code to see if it's actually returning a value:

Dim olNS As NameSpace 
Dim item As Object 
Dim Inbox As MAPIFolder 
Dim SubInbox As MAPIFolder 

Set olNS = GetNamespace("MAPI") 
Set Inbox = olNS.GetDefaultFolder(olFolderInbox) 
Set SubInbox = Inbox.Folders("TEST")
dim sSQL as string

For Each item In SubInbox.Items
    If TypeOf item Is MailItem Then
        With item
            debug.print .subject
            debug.print .to
            debug.print .body
            'if you get values in these debug.print statements you can move on to the SQL which in your example won't work
            DoCmd.RunSQL "INSERT INTO TEST(subject, to) SELECT '" & & "','" & .To & "'"
        End With
    End If
Next item
Set ns = Nothing
Set olNS = Nothing
Set Inbox = Nothing
Set SubInbox = Nothing

Your SQL will not work because you're appending 1 record with predetermined values so you have to use the VALUES statement not the SELECT. you would use SELECT (I explained this in your other thread too I think) when you are trying to bulk add records from one table to another.

dim sSQL as string
ssql = "INSERT INTO TEST(subject, to) VALUES (" & Quote(.Subject) & ", " & Quote(.To) & ")"
debug.print ssql
DoCmd.RunSQL

using the debug.print statement on your SQL string will allow you to cut and paste it into a query window and see if you are getting the results you expect.

[ck4794]

debug.print commands are giving me method failed errors.

.subject still works... i think. nothing happens. where exactly is that supposed to "print"?

[rpeare]

in your IMMEDIATE window, if the immediate window is not visible (on mine it's in the lower portion of the VBA window) you can hit CTRL-G in your VBA window and it will pop up. I cut and paste the code I gave, you, commented out the sql statement and it worked fine.

Dim olNS As NameSpace
Dim item As Object
Dim Inbox As MAPIFolder
Dim SubInbox As MAPIFolder

Set olNS = GetNamespace("MAPI")
Set Inbox = olNS.GetDefaultFolder(olFolderInbox)
Set SubInbox = Inbox.Folders("TEST")
Dim sSQL As String

For Each item In SubInbox.Items
    If TypeOf item Is MailItem Then
        With item
            Debug.Print .subject
            Debug.Print .to
            Debug.Print .body
            Debug.Print .SenderEmailAddress
            'if you get values in these debug.print statements you can move on to the SQL which in your example won't work
            'DoCmd.RunSQL "INSERT INTO TEST(subject, to) SELECT '" & & "','" & .To & "'"
        End With
    End If
Next item
Set ns = Nothing
Set olNS = Nothing
Set Inbox = Nothing
Set SubInbox = Nothing

Again, the only reference you need is MS outlook xx.x object library.

[ck4794]

ok, found it. i've never used that before. so .subject still works just fine. .to gives me "run time error...... method 'to' of object '_mailitem' failed. i do have that reference selected.

[rpeare]

are you using the exact same code I posted in my last post? (if you didn't cut and paste it into your module you're not)

[ck4794]

yes it's the exact same code

[rpeare]

ck4794.zip

Try this sample database

[ck4794]

ck4794.zip

Try this sample database

no dice. still getting hung up on that .to line saying run time error method don't work. double checked my references and i still have that outlookx.xlibrary

[June7]

I tested rpeare's db and the code runs without error. Info is output to immediate window. Using Access 2010.

[rpeare]

the references are specific to the database, if you opened mine, ran the module, and still got the message there's something else going on that I don't understand, I created that using access 2007 so there shouldn't be a compatibility issue.

if it's dying on the debug.print .to I am not really sure what to suggest. Did you run the module in my database, or did you copy it to your database?

[ck4794]

here's what i'm getting. yes i changed the folder name (this is as i'm switching back and forth between computers, but working the same principle). that wasn't the problem because again, subject is working fine.

[ck4794]

the references are specific to the database, if you opened mine, ran the module, and still got the message there's something else going on that I don't understand, I created that using access 2007 so there shouldn't be a compatibility issue.

if it's dying on the debug.print .to I am not really sure what to suggest. Did you run the module in my database, or did you copy it to your database?

both, copy and paste to mine, plus opening the one from the zip and run that.