Outlook import

[ck4794]

I've been trying to find a way to import outlook emails to access through vba.



Every code sample I've encountered has something like ...GetNamespace("MAPI")

I can't get past this, I keep getting an error:

Run-time error'91':
Object variable or with block variable not set

Can anybody help with this?

Below is the entire code i've attached to a command button

Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olMail As Outlook.MailItem
Dim eFldr As Outlook.MAPIFolder
Set oldapp = olApp.GetNamespace("MAPI")
Set eFldr = olNS.Folders("ASM")
For Each olamil In eFldr.Items
    DoCmd.RunSQL "INSERT INTO ASM(subject) " _
                & "SELECT " & olMail.Subject
    Next
Set olApp = Nothing
Set olNS = Nothing
Set olMail = Nothing
Set olMail = Nothing
Set eFldr = Nothing

[ck4794]

To expand on that, I intentionally only have the subject pulling right now, just to get it working right before I mess with the rest of it. And the folder in my inbox is called "ASM". The table I want it to go to is also called "ASM". Thanks again in advance.

[pbaldy]

You haven't set the olApp variable at the point you try to use it. See if the "Saving an Email Attachment" code here:

http://www.thatlldoit.com/Pages/howtosarticles.aspx

helps you. It got me started with importing from Outlook.

[ck4794]

No matter what I tweak it seems to be stuck on that.

[pbaldy]

What's your code now?

[rpeare]

Have you enabled the reference for MAPI controls it may be the reference

Microsoft CDO For Windows Library
or
Microsoft Outlook xx.x Object Library

[pbaldy]

I think you'd get a compile error rather than a runtime error if it was a reference problem.

[rpeare]

maybe though I've gotten that error on a bad reference before don't remember how.

If it's bombing on the runSQL command you can try this:

DoCmd.RunSQL "INSERT INTO ASM (subject) VALUES (" & olMail.Subject & ")"

you would only use SELECT if you were pasting from one table to another I would think plus you'd want to include brackets around the values.

[ck4794]

maybe though I've gotten that error on a bad reference before don't remember how.

If it's bombing on the runSQL command you can try this:

DoCmd.RunSQL "INSERT INTO ASM (subject) VALUES (" & olMail.Subject & ")"

you would only use SELECT if you were pasting from one table to another I would think plus you'd want to include brackets around the values.

as far as I can tell it's not even making it that far

[ck4794]

Have you enabled the reference for MAPI controls it may be the reference

Microsoft CDO For Windows Library
or
Microsoft Outlook xx.x Object Library

checked both of those, no dice

[ck4794]

What's your code now?

i'm back at the original code i have posted, minus the typos. also i had to switch to the home computer, and now my folder, table, and code all say TEST instead of ASM. I don't have any extra junk in this database. simply a table, and a blank form with a button. if i get it working i'll copy and paste the code in an email or something.

[rpeare]

this is the code I used to run through my inbox:

Dim ns As NameSpace
Dim item As Object
Dim inbox As MAPIFolder

Set ns = GetNamespace("MAPI")
Set inbox = ns.GetDefaultFolder(olFolderInbox)

For Each item In inbox.Items
    If TypeOf item Is MailItem Then
        With item
            Debug.Print .Subject
            Debug.Print .Body
        End With
    End If
Next item

Set ns = Nothing

it correctly (seemingly) printed out both the subject and body of each mail, should be a short jump to appending that to a table, just be aware that if the body of any of your emails contains a " or ' mark you may encounter errors appending it to a table and you may have to run a replace() command to get rid of them.

EDIT: I'm only using Microsoft Outlook xx.x Object Library reference for this.

[ck4794]

scratch that. i started messing with the example from the very first link. i got past that error. now on my set efldr command i'm getting an error. trying to use a number like the example, i get an invalid procedure error. trying the folder by name gets me a type mismatch. code posted below:

Private Sub Command0_Click()
Dim olApp As Object
Dim olNS As Object
Dim olMail As Object
Dim eFldr As Object

Set olApp = CreateObject("outlook.application")
Set olNS = olApp.GetNamespace("mapi")
Set eFldr = olNS.GetDefaultFolder("test")
For Each olMail In eFldr.Items
    DoCmd.RunSQL "INSERT INTO test(subject) " _
                & "SELECT " & olMail.Subject
    Next
Set olApp = Nothing
Set olNS = Nothing
Set olMail = Nothing
Set eFldr = Nothing
End Sub

[ck4794]

this is the code I used to run through my inbox:

Dim ns As NameSpace
Dim item As Object
Dim inbox As MAPIFolder

Set ns = GetNamespace("MAPI")
Set inbox = ns.GetDefaultFolder(olFolderInbox)

For Each item In inbox.Items
    If TypeOf item Is MailItem Then
        With item
            Debug.Print .Subject
            Debug.Print .Body
        End With
    End If
Next item

Set ns = Nothing

it correctly (seemingly) printed out both the subject and body of each mail, should be a short jump to appending that to a table, just be aware that if the body of any of your emails contains a " or ' mark you may encounter errors appending it to a table and you may have to run a replace() command to get rid of them.

EDIT: I'm only using Microsoft Outlook xx.x Object Library reference for this.

i commented out my code and inserted yours, changing the debug to sql insert into. i get nothing... but at least no error.

EDIT: so i got NOTHING because i commented out the first line of "private sub" hahaha. but so now, i'm getting a new error. missing operator in [subject line of first email]. so i have confirmed that they are at least talking to each other.

EDIT: EDIT: working!!!! forgot to put some quotes around there. we're good. now i've got to toy with it to get to the sub folder.

[rpeare]

is your TEST folder a subfolder within your inbox? or is it outside of it (i.e. same level as junk mail, sent items, etc)

EDIT clicked save instead of something else...

if the TEST folder is a subfolder of your inbox you can change it to this:

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

Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
Set SubInbox = Inbox.Folders("TEST")


For Each item In SubInbox.Items
    If TypeOf item Is MailItem Then
        With item
            Debug.Print .Subject
            Debug.Print .Body
        End With
    End If
Next item

Set ns = Nothing