invalid procedure call or argument

[Stephanie]

Hi. The fabulous discussion group folks helped me create this code to send an
email to everyone who meets the sql statement requirement, without sending an
attachment. I didn't take into consideration if there is no one in the email
group (unlikely, but still). If the email group is empty, I receive a
run-time error "5": invalid procedure call or argument.
Debug pops me to strTo = Left(strTo, Len(strTo) - 1)

Is there a graceful way to say "no one in that email group", have the user
click "OK" and have the code close without debug or an email with no
recepients? Thanks for your time

Private Sub EmailChapter_Click()
Dim rst As DAO.Recordset
Dim strTo As String
Dim sql As String
Dim db As Database

sql = "SELECT Contacts.EmailName FROM ChapterMembers INNER JOIN " +
"Contacts ON ChapterMembers.ContactID = Contacts.ContactID " + "WHERE
(((ChapterMembers.ChapterID)=" + CStr(Me.FindChapter) + ") AND
((Contacts.EmailName) Is Not Null));"

Set db = CurrentDb
Set rst = db.OpenRecordset(sql)

With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
DoCmd.SendObject acSendNoObject, , , , , strTo, "Email Chapter"
DoCmd.Close acForm, "ChapterParamEmail", acSaveNo

End Sub

 

[Douglas J. Steele]

Rather than simply having

strTo = Left(strTo, Len(strTo) - 1)

use

If Len(strTo) > 0 Then
strTo = Left(strTo, Len(strTo) - 1)
End If

 

[Stephanie]

Perfect! Thanks! Now an email opens up with no addressees. I can live with
that!
Now if you could answer an odd-ball question, I'd be good to go.

When I close the email, I lose Access focus. As soon as the email closes,
my screen shows whatever else besides Access that is open (this time, it was
the discussion group, often it's the "exploring" window). Any way to fix it?
It's kind of annoying.

Thanks,
Stephanie

Rather than simply having

strTo = Left(strTo, Len(strTo) - 1)

use

If Len(strTo) > 0 Then
strTo = Left(strTo, Len(strTo) - 1)
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi. The fabulous discussion group folks helped me create this code to send
an
email to everyone who meets the sql statement requirement, without sending
an
attachment. I didn't take into consideration if there is no one in the
email
group (unlikely, but still). If the email group is empty, I receive a
run-time error "5": invalid procedure call or argument.
Debug pops me to strTo = Left(strTo, Len(strTo) - 1)

Is there a graceful way to say "no one in that email group", have the user
click "OK" and have the code close without debug or an email with no
recepients? Thanks for your time

Private Sub EmailChapter_Click()
Dim rst As DAO.Recordset
Dim strTo As String
Dim sql As String
Dim db As Database

sql = "SELECT Contacts.EmailName FROM ChapterMembers INNER JOIN " +
"Contacts ON ChapterMembers.ContactID = Contacts.ContactID " + "WHERE
(((ChapterMembers.ChapterID)=" + CStr(Me.FindChapter) + ") AND
((Contacts.EmailName) Is Not Null));"

Set db = CurrentDb
Set rst = db.OpenRecordset(sql)

With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
DoCmd.SendObject acSendNoObject, , , , , strTo, "Email Chapter"
DoCmd.Close acForm, "ChapterParamEmail", acSaveNo

End Sub