do until not giving me individual results

[Ruegen]

I have made a send object loop through every record however for some reason they are giving me the same subject instead of an individual one

each record should get a different subject according to their name

eventually I will make each email go to each schools email - but I want to get this working correctly first (I don't want to email one school 2000 emails)


I have

If IsNull(Me.txtMessageBody) Or IsNull(Me.txtMessageBody) Or IsNull(Me.Text38) Then
MsgBox "Please ensure that you have the type of school, year, show and email message filled, once they have been selected as the minimum requirment for this form to work you can then email."
Else










Dim attch1 As String
Dim attch2 As String
Dim messagebody As String
Dim emsubject As String
Dim emailcont As String
Dim thecount As String
Dim mresponse As Integer
Dim naofsc As String




Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone


rs.MoveFirst
rs.MoveLast
thecount = rs.RecordCount




messagebody = Me.txtMessageBody
emsubject = Me.txtSubject


If thecount <= 0 Then
Set rs = Nothing
Exit Sub
End If


mresponse = MsgBox("Are you sure you want to email " & thecount & " contacts?", vbYesNo, "Continue")


If mresponse = vbYes Then




rs.MoveFirst
Do Until rs.EOF


naofsc = me.SchoolName


DoCmd.SendObject , , , "email@email.com.au", , , emsubject & " " & naofsc, messagebody, no


rs.MoveNext
Loop
MsgBox "all done"


Else
MsgBox "You have cancelled emailing"
End If
rs.Close
Set rs = Nothing










End If

What have I gotten wrong?

[ItsMe]

You need to assign your string variables the value of the current record in the DAO recordset, not the current record of the Object for the Class Module. The Me. is a shortcut for the Object that owns the Class Module you are in. It replaces something like Forms!frmName

So maybe something like
rs![SchoolName]

You Set rs to = the DAO recordset. In this case, it is a recordset clone. Reference the recordset clone using what you declared. rs not Me or Forms!frmName. You are looping through the recordset clone not the form's recordset. The form's recordset is not moving.

[Ruegen]

I just figured that one out myself before I could answer you - much office joy

everything works, apologies

I am going to reference the school email this way too

so rs!SchoolEmail


er I get error when I do naofsc = rs!SchoolEmail

could it be because it's asking to put an email address in the subject field (I just want to test this first)

[ItsMe]

You need to do a lot within the loop. Get all of your string variables in there and also Open and close your report. Test it out with a short list before you go after the schools. You could test it by opening and closing the reports with a msgbox before closing. The msgbox will cause the loop to pause. You can look at the report and make sure it is the correct criteria.

[Ruegen]

You need to do a lot within the loop. Get all of your string variables in there and also Open and close your report. Test it out with a short list before you go after the schools. You could test it by opening and closing the reports with a msgbox before closing. The msgbox will cause the loop to pause. You can look at the report and make sure it is the correct criteria.

It dies when I get to a null email field

so I tried naofsc = Nz(rs!SchoolEmail, "email@address.com.au")

and it works however I would like not one email sent if the field is null (sort of skip the record)

any recommendations?

[ItsMe]

Don't include Null fields in your recordset.

Put the Is Null criteria in your query for the form, if it is a recordset clone. Or filter out Nulls. The recordset clone will look at filtered results in the form. For instance, if the user filters out user ID's that equal 12 and then creates the recordset clone, User Id 12 will not be in the clone.

[Ruegen]

Don't include Null fields in your recordset.

Put the Is Null criteria in your query for the form, if it is a recordset clone. Or filter out Nulls. The recordset clone will look at filtered results in the form. For instance, if the user filters out user ID's that equal 12 and then creates the recordset clone, User Id 12 will not be in the clone.

hmmm... I guess I will have to do that.


I would have to filter the form before the email schools button was pressed - to ensure the user filters out the nulls in the field.

The control changes to a different email field depending on what value in the group box I have (pictured)

I am going to make an if statement that swaps the string for the checked value from the group

so if value = 1 then

emailstring = rs!LibraryEmail
elseif
value = 2 then

emailstring = rs!Dramaemail

end if

I don't quite know if I should do this before the dountil or inside of it - I was thinking outside the loop/above or does it need to be in each step?

[ItsMe]

I would do it before. You can copy any existing filter into a string. Then you can concatenate any existing filter that may be there to your additional criteria, to include Is Null. Remove filter, have filter = new concatenated string, then filter = new string, filter = true.

Then Recordset clone

[Ruegen]

I would do it before. You can copy any existing filter into a string. Then you can concatenate any existing filter that may be there to your additional criteria, to include Is Null. Remove filter, have filter = new concatenated string, then filter = new string, filter = true.

Then Recordset clone

that is what I thought however filtering depending on the chosen field

Me.Filter = "[Area] Like '*" & Me.Text36 & "*'" & IIf(Not IsNull(Me.Text38), " and SchoolTypeID=" & Me.Text38, "") & IIf(Not IsNull(Me.txtStates), " and StateID=" & Me.txtStates, "")
Me.FilterOn = True

I would have to add the control so that no matter what the control is when changed by the option group - the filter changes to it.

not sure how to concatenate that - I was thinking of adding & not isnull(me.txtSchoolEmail) however tacking on the end I know won't work...

[ItsMe]

Seems like you need to check one more field for null. Wasn't it he Email address field or something. Between nz and Isnull, I am sure you can get something to work. I need to get some sleep. One last thing though. You might want to check if there is an existing filter before applying your own programaticaly. Maybe

If me.filterOn = true then

strWhere = Me.filter

end if.

Then concatenate the existing filter (strWhere) to the other criteria you need to add. Just to avoid issues if the User is trying to use intrinsic filter controls.

[Ruegen]

Seems like you need to check one more field for null. Wasn't it he Email address field or something. Between nz and Isnull, I am sure you can get something to work. I need to get some sleep. One last thing though. You might want to check if there is an existing filter before applying your own programaticaly. Maybe

If me.filterOn = true then

strWhere = Me.filter

end if.

Then concatenate the existing filter (strWhere) to the other criteria you need to add. Just to avoid issues if the User is trying to use intrinsic filter controls.

I made a dim clickemailchange as string on form load

then when you click the options the clickemailchange gets the new field

clickemailchange = "SchoolEmail"

(I even have a little label that shows me what the dim is by getting the dim to change the label text just to test)

now I can filter the clickemailchange null or not null except I don't know how to concatenate to filter the form according to what the dim is representing

[ItsMe]

If all you have left is to validate the Email field within your recordset, you could validate within your loop. Just enclose the task of sending hte email within the validation. Here I use the function I showed you the other day to test for both Null and empty string. Also, if you only want to email to a specific domain, you can use the InStr function to make sure you are not emailing outside the domain.

 Do Until rs.EOF
    If Not isNothing(rs![EmailAddress]) Then
    
        strEMail = rs![EmailAddress]
        
            If InStr(2, naofsc, "@SchoolDomain.com") Then 'Check and see if a valid school address
                DoCmd.SendObject , , , "email@email.com.au", , , emsubject & " " & naofsc, messagebody, no
            End If 'Valid school address
    
    End If 'Check for Email Address
rs.MoveNext
Loop

However, it seems you still may need to validate other fields such as SchoolName. You could add another If Then statement and use the isNothing Function. I would add it as the first check.

Something Like

Do Until rs.EOF
    If Not isNothing(rs![SchoolName]) Then 'Check for School Name
    
        naofsc = rs![SchoolName]
        
        If Not isNothing(rs![EmailAddress]) Then 'Check for Email Address
        
            strEmail = rs![EmailAddress]
            
                If InStr(2, naofsc, "@SchoolDomain.com") Then 'Check and see if a valid school address
                    DoCmd.SendObject , , , "email@email.com.au", , , emsubject & " " & naofsc, messagebody, no
                End If 'Valid school address
        
        End If 'Check for Email Address
    
    End If 'Check for School Name

rs.MoveNext
Loop

If it was me, I would assign the recordsource of the form at the load event of the form using an SQL string. I would declare two string variables at the top of the module below the haeader.

Dim strSQL as String
dim strWhere as String

In the load event of the form I would assign the recordsource via the SQL string. You can base it off of a saved query.

strWhere = "FieldPK > 0"

strSQL = "SELECT MyQry.* " & _
             "FROM MyQry " & _
             "WHERE " & strWhere & _
             "ORDER BY [FieldPK];"

Me.RecordSource = strSQL

Now I can adjust strWhere at anytime I like.
strWhere = (strWhere & " AND ") & "[SomeField] = '" & Me.txtField.Value & "'"

Now that strWhere is adjusted, I can adjust strSQL

strSQL = "SELECT MyQry.* " & _
             "FROM MyQry " & _
             "WHERE " & strWhere & _
             "ORDER BY [FieldPK];"

Now I can avoid touching the form and jusat open up a DAO recordset based on the SQL string I created.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

[Ruegen]

If all you have left is to validate the Email field within your recordset, you could validate within your loop. Just enclose the task of sending hte email within the validation. Here I use the function I showed you the other day to test for both Null and empty string. Also, if you only want to email to a specific domain, you can use the InStr function to make sure you are not emailing outside the domain.

 Do Until rs.EOF
    If Not isNothing(rs![EmailAddress]) Then
    
        strEMail = rs![EmailAddress]
        
            If InStr(2, naofsc, "@SchoolDomain.com") Then 'Check and see if a valid school address
                DoCmd.SendObject , , , "email@email.com.au", , , emsubject & " " & naofsc, messagebody, no
            End If 'Valid school address
    
    End If 'Check for Email Address
rs.MoveNext
Loop

However, it seems you still may need to validate other fields such as SchoolName. You could add another If Then statement and use the isNothing Function. I would add it as the first check.

Something Like

Do Until rs.EOF
    If Not isNothing(rs![SchoolName]) Then 'Check for School Name
    
        naofsc = rs![SchoolName]
        
        If Not isNothing(rs![EmailAddress]) Then 'Check for Email Address
        
            strEmail = rs![EmailAddress]
            
                If InStr(2, naofsc, "@SchoolDomain.com") Then 'Check and see if a valid school address
                    DoCmd.SendObject , , , "email@email.com.au", , , emsubject & " " & naofsc, messagebody, no
                End If 'Valid school address
        
        End If 'Check for Email Address
    
    End If 'Check for School Name

rs.MoveNext
Loop

If it was me, I would assign the recordsource of the form at the load event of the form using an SQL string. I would declare two string variables at the top of the module below the haeader.

Dim strSQL as String
dim strWhere as String

In the load event of the form I would assign the recordsource via the SQL string. You can base it off of a saved query.

strWhere = "FieldPK > 0"

strSQL = "SELECT MyQry.* " & _
             "FROM MyQry " & _
             "WHERE " & strWhere & _
             "ORDER BY [FieldPK];"

Me.RecordSource = strSQL

Now I can adjust strWhere at anytime I like.
strWhere = (strWhere & " AND ") & "[SomeField] = '" & Me.txtField.Value & "'"

Now that strWhere is adjusted, I can adjust strSQL

strSQL = "SELECT MyQry.* " & _
             "FROM MyQry " & _
             "WHERE " & strWhere & _
             "ORDER BY [FieldPK];"

Now I can avoid touching the form and jusat open up a DAO recordset based on the SQL string I created.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

I will need to check only the email field whether it is there or not and depending on what field I have switched it to.

I was placing:

If Me.Frame63 = 1 Then
    If Not isNothing(rs![1ContactEmail]) Then
    emailofsc = rs![1ContactEmail]
    End If
ElseIf Me.Frame63 = 2 Then
  If Not isNothing(rs![6EnglishEmail]) Then
    emailofsc = rs![6EnglishEmail]
    End If
ElseIf Me.Frame63 = 3 Then
  If Not isNothing(rs![2LibrarianEmail]) Then
    emailofsc = rs![2LibrarianEmail]
    End If
ElseIf Me.Frame63 = 4 Then
  If Not isNothing(rs![4MusicEmail]) Then
    emailofsc = rs![4MusicEmail]
    End If
ElseIf Me.Frame63 = 5 Then
  If Not isNothing(rs![3DramaEmail]) Then
    emailofsc = rs![3DramaEmail]
    End If
ElseIf Me.Frame63 = 6 Then
  If Not isNothing(rs![5WelfareEmail]) Then
    emailofsc = rs![5WelfareEmail]
    End If
ElseIf Me.Frame63 = 7 Then
  If Not isNothing(rs![SchoolEmail]) Then
    emailofsc = rs!SchoolEmail
    End If
End If

at the time I wasn't sure what isnothing was but went with it anyway until I ran it - I haven't declared that function in order to call it.

Where did you have that function typed? I am guessing this is another library I don't have checked?

[ItsMe]

.......I wasn't sure what isnothing was but went with it anyway until I ran it - I haven't declared that function in order to call it.

Where did you have that function typed? I am guessing this is another library I don't have checked?

Check your PM's. I sent it to you a while back. It is a user defined function. I believe it is relevant since the users may be saving records after they cleared a field and may leave empty strings in the EMail field. To test the code you have now, you could use IsNull() until after you get the function into a General Module.

[Ruegen]

Check your PM's. I sent it to you a while back. It is a user defined function. I believe it is relevant since the users may be saving records after they cleared a field and may leave empty strings in the EMail field. To test the code you have now, you could use IsNull() until after you get the function into a General Module.

facepalm for me...