Results 1 to 8 of 8

Thread: Importing Outllook email data into Access 2007 table issue

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2017
    Posts
    4

    Talking 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:

    Name:  vbforums001.jpg
Views: 517
Size:  45.9 KB

    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:

    CheersName:  vbforums002.jpg
Views: 464
Size:  29.9 KB

    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

  2. #2
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    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

  3. #3
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    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

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2017
    Posts
    4

    Re: Importing Outllook email data into Access 2007 table issue

    Hi Spoo

    I deleted the ID field and the problem still happens

  5. #5
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    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

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    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

  7. #7
    New Member
    Join Date
    Aug 2017
    Posts
    1

    Re: Importing Outllook email data into Access 2007 table issue

    Check EntryID existing before AddNew
    Using NZ() for text/memo field

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Importing Outllook email data into Access 2007 table issue

    Quote Originally Posted by kam.peter@gmail.com View Post
    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
  •  



Click Here to Expand Forum to Full Width