Email automation run-time error 2147467259

[goestejs]

I have the following code which is run after a report is exported to a pdf file which should attach the report to an email with a pre-defined body and automatically send to the correct recipient based on a query. I am getting a run-time error 2147467259 "There must be at least one name or contact group in the To, Cc, or Bcc box. I am fairly certain this is because the recordset appears empty when it is opened, but I cannot understand why. If I run the query (SELECT RECIPIENT FROM qry_Sales_IDI_Email WHERE INVOICE = Forms!frm_Sales!INVOICE independently, then I get the expected resulting RECIPIENT email address.

Public Function Send_Sales_IDI_EMail()
 
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
 
FileID = Forms!frm_Sales!INVOICE
mysql = "SELECT RECIPIENT FROM qry_Sales_IDI_Email WHERE INVOICE = " & FileID & ";"
myattach = "\\MSTFPS004\Finance\" & FileID & ".pdf"
 
Set fso = New FileSystemObject
Subjectline$ = "Sales IDI " & FileID
 
If Subjectline$ = "" Then
   MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
      "Quitting...", vbCritical, "E-Mail Merger"
   Exit Function
End If
 
BodyFile$ = "\\MSTFPS004\Finance\IDIEMailBody.txt"
 
If BodyFile$ = "" Then
   MsgBox "No body, no message." & vbNewLine & vbNewLine & _
      "Quitting...", vbCritical, "I Ain't Got No-Body!"
   Exit Function
End If
 
If fso.FileExists(BodyFile$) = False Then
   MsgBox "The body file isn't where you say it is. " & vbNewLine & vbNewLine & _
      "Quitting...", vbCritical, "I Ain't Got No-Body!"
   Exit Function
End If
 
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
MyBodyText = MyBody.ReadAll
MyBody.Close
 
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MailList = db.OpenRecordset(MySQL)
Set MyMail = MyOutlook.CreateItem(olMailItem)
 
Do Until MailList.EOF
    MyMail.Recipients.Add MailList("RECIPIENT")
    MailList.MoveNext
Loop
 
MyMail.Subject = Subjectline$
MyMail.Body = MyBodyText
MyMail.Attachments.Add myattach, olByValue, , "Sales IDI"
MyMail.Send
 
Set MyMail = Nothing
Set MyOutlook = Nothing
 
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
 
End Function

Mod Edit: include CODE tags and fix code more readable

[June7]

I don't see a recordset opened in that procedure. It has a variable to hold SQL statement but it is not used to open a recordset.

It is not necessary to save PDF document to send email with attached PDF of report. SendObject method can do that and don't need to manipulate an Outlook object.

[goestejs]

Here is where the recordset is being opened and defined as MailList.
Set MailList = db.OpenRecordset(MySQL)
I realize that it is not necessary to save the pdf, but we also want a soft copy on our network for SOX requirements.

[June7]

Sorry, couldn't find it before.

Maybe open the recordset at beginning then wrapping the rest of code in:

If Not MailList.EOF Then
...
End If

Now why the recordset has no records is another issue. I don't see anything wrong with that code. FileID is a number type field?

Could be record is there but pointer is at EOF. Try moving to first record immediately after opening recordset:

MailList.MoveFirst

[goestejs]

FileID is text since it contains dashes. I inserted MailList.MoveFirst after Set MailList = db.OpenRecordset(mysql) and I now get run-time error 3021 "No current record." I should mention that I have used this same code before in another database without receiving this error.

[June7]

Correction, my question should have been: Is INVOICE a text type field. And apparently it is which means the FileID parameter needs apostrophe delimiters.

INVOICE = '" & FileID & "';"

Although, I would expect a different error message.

[goestejs]

Thanks June7! Adding the single quotes did the trick.