-
Jul 30th, 2017, 04:10 PM
#1
Thread Starter
New Member
Importing Outllook email data into Access 2007 table issue
Hi guys,
This is my first post in these forums and I am hoping somebody can help me as I have spent many days trying to solve this issue.
I'm also a bit of a newbie so please be gentle
I have developed an Access 2007 application for my own use for invoicing and other administration tasks.
I both send and receive emails to/from customers. I want to interface directly with Outlook so that I can "see" a list of all emails relating to a customer based upon their email address. I also want to "open/view" the email from my Access application. I can't use the "Linked' table method as I need the [EntryID] field.
After many days of Googling etc, I have the following code that does 99% of what I want.
It basically connects to the Outlook Sent folder and imports all records into the table called "tblEmail"
I can then open a specific email based upon its [EntryID]...this all works really well.
Here is the code I am using that works 99%:
Code:
Sub ImportInboxFromOutlook()
' This code is based in Microsoft Access.
' 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
'open a couple of recordsets to handles emails and attachments
Set rstmessages = CurrentDb.OpenRecordset("tblEmail")
Set rstfiles = CurrentDb.OpenRecordset("tblFileAttachments")
'grab the file path to the folder in which we will place attachments
MyPath = DLookup("[Folderpath]", "tlkplookup")
'check existence create if not
If Not FolderExists(MyPath) Then
If MsgBox("Do you want me to create the folder " & MyPath, vbYesNo + vbQuestion, "Create folder") = vbYes Then
MkDir MyPath
Else
GoTo here:
End If
End If
'Delete the records from the tables
'CurrentDb.Execute "DELETE * FROM tblEmail", dbFailOnError
'CurrentDb.Execute "DELETE * FROM tblFileAttachments", dbFailOnError
' Set up Outlook objects.
Set olns = ol.GetNamespace("MAPI")
' Set the Inbox
'Set cf = olns.GetDefaultFolder(olFolderInbox)
'Set the Sent Folder
Set cf = olns.GetDefaultFolder(olFolderSentMail)
Set objItems = cf.Items
nummessages = objItems.Count
If nummessages <> 0 Then
For i = 1 To nummessages
Forms!frmEmail.lblwarn.Caption = "Processing item " & i & " of " & nummessages
Forms!frmEmail.Repaint
If TypeName(objItems(i)) = "MailItem" Then
Set c = objItems(i)
rstmessages.AddNew
'rstmessages.AddNew
rstmessages!EntryID = c.EntryID
rstmessages!Subject = c.Subject
rstmessages!Sender = c.SenderName
rstmessages!SenderEmail = c.SenderEmailAddress
rstmessages!To = c.To
rstmessages!ToEmail = c.Recipients.Item(1).Address
rstmessages!SentDate = c.SentOn
rstmessages!ReceivedTime = c.ReceivedTime
rstmessages!Body = c.Body
rstmessages!Folder = "Sent"
rstmessages!MessageSize = c.Size
rstmessages!DateRecordAdded = Now()
rstmessages!Importance = c.Importance
rstmessages!Attachments = c.Attachments.Count
rstmessages.Update
' now look at attachments per email message as it loop into the tblemailattachment table
' If c.Attachments.Count > 0 Then
' For X = 1 To c.Attachments.Count
' MyFileName = c.Attachments.Item(X).FileName
' rstfiles.AddNew
' rstfiles!EntryID = c.EntryID
' rstfiles!FileName = Trim(MyPath & MyFileName)
' rstfiles.Update
' 'create the file only if it is not already there
' On Error Resume Next
' If Not FileExists(MyPath & MyFileName) Then
' c.Attachments.Item(X).SaveAsFile (MyPath & MyFileName)
' End If
' Next
' End If
End If
Next i
here:
'finished close the recordsets and cleanup
rstmessages.Close
rstfiles.Close
MyPath = ""
Set ol = Nothing
Set olns = Nothing
Set cf = Nothing
Set c = Nothing
Else
End If
End Sub
The problem I have is this.
Due to the high number of emails in the Sent and Inbox folders (I will be changing the the VBA code to handle both folders), I want to do an initial import and then only "add" new emails to the table....this logic is what is causing me the stress!
Here is the structure of the table:
The [EntryID] field is indexed and does not allow duplicates which is what I assume is required.
This is what I am doing to test:
1. Starting with an empty table, I do an initial import of 188 records from the Outlook Sent folders. This works perfectly.
2. I then open the table and manually delete 10 rows so that I have 178.
3. I then run the import again expecting that it will import the missing 10 in order to bring the total up to 188 but it doesn't!
Also, if I do the following test:
1. Starting with an empty table, I do an initial import of 188 records from the Outlook Sent folders. This works perfectly.
2. I then send a new test email so that there are 189 in the Sent folder.
3. I then run the import expecting to have the 189th email added to the table but this does not happen!
I am sure that I am missing something really simple but I have spent hours and hours trying to figure this issue out without any success.
I hope one of you guys or girls can help me simple add the missing records.
Cheers
Greg
Hi guys,
Update 1:
I have been doing some more testing and it appears that the VBA code works sometimes.
This is what I have done:
1. Run initial load of 188 emails.
2. Open table and manually delete 88 rows so that I end up with 100 rows in the table.
3. Run the import again and it inserts 33 rows, not the missing 100.
4. I open the table and delete all of the rows and run the initial load of 188 emails again.
5. I then open the table and delete 187 rows leaving me with 1 row.
6. I then run the import again and I get 33 rows inserted.
Why is it happening???
Update 2:
I have added some error checking to see if there is an error that I am missing and received the following error:
Cheers
The only field that is indexed and does not allow duplicates is the [EntryID] field which should be unique. if I manually delete a bunch of rows and the [EntryID] 's are also deleted which mean that I should be able to add these email records again.....what am i missing?
Last edited by Lateral; Jul 30th, 2017 at 07:10 PM.
Reason: More testing
-
Aug 1st, 2017, 09:42 AM
#2
Re: Importing Outllook email data into Access 2007 table issue
so no linked tables
as far as i can see, i think you have 3 choices
1) the (in)famous on error resume next
or
2) before adding a record in the access table, see first if it does not already exists
or
3) have a temporary table, the same as the access table,
empty this table before an import
do the import into this table
import this table into the access table by means of an append query
[code]
INSERT INTO TheAccessTable SELECT * FROM TheTemporyTable;
[code]
do not put off till tomorrow what you can put off forever
-
Aug 4th, 2017, 02:45 AM
#3
Re: Importing Outllook email data into Access 2007 table issue
Lateral
The only field that is indexed and does not allow duplicates is the [EntryID] field which should be unique.
At the risk of being nit-picky, per your image, it appears that the ID field is unique .. designated by AutoNumber
My guess is that the AutoNumber data type is the issue.
A shot in the dark, but can you "turn off" the AutoNumber, and instead make it a Number data type?
Spoo
-
Aug 4th, 2017, 02:55 AM
#4
Thread Starter
New Member
Re: Importing Outllook email data into Access 2007 table issue
Hi Spoo
I deleted the ID field and the problem still happens
-
Aug 4th, 2017, 03:13 AM
#5
Re: Importing Outllook email data into Access 2007 table issue
Lateral
OK, so I take it that there are now NO fields of AutoNumber data type.
Sadly, my Access knowledge is weak.
Maybe you could do a search on Error 3022 .. here in the Forums or on Google or Bing.
Spoo
-
Aug 5th, 2017, 11:51 AM
#6
Re: Importing Outllook email data into Access 2007 table issue
Hi Lateral,
this sample(not my Code) uses ADO, perhaps it will Help
Code:
'~~> READ ME FIRST
''
'~~> add reference to ADO object library. You can do this from within the VBE by
'~~> selecting the menu Tools, References and selecting Microsoft
'~~> ActiveX Data Objects x.x Object Library.
'
'~~> This sample also assumes that you have a database with the name
'~~> MyDatabase.mdb which resides in C:\ and has the following fields
'~~> "Subject","Body","FromName","ToName","FromAddress","FromType"
'~~> "CCName","BCCName","Importance" and "Sensitivity"
'
'~~> This sample also assumes that the database has a table called Table1
Dim adoConn As ADODB.Connection, adoRS As ADODB.Recordset
Dim DBFullName As String
Dim ns As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim i As Long
Sub ExportToAccess()
Set ns = GetNamespace("MAPI")
Set objFolder = ns.PickFolder
'~~> Replace with your database
DBFullName = "C:\temp.mdb"
Set adoConn = New ADODB.Connection
'~~> Open the database using connection string
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set adoRS = New ADODB.Recordset
With adoRS
'~~> Replace Table1 with the name of the table in your database
.Open "SELECT * FROM Tabelle1", adoConn, , adLockOptimistic, adCmdText
For i = objFolder.Items.Count To 1 Step -1
With objFolder.Items(i)
If .Class = olMail Then
adoRS.AddNew
adoRS("Subject") = .Subject
adoRS("Body") = .Body & " "
adoRS("FromName") = .SenderName
adoRS("ToName") = .To
' adoRS("FromAddress") = .SenderEmailAddress & " "
' adoRS("FromType") = .SenderEmailType & " "
adoRS("CCName") = .CC & " "
adoRS("BCCName") = .BCC & " "
adoRS("Importance") = .Importance
adoRS("Sensitivity") = .Sensitivity
adoRS.Update
End If
End With
Next
End With
'~~> Close and Cleanup
adoRS.Close
Set adoRS = Nothing
Set adoConn = Nothing
Set ns = Nothing
Set objFolder = Nothing
End Sub
Private Sub Command1_Click()
ExportToAccess
End Sub
regards
Chris
-
Aug 16th, 2017, 12:45 AM
#7
New Member
Re: Importing Outllook email data into Access 2007 table issue
Check EntryID existing before AddNew
Using NZ() for text/memo field
-
Aug 23rd, 2017, 12:14 PM
#8
Re: Importing Outllook email data into Access 2007 table issue
Originally Posted by kam.peter@gmail.com
Check EntryID existing before AddNew
Using NZ() for text/memo field
what do you mean ?
regards
Chris
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|