multiple email body problem

[BarryMK]

I found some code that sends emails to multiple addresses stored in a table in
http://www.access-programmers.co.uk/forums/showthread.php?t=66121&highlight=email+multiple that I've tested and it works.

On adding it to my existing working code (coloured blue) to input fields into the strBody and a subject line in strSubject I get Runtime Error 2467 The expression you entered refers to an object that is closed or doesn't exist.

The code is breaking on the line 'strBody = "You have received a new " & Me!cmbType.Column(1) & " raised by customer " & Me!cmbSal.Column(1) & " " & Me!txtLastName & ". This is Compliments and Complaints database record number " & Me!txtCompID & ". The customer's comments were: " & Me!memComments & ". Please action" '


Private Sub cmdClose_Click()

If MsgBox("Have you selected the record type and filled in the other yellow fields? These are mandatory. If you've missed any - click on 'No' to return to form", vbQuestion + vbYesNo, "Close Form?") = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdClose
DoCmd.SetWarnings True
End If

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strEmail As String
Dim strBody As String
Dim strSubject As String

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "tblPlannersEmails", cn

With rs
Do While Not .EOF
strEmail = strEmail & .Fields("Mailadd") & ";"
.MoveNext
Loop
.Close
End With

strSubject = "New feedback from a customer at SomewhereBranch"
strBody = "You have received a new " & Me!cmbType.Column(1) & " raised by customer " & Me!cmbSal.Column(1) & " " & Me!txtLastName & ". This is Compliments and Complaints database record number " & Me!txtCompID & ". The customer's comments were: " & Me!memComments & ". Please action"
DoCmd.RunCommand acCmdSaveRecord

strEmail = Left(strEmail, Len(strEmail) - 1)
DoCmd.SendObject , , , strEmail, , , strSubject, strBody, True


DoCmd.OpenForm "frmSwitchboard"
DoCmd.Close acForm, "frmSelectNew"
End Sub

 

[bonekrusher]

Its telling you that you are refferring to an object that is not there. Check to make sure all your objects are there

 

[BarryMK]

I got that much from the message! Yup, as far as I can tell nothing is missing but I'll trawl through again.
Cheers

 

[BarryMK]

Solved it myself!

I took the working section that inputs data into the email body and selected the various email addresses from hard code. This will do for now (quick fix) and I'm sure I'll be able to link the select statement to the table at a later date.

Dim strEmail As String
Dim strBody As String, strSubject As String

Select Case txtDeptName.Value

Case "Environmental Services"
strEmail = "BK@here.com; dDH@t5here.co"

Case "Managing Director"
strEmail = "JB@once.com; bHH@TH5US.CO"
End Select


strSubject = "New feedback from a customer at Somewhere."

strBody = "You have received a new " & Me!cmbType.Column(1) & " raised by customer " & Me!cmbSal.Column(1) & " " & Me!txtLastName & ". This is Compliments and Complaints database record number " & Me!txtCompID & ". The customer's comments were: " & Me!memComments & ". Please action"

DoCmd.RunCommand acCmdSaveRecord


DoCmd.SendObject , , , strEmail, , , strSubject, strBody, False


DoCmd.OpenForm "frmSwitchboard"
DoCmd.Close acForm, "frmSelectNew"