Get rid of error 251 and add a field in the subject line

[Thompyt]

All, I have adapted code gotten off the net to my needs. I am NOT a programmer extraordinaire. What I would like to do is:

A. Get rid of Error 2501 The SendObject was canceled.

I tried: MsgBox Err.Number & " " & (Err.Description<>2501) but run into a Runtime error '13".

B. Add the field CR_Number in the subject line of the message.

I tried strSubject = "GO Change Request Number " & rstChange_Request("CR_Numbers"), I get Error 3265 Item not found in this collection

Private Sub Cmd_Email_GO_CRs_Click()
On Error GoTo Error
Dim db As Database
Dim rstChange_Request As Recordset
Dim strSQL As String
Dim strSubject, strBody, strAddresses As String
Set db = CurrentDb()
strSQL = "SELECT * FROM GO_EMail"
Set rstChange_Request = db.OpenRecordset(strSQL, dbOpenDynaset)
If Not rstChange_Request.EOF Then
strBody = "Action Officers," & vbCrLf & "This is a following on from today's ERB discussion on CR "
strBody = strBody & rstChange_Request("CR_Numbers") & ". If needed, please back-brief your O6 for SA, and let me know if there is any issues or concerns. Please provide your votes to me NLT 1940 today or this will be approved automatically. Also, feel free to give me a call if you have any questions or issues." & vbCrLf & vbCrLf & vbCrLf
strBody = strBody & "Date Issue Identified: " & Chr(9) & Chr(9) & Chr(9) & rstChange_Request("Dates") & vbCrLf
strBody = strBody & "CR Number: " rstChange_Request("CR_Numbers") & vbCrLf
strBody = strBody & "Change Requested: " & Chr(9) & rstChange_Request("Change Requested") & vbCrLf & vbCrLf
strBody = strBody & "Unit & Section: " & Chr(9) & Chr(9) & Chr(9) & rstChange_Request("Units") & vbCrLf
strBody = strBody & "MTOE Para & Bumper Number: " & Chr(9) & rstChange_Request("MTOE Paras") & vbCrLf & vbCrLf


strBody = strBody & "Rationale: " & Chr(9) & rstChange_Request("Rationale") & vbCrLf & vbCrLf & vbCrLf
strBody = strBody & "Notes: " & Chr(9) & rstChange_Request("Notes") & vbCrLf
strBody = strBody & "Action Items: " & Chr(9) & rstChange_Request("Action_Items") & vbCrLf
rstChange_Request.MoveFirst
Do While Not rstChange_Request.EOF
rstChange_Request.MoveNext
Loop
End If
strSubject = "GO Change Request Number"
strAddresses = ""
DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddresses, , , strSubject, strBody, True
Exit Sub
Error:
MsgBox Err.Number & " " & (Err.Description)
Exit Sub
End Sub

[June7]

Error handler works for me - the one in your code, not the one that generates error 13.

So you don't know if references to other fields of rstChange_Request work?

Not sure it will make a difference, but SELECT sql normally terminates with semi-colon.
strSQL = "SELECT * FROM GO_EMail;"

See nothing wrong with method of referencing fields, but this should also work:
rstChange_Request!CR_Numbers

If you want to explicitly declare each variable type, then must be like:
Dim strSubject As String, strBody As String, strAddresses As String

[Thompyt]

June
I'll add the colon, and try your references. I think the issue was it being outside the loop.
Thanks

I still get Error 3021 No Records found. when I use this code:

strSubject = "Action Officer Change Request Number " & rstChange_Request!CR_Numbers


There error handling is fine. I didn't want the 2501 popping up. I wanted to exclude it from the list.

[Thompyt]

The error handler is fixed.

I used this off the net:
Error_Handler_Exit:
Exit Sub

Error_Handler:
Select Case Err.Number
Case 2501
Err.Clear
Resume Error_Handler_Exit
Case Else
MsgBox "Error No. " & Err.Number & vbCrLf & "Description: " & Err.Description, vbExclamation, "Database Error"
Err.Clear
Resume Error_Handler_Exit
End Select

[Thompyt]

Still wondering about Error 3021 No record found part

[Thompyt]

I moved a Couple of lines to add the STRSubject, strAddress before the Loop & End if statements
rstChange_Request.MoveFirst
strSubject = "CCB Change Request Number " & rstChange_Request("CR_Numbers")
strAddresses = ""
DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddresses, , , strSubject, strBody, True
Do While Not rstChange_Request.EOF
rstChange_Request.MoveNext
Loop
End If