SQL to copy Emails from linked inbox

[WhiskyLima]

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:

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!

[cbende2]

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.

[WhiskyLima]

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?

[cbende2]

Check your quotes,

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

[WhiskyLima]

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?

[cbende2]

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.

[WhiskyLima]

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?

[WhiskyLima]

This is the code im using:

    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.

[WhiskyLima]

Ok I have found this:

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

[cbende2]

Good luck! I'm enjoying watching your progress!