Results 1 to 10 of 10
  1. #1
    WhiskyLima is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2013
    Location
    UK
    Posts
    32

    SQL to copy Emails from linked inbox

    Hi guys, im having a bit of trouble with this. basically I want to 'save' certain emails from a linked inbox folder. the folder takes its info from a IMAP email account and I want to be able to archive old messages from this to the database so the are not removed from the database if they are deleted from the IMAP account.

    So here's what I have got:

    Code:
    strSQL = "INSERT INTO SavedEmails ([Sender Name], [Subject], [Received], [Content]) SELECT [Sender Name], [Subject], [Received], [Contents] FROM Inbox WHERE [Sender Name] ='" & Forms![assignEmail].OpenArgs & "'"
    When I execute this code, it gives me an error: Runtime Error 3001 - invalid argument. When I select debug it just points to the execution of the SQL and gives me no further information. The openArgs for the form is basically just a name

    What am I missing? Any help would be appreciated!

  2. #2
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    In the INSERT INTO, you have [Content], in the SELECT you have [Contents], that could be your problem??

    I'm not really familiar with SQL though so I'm not sure :\

    but thats the only thing that seems off in the statement.

  3. #3
    WhiskyLima is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2013
    Location
    UK
    Posts
    32
    Yeah that's how it should be, the two tables have some slight differences but that should cause any issues. Even with just the [Sender Name] in both tables I still seem to get this error. Am I missing something fundamental here?

  4. #4
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Check your quotes,

    and like I said, I don't know too much about SQL

  5. #5
    WhiskyLima is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2013
    Location
    UK
    Posts
    32
    Ok going over this it seems that I can perform this exact same SQL on another table in the database (created a mock table). Does this method not work on linked external data such as outlook inbox data? I would have thought this would behave the same way that a normal linked list would?

  6. #6
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    That very well could be the problem, But I don't have a definite answer for you, that sounds like something for google! Try doing a little research on linked external data outlook in access, or something along those lines.

  7. #7
    WhiskyLima is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2013
    Location
    UK
    Posts
    32
    Ok I think im a little further forward now. Instead of trying to use SQL in this way I have created a loop which takes the information from each field in the email table 'Inbox' and then deposits the information into variables. these are then used to create the new email in the saved emails from again using SQL. Now this is fine but it seems that the Content section of the email, which often contains special characters, screws everything up. Is there a way to tell the vba to ignore any special characters in a string?

  8. #8
    WhiskyLima is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2013
    Location
    UK
    Posts
    32
    This is the code im using:

    Code:
        strSQL2 = "SELECT [Sender Name], [Subject], [Received], [Contents] FROM Inbox WHERE [Sender Name] ='" & Forms![assignEmail].OpenArgs & "'"
        Set rst = db.OpenRecordset(strSQL2)
        rst.MoveLast
        If rst.RecordCount > 0 Then
            rst.MoveFirst
            Do While Not (rst.EOF)
                recTemp = rst.Fields("[Received]")
                sendTemp = rst.Fields("[Sender Name]")
                subTemp = rst.Fields("[Subject]")
                conTemp = rst.Fields("[Contents]")
                db.Execute "INSERT INTO SavedEmails ([Sender Name], [Received], [Subject]) VALUES ('" & sendTemp & "', '" & recTemp & "', '" & subTemp & "')"
                rst.MoveNext
            Loop
        End If
    As you can see I have not included the [Contents] of the email and have had success with this method. It does exactly what I want. but im still having trouble with the special characters in the content of the email.

  9. #9
    WhiskyLima is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2013
    Location
    UK
    Posts
    32
    Ok I have found this:

    Code:
    Function ValidateString(strInput As String) As String
        Dim strInvalidChars As String
        Dim i As Long
        strInvalidChars = "\/:*?""<>|'"
        For i = 1 To Len(strInvalidChars)
            strInput = Replace$(strInput, Mid$(strInvalidChars, i, 1), "")
        Next
        ValidateString = strInput
    End Function
    Which remove all the special characters that could screw things up and then returns the original string. Cool.. now for the attachments :S

  10. #10
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    Good luck! I'm enjoying watching your progress!

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

Similar Threads

  1. Replies: 2
    Last Post: 04-15-2014, 10:03 PM
  2. Emails with a Macro
    By srcacuser in forum Access
    Replies: 5
    Last Post: 05-02-2012, 11:49 AM
  3. Linked Outlook emails
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 04-15-2012, 11:16 AM
  4. Replies: 5
    Last Post: 02-02-2012, 06:42 PM
  5. Linked tables and copy
    By TimG in forum Access
    Replies: 2
    Last Post: 10-08-2009, 07:54 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