email report through module error but unknown where

[Jake F]

I run a report that was setup before me. I push a button and a module runs
the report, fills in an email (to, body, signature) and then sends it out.
The problem is when I run the report on 12 of the 100 or so emails are sent
out. The only error I receive is "This expression is typed incorrectly, or
it is too complex to be evaluated. For example, a nmeric expression may
contain to many complicated elements. Try simplifying the expression by
assigning parts of the expression to variables." I don't understand why I
would get this when it sent out a couple emails perfectly fine and I can't
find anything wrong in the module but I'm not very deep as far as programming
goes. Are there any generic causes I should look at?

 

[John W. Vinson]

I run a report that was setup before me. I push a button and a module runs
the report, fills in an email (to, body, signature) and then sends it out.
The problem is when I run the report on 12 of the 100 or so emails are sent
out. The only error I receive is "This expression is typed incorrectly, or
it is too complex to be evaluated. For example, a nmeric expression may
contain to many complicated elements. Try simplifying the expression by
assigning parts of the expression to variables." I don't understand why I
would get this when it sent out a couple emails perfectly fine and I can't
find anything wrong in the module but I'm not very deep as far as programming
goes. Are there any generic causes I should look at?

One case that can do this is improper interpretation of a date.

Care to post your button code? It's more than a mite difficult to debug code
that you can't see. If the button runs a query, or if the report is based on a
query, it might help to also post that query's SQL.

John W. Vinson [MVP]

 

[Jake F]

Here is the code. It runs a query and pulls data from a couple tables. All
this is done through the module I copied the code from. I thought it might
be because an email wasn't valid and it got confused, I'll check on the date
parts but I can't figure out why it would send the first twelve reports and
not the other 90 or so. Also, I ran the report alone and it works fine and
pulled for everyone. Not sure if that helps locate the problem. Thanks.

Public Function DivTransEmail()
On Error GoTo Err_DivTransEmail
Const strQueryName = "select * from qryEmailList"
Dim strMessageBody As String
Dim CRLF As String
CRLF = Chr(13) + Chr(10)
' Resume UClick to avoid having to respond to Outlook security
UClickResume
DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery ("qryDeleteDivisionTransactionsForReps")
Dim rs As DAO.Recordset
' Open pointer to current database
Set db = CurrentDb()
' Open recordset
Set rs = db.OpenRecordset(strQueryName)
Do While Not rs.EOF
' Set email Body
strMessageBody = "Hello " + rs![EMPL_FIRST_NM] + "," + CRLF + CRLF
strMessageBody = strMessageBody + Forms![frmReportRun]![Email Body] +
CRLF + CRLF
strMessageBody = strMessageBody + "Thank You," + CRLF + CRLF
strMessageBody = strMessageBody + Forms![frmReportRun]![HR
Contact].Column(1) + CRLF
strMessageBody = strMessageBody + "Human Resources"
[Forms]![frmHidden].[ctlHidden] = rs![POSN_NBR]
' rs![EMPLT_EMAIL_ADDR] "email@live"
DoCmd.SendObject acSendReport, "rptDivisionTransactions", acFormatPDF,
"(e-mail address removed)", , , Forms![frmReportRun]![Email Subject], strMessageBody,
False, False
' TEST PRINT DoCmd.OpenReport "rptDivisionTransactions", acViewNormal
DoCmd.OpenQuery ("qryDivisionTransactionsRptForReps")
rs.MoveNext
Loop
' Clean Up
rs.Close
db.Close
DoCmd.SendObject acSendReport, "rptDivisionTransactionsForReps",
acFormatPDF, "(e-mail address removed)", , , "Rep Reports", , False, False
Forms![frmReportRun]![Last Run] = Date
DoCmd.SetWarnings (WarningsOn)
' Suspend UClick so Outlook security is functional
UClickSuspend
Exit_DivTransEmail:
Exit Function
Err_DivTransEmail:
MsgBox Err.Description
Resume Exit_DivTransEmail:
End Function