Field does not exist: MsgBodyText in subform as value for SendObject method

[Abiart]

Hi guys,

I've made leaps and bounds with my current little project today, but there's a frustrating error which is keeping me from testing my procedure.

I have a form which contains 2 controls: a button "btnMailIt", and a subform, "qryMessagesMerge subform".

MsgBodyText is a text field in the subform.

I have some code to send report snapshots in a loop to names in a list. I decided to have all the info for the SendObject method in this subform for simplicity's sake, thus other subform fields include MsgSubject and EmailAddress. I wasn't sure it was possible to have merged fields within the MessageBody, so for each record, there is a separate MessageBody record customised to suit the recipient.

My problem is this: when the code tries to establish the MessageBody field, it produces an error. Below is the code up to and including this error. I know a version of the code worked previously, when the message details (To, Subj, Body etc) were stored in a separate table, so I wondered if it was an issue with subforms? I'm suspicious, though, because the code opens the subform in a new window anyway.

Also, and this might be a way of diagnosing more effectively, I'd like to implement more detailed error reporting. You'll see that currently all error numbers produce the same message: MsgBox "ERROR: Message body text field " + MsgBodyTextField + " does not exist or is empty." I want to put in lines e.g.

If Err.Number = 1 Then 'do this
If Err.Number = 2 Then 'do that etc.

But where can I find a list of error numbers and messages for SendObject?

Thanks for your patience!

-Abby

Option Compare Database
Option Explicit
      

Const MsgBodyTextField As String = "MsgBodyText"    ' Name of form field in message table containing message text
Const MsgSubjectField As String = "MsgSubject"      ' Name of form field in message table containing message subject
Const RecordsFormName As String = "qryMessagesMerge subform" ' Name of form that displays table of records which will be used in mail merge
Const EmailAddressField As String = "EmailAddress"  ' Name of table field in mail merge record table that contains email addresses

' Name of error log file. 
Const ErrorLogFile As String = "S:\Sales\MDB\Prospects Email Merge Logs\MailMergeErrorLog.txt"

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Const ETYPE As Integer = 0              ' Index of element in MsgElem array that indicates element type - field name or chunk of literal text
Const ETEXT As Integer = 1              ' Index of other element in MsgElem array. Contains actual field name or chunk of literal text

Const FIELDNAME As Boolean = True       ' Code goes in ETYPE element to indicate text represents a field name
Const MESSAGECHUNK As Boolean = False   ' Code goes in ETYPE element to indicate text is literal message text

Const FAILURE As Boolean = False        ' General failure return code
Const SUCCESS As Boolean = True         ' General success return code

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Code executed when you click the Mail It button
Private Sub btnMailIt_Click()

Dim MsgElem() As Variant
Dim strMsgToSend As String, _
    strT As String
Dim rst As Object
Dim fld As Field
Dim obj As AccessObject
Dim ctl As Control
Dim bFoundForm As Boolean, _
    bWasClosed As Boolean, _
    bSendError As Boolean, _
    bHadErrors As Boolean
        
    ' Ensure form data is in sync with underlying table
    Me.Refresh
        
    'Ensure the form that contains the mail merge records exists. Open it if necessary
    bFoundForm = False
    bWasClosed = False
    
    For Each obj In Application.CurrentProject.AllForms
        If obj.Name = RecordsFormName Then
            bFoundForm = True
            If obj.IsLoaded = False Then
                DoCmd.OpenForm obj.Name, acFormDS
                bWasClosed = True
                DoEvents
                Exit For
            End If
        End If
    Next

    If Not bFoundForm Then
        MsgBox "Cannot find form " + RecordsFormName + ". Aborting"
        Exit Sub
    End If
        
    ' Ensure all required fields exist
    On Error Resume Next
    
    ' Message body text field. Content is further validated in GetMessageText function
    strT = Me.Controls(MsgBodyTextField)
    If Err.Number <> 0 Then
        MsgBox "ERROR: Message body text field " + MsgBodyTextField + " does not exist or is empty."
        If bWasClosed Then DoCmd.Close acForm, obj.Name, acSaveNo
        Exit Sub
    End If

 

[gemma-the-husky]

strT = Me.Controls(MsgBodyTextField) is probably the culprit

either strT=MsgBodyTextField or
strt = me.controls("MsgBodyTextField").value (perhaps value may not be necessary)

but strt = me.controls(MsgBodyTextField) is ttrying to find a numeric equivalent for MsgBodyTextField ie me.controls(2) say to reference the control

 

[Abiart]

Thanks for the tip, Gemma. That error (and subsequent similar issues) has been resolved.

I'm now getting a different error, with the same field. The code runs ok up to the SendObject command. Then code fails and a line is written to the logfile which says there is a missing or empty field, and in the place of the contents of the Message Body field in the log, there is simply the field name, MsgBodyText. It looks like this:

==================================================
Error in record 1 Email address: rick@domain.com - MISSING OR EMPTY MAIL MERGE FIELD(S)?
MsgBodyText

I can't see where the problem lies - I expect it's something like the last problem, but I'm just not well experienced enough to spot it

The procedure in full was too big for the post, but I have attached it as a text file. The area I'm having problems with is:

        ' Assemble message from boilerplate text and merge data in current record
        strMsgToSend = ""
        If AssembleMessage(strMsgToSend, MsgElem, rst) = FAILURE Then
            bSendError = True
        Else    ' If no error, attempt to send it

            DoCmd.SendObject ObjectType:=acSendReport, _
                             ObjectName:="Distributors Summary Merge", _
                             outputformat:="SnapshotFormat(*.snp)", _
                             To:=rst(EmailAddressField), _
                             subject:=Me.Controls(MsgSubjectField), _
                             MessageText:=strMsgToSend, _
                             EditMessage:=False
        
            If Err.Number <> 0 Then ' SendObject raised an error
                bSendError = True
                Err.Clear
            End If
        End If
        
        If bSendError Then ' Write a line to the log file
            Print #1, String$(50, "=")
            Print #1, "Error in record" + Str(rst.AbsolutePosition + 1) + vbTab + "Email address: ";
            
            If IsNull(rst(EmailAddressField)) Then
                Print #1, "EMPTY FIELD"
            Else
[COLOR="Red"]                Print #1, rst(EmailAddressField) + " - MISSING OR EMPTY MAIL MERGE FIELD(S)?"
                Print #1, strMsgToSend[/COLOR]
            End If
            
            bSendError = False
            bHadErrors = True
        End If

Any suggestions would be gratefully received!

Thanks again,

Abby

 

[gemma-the-husky]

the syntax of rst references should be

To:=rst!EmailAddressField

not brackets as you used

 

[Abiart]

The syntax: rst!EmailAddressField doesn't work for me: in debugging mode if I hover over EmailAddressField using this syntax, the bubble text says:

rst!EmailAddressField = <not found in this collection>.

Whereas if I use rst(EmailAddressField), the bubble text says:

rst(EmailAddressField) = "EmailAddress"

I've tried setting it to the former, but I still get the same error and I don't think it's related as it's not the EmailAddressField I'm having trouble with.

 

[gemma-the-husky]

depends what rst is . if its a recordset, then rst() syntax definitely seems wrong. take it out of the send object parameters and see what happens.

i meant to say before, set edit message = true, then it should show you the e-mail before it sends it, so you can see if there are any missing params

 

[Abiart]

I regret to say that I've tried both of those things and it's made no difference whatsoever.

Setting the EditMessage property to "True" doesn't result in the New Message being opened for editing. I believe that the message is put together by the Assemble Message function, so the failure of the New Message window to open for editing means the error could be anywhere up to this point, and not just up to the SendObject command.

The reason I know that the MsgBodyText field is not found is because if the code worked properly but, say there was a problem with the email client and the message was not sent, the logfile would show the body of the email for each message the could not be sent. Since the error log has "MsgBodyText" in the place of the body of the message, it would seem that this text could not be found.