Using Dao Object In Query In VBA - How? (1 Viewer)

[Lateral]

Hi guys,

I'm a newbie to Access and VBA so please be patient as I try to explain what I am doing and trying to do.

I have a simple Access application that I am using DAO to connect directly the Outlook Inbox in order to import email information into a table so that I can query it. I am not using the "Linked" method as I want the [EntryID] and other fields that are not available via the Linked method.

I have found some VBA code that I have massaged and it works well but I want to use SQL within my VBA code in order to make things easier and I believe faster.


Here are the DIM's that I have defined:

' Set up DAO objects (uses existing "tblEmail" table)
Dim ol As New Outlook.Application
Dim olns As Outlook.Namespace
Dim cf As Outlook.MAPIFolder
Dim c As Outlook.MailItem
Dim objItems As Outlook.Items
Dim rstmessages, rstfiles As DAO.Recordset
Dim MyPath As String
Dim nummessages As Integer
Dim checkName As String
Dim mSQL As String

' Set up Outlook objects.
Set olns = ol.GetNamespace("MAPI")

' Set the Inbox
Set cf = olns.GetDefaultFolder(olFolderInbox)



I want to run a simple Insert query that that will use the Outlook Inbox as the input and insert records if they don't already exist, into the table called "tblEmail"

.RunSQL "INSERT INTO tblEmail (EntryID, Subject) SELECT EntryID, Subject FROM ????????


I have spent ages trying to figure this out but can't figure out how to specify the Inbox as the FROM..

I hope somebody can help me

Cheers
Greg

 

[MarkK]

Cross posted: http://www.utteraccess.com/forum/index.php?showtopic=2044865&hl=

 

[Orthodox Dave]

Hi Greg,

Have you come across this site:
http://www.freevbcode.com/ShowCode.asp?ID=4837

I used a modified version of that to access Sent Items with a certain subject line and save the file as a .msg. But the basic tools are there to access the Inbox emails.

Hope it helps.

 

[Lateral]

Thanks Dave,

Yes I have seen this before but I think that I am very close to getting my code to work. i will definitely look again at the link to see if it can help.

Cheers
Greg

 

[NauticalGent]

Hi Greg,

To reinforce MarkK's post about cross-posting, it is considered bad manners to do so unless certain protocol is met. There is a link about the why's, do's and don'ts regarding this. Please give it a look over...

In regards to this question, have you considered simply linking the folder interactively instead of inserting them into a separate table?

 

[Lateral]

Thanks for the clarification....I cross posted as I am desperate for a solution to this issue...

The reason that I am not linking the folder is that I want to be able to display the emails on a form, double click it and display the actual email natively in Outlook. In order to do this I need the EntryID field which isn't one of the fields that is available when you Link to the Inbox or other Outlook folders. I also need the actual email addresses for both Sent and Inbox so that I can "attach/link" them to a Customer table so that I can keep track of all incoming and outgoing emails for each customer.....

Using the link to folder would be far easier ifI could get hold of the EntryID and some other fields.

Cheers
Greg

 

[NauticalGent]

Using the link to folder would be far easier ifI could get hold of the EntryID and some other fields.

I am not certain, but I think once you link the folder, you can see all fields. If not you have be able to tweak the settings so you can.

I would definitely try this approach since it involves the least amount of code. Once the table is linked, you can use it for forms, reports, queries etc...

 

[Lateral]

I have spent a lot of time looking to see if you can add to the fields that are available via the linked method without any luck...