SQL error and Send Email Error

[bhujanga]

I have a form for processing permit applications. When certain dates or actions are documented in the form, they want to be able to generate a preformatted email based on that particular event. They also want to be able to save a history of the emails that were sent.

So I have a table called "Email Default Templates" that stores Subject Line, CC, and Message Text for the various types of emails. I also have an "Emails" table where I store that information plus the recipient, permit number and date sent for each email that gets sent using this system.

I am putting command buttons next to these fields that say "Send Email" on them. I am using an SQL statement to append a record to "Emails" based on data looked up from the "Email Default Templates" table along with the current date, and information pulled from the form itself. Then I'm using the SendObject command to send the actual email.

The code for one of these buttons is below. The rest would be exactly the same except for the first line.When it gets to the CurrentDBExecute statement I get an error that says "Error in INSERT INTO statement.If I comment that out, then when I get to the SendObject statement I get an error that says 'Microsoft Access can't send this email. I'd appreciate any help with these problems. Thank you.

CODE:
EmailType = "Deficient Application Response" 'This is for looking up the right info in Templates table

PermitNo = Me![PermitNumber]
SendDate = Date
CCs = DLookup("[CC]", "Email Default Templates", "[Origination Key] = '" & EmailType & "'")
SubjectLine = DLookup("[SubjectLine]", "Email Default Templates", "[Origination Key] = '" & EmailType & "'")
Message = DLookup("[Message]", "Email Default Templates", "[Origination Key] = '" & EmailType & "'")
strAddressee= Me![PermitteeName]'Put info in table

Dim strSQL1 As Stringstr
SQL1 = "INSERT INTO [Emails] " & _"(PermitNumber, SendDate, CC, SubjectLine, Message, TO,) " & _"Select PermitNo as EXPR1, SendDate as EXPR2, CCs as EXPR3, SubjectLine as EXPR4, " & _"Message as EXPR5, strAddressee as EXPR6"

CurrentDb.Execute strSQL1, dbFailOnError

DoCmd.SendObject acSendNoObject, , , strAddressee, CCs, , SubjectLine, Message, True

 

[dhookom]

Where to start...

Looking at just the SQL, you have an extra comma after "TO".

I don't know if it's just formatting issues but the "& _" should be the last characters on a line.

The "Select " statement must have a "FROM " clause. I believe you want to insert "Values".

What is the data type for PermitNo?

My best guess at the SQL code is

strSQL1 = "INSERT INTO [Emails] " & _
  "(PermitNumber, SendDate, CC, SubjectLine, Message, TO) " & _
  "Values (" & PermitNo ", #" & SendDate & "#,""" & CCs & _
    """,""" & SubjectLine & """, """ & _
    Message & """, """ & strAddressee & """)"

Duane
Hook'D on Access
MS Access MVP

 

[bhujanga]

I just had time today to try this out. I had to adjust it a bit, but I was able to see from your suggestion the form that was needed, and I got the SQL part to work no problem.
Thanks for your help.