Results 1 to 5 of 5
  1. #1
    NiMora is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Location
    Germany
    Posts
    17

    Problems to import Mails from Outlook via vba


    Hi Access-Guys,

    I use the following code to get all my mails into a table:

    Code:
    Option Compare
    DatabaseOption Explicit
    
    
    Private Sub Befehl17_Click()
    ' ##################### OutlookImport ##################################
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim outObject, Mapi, Inbox, InboxImported
    Dim i As Integer
    Dim strSQL As String
    
    
    Set rs = CurrentDb.OpenRecordset("OutlookImport")
    Set outObject = CreateObject("Outlook.Application")
    Set Mapi = outObject.GetNamespace("MAPI")
    Set Inbox = Mapi.GetDefaultFolder(olFolderInbox).Folders("import")
    Set InboxImported = Mapi.GetDefaultFolder(olFolderInbox).Folders("imported")
    
    
        For Each Mail In Inbox.Items
        
        strSQL = "INSERT INTO OutlookImport (AbsenderMail, AbsenderName, SendTo, SendCC, Betreff, MailDatum, Nachricht, EntryID) VALUES ('" & Mail.SenderEmailAddress & "', '" & Mail.SenderName & "', '" & Mail.To & "', '" & Mail.CC & "', '" & Mail.Subject & "', '" & Mail.SentOn & "', '" & Mail.Body & "', '" & Mail.EntryID & "');"
    
    
            DoCmd.SetWarnings False
            On Error Resume Next
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
        
             If Not Err.Number <> 0 Then
                Mail.Move InboxImported                    
             End If
        Next
    
    
        Forms![OutlookImport].Requery
    End Sub
    I check all the mails in the "import-folder", import the content into the database and after that I move the mails into the "imported-folder".

    Actually it works, but there are a lot of mails generate an error. I can handle that with the "On Error Resume Next" but so I lost many mails to move and I want to know why the error happens.

    the protocol shows me the following error message: "Syntax error (missing Operator)" and Err.Description shows me the content of the mail.

    can anybody help me with that strange stuff or has an idea? It would be helpful.

    thx



  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    One possibility is text of one of the Outlook elements (most likely Subject or Body) has apostrophe or quote mark and the SQL statement is choking on the special character within text string. I know how to handle apostrophes but quote marks throw me and I forbid quote marks in data entry for my database.

    Replace(Mail.Subject, "'", "''")

    Replace(Mail.Body, "'", "''")

    Just did a test to replace quote mark with apostrophe and that works:

    Replace(Mail.Body, """", "'")

    So to handle both possibilities:

    Replace(Replace(Mail.Body, """", "'"), "'", "''")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    NiMora is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Location
    Germany
    Posts
    17
    Hey June7,

    thx 4 the qick reply.

    I change it to this:
    Code:
        strSQL = "INSERT INTO OutlookImport (AbsenderMail, AbsenderName, SendTo, SendCC, Betreff, MailDatum, Nachricht, EntryID) VALUES ('" & Mail.SenderEmailAddress & "', '" & Mail.SenderName & "', '" & Mail.To & "', '" & Mail.CC & "', '" & Mail.Subject, "'", "''" & "', '" & Mail.SentOn & "', '" & MailTextMail.Body, "'", "''" & "', '" & Mail.EntryID & "');"
    but I get already a syntax error as I try to compile the code.

    Maybe I misunderstand your Replace note?!?!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Have to use the Replace() function as I demonstrated.

    I did some more testing on the quote mark and this might work:

    Replace(Replace(Mail.Body, """", """"""), "'", "''")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    NiMora is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Location
    Germany
    Posts
    17
    June7, you are my hero of the day!!!!

    I work almost two days on that problem.

    big thx, particularly for the encapsulated Replace function. after I read a little bit about that, I understand.

    so, again: BIG thx

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Outlook import
    By ck4794 in forum Programming
    Replies: 19
    Last Post: 06-05-2014, 07:13 AM
  2. Access 97 Sending a Report to Outlook with Problems
    By PianistChris in forum Access
    Replies: 3
    Last Post: 07-18-2013, 12:17 PM
  3. Import data from Outlook e-mails to table
    By Lusitan in forum Import/Export Data
    Replies: 9
    Last Post: 07-11-2012, 12:17 PM
  4. Import from Outlook
    By funi.t in forum Import/Export Data
    Replies: 1
    Last Post: 12-20-2011, 03:17 AM
  5. Send multiple e-mails through Outlook based on query
    By dataphile in forum Programming
    Replies: 3
    Last Post: 12-30-2009, 12:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums