SendObject and No Email Address

[Tomfernandez1]

Hi All,

I have a Command Button on a Form that when executed will open my Outlook and add the email addresses of records assiciated with an ID Code field. This works great with multiple email addresses when the ID code if filtered. I posted the code behind the Command Button below.

Problem: If one of the filtered records does not have an email address I get the following message "Unknown message recipient(s); the message was not sent."

Is there something in the code I can change to ignore records without an email address?

Your help is much appreciated!!

Tommy




Private Sub EMAIL_DRIVERS_Click()
DoCmd.RefreshRecord
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strEmailAddy As String
Set db = CurrentDb()
'following would be an SQL statement that retrieved the appropriate records. You could also use a query directly

'
strSQL = "SELECT * FROM [KT VANPOOL DRIVER RECORDS] WHERE [ID CODE] = """ & Forms![KITSAP TRANSIT DRIVERS].[ID CODE] & """"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do While Not rs.EOF


strEmailAddy = strEmailAddy & rs![E-MAIL ADDRESS] & ";"
rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing

On Error GoTo Err_cmdOpenEmail_Click
DoCmd.SendObject acSendNoObject, , , strEmailAddy, , , , , True

Exit_cmdOpenEmail_Click:
Exit Sub
Err_cmdOpenEmail_Click:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_cmdOpenEmail_Click
End If

End Sub

[pbaldy]

I'd add to the WHERE clause in your SQL:

"...AND [E-MAIL ADDRESS] Is Not Null"

[Tomfernandez1]

Is this how I would do it:

strSQL = "SELECT * FROM [KT VANPOOL DRIVER RECORDS] WHERE [ID CODE] = """ & Forms![KITSAP TRANSIT DRIVERS].[ID CODE] & """"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do While Not rs.EOF
strEmailAddy = strEmailAddy & rs![E-MAIL ADDRESS] & [E-MAIL ADDRESS] Is Not Null" ";"


Thanks for your help!

Tommy

[pbaldy]

No, I said add to your SQL string:

strSQL = "SELECT * FROM [KT VANPOOL DRIVER RECORDS] WHERE [ID CODE] = '" & Forms![KITSAP TRANSIT DRIVERS].[ID CODE] & "' AND [E-MAIL ADDRESS] Is Not Null"

[Tomfernandez1]

That did it!

Thanks again for the outstanding service you've given me. I owe you an espresso the next time you are in the Seattle area!!

Tommy

[Tomfernandez1]

That did it!

Thanks again for the outstanding [I meant service] you've given me. I owe you an espresso the next time you are in the Seattle area!!

Tommy

[pbaldy]

Happy to help. Might get up there in Feb for a Microsoft thing, so I may take you up on that!