How Do I Stop Code if No Records in Query?

[froggiebeckie]

This code runs a query and sends an email based on the records returned in the query, (thanks to a lot of help from the folks here).

Public Function SendReminder3()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim Attach As String
Set fso = New FileSystemObject

' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
' Set up the database and query connections
Set db = CurrentDb()
[COLOR="Red"][B][I]Set MailList = db.OpenRecordset("MyEmailAddresses3")[/I][/B][/COLOR]
' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it
MyMail.To = MailList("email")
' This adds addresses to Copy
MyMail.CC = MailList("Copy To")
'This gives it a subject
MyMail.Subject = MailList("subject") & " Due"
'This gives it the body
Do Until MailList.EOF
MyMail.Body = MyMail.Body & vbCrLf & MailList("equipment") & " - " & MailList("Freq") & "  Day " & MailList("subject") & " -  Due " & MailList("DueDate")
'This does the attachment
Attach = MailList("Attachment File Path")
MyMail.Attachments.Add Attach


'This sends it!
'MyMail.Send
'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.
MyMail.Display
'And on to the next one...
MailList.MoveNext
Loop

'Cleanup after ourselves
Set MyMail = Nothing
'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.
'MyOutlook.Quit
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function

Actualy, there are several different codes to run different queries to cover different issues.
Only problem I'm having is when there are no records returned. If the query returns no records, the specific code glitches out and stops the whole process.

Is there a way to check for query results BEFORE starting the code to make the emails, and then end the code if there are no records returned?

As always, thanks for taking the time to try to help.

BeckieO

 

[Rabbie]

If you open the query as a recordset you can check the recordcount property to see if there are any records returned. If it is Zero then just exit.

 

[froggiebeckie]

PHOOIE!!
I accidently left out this line:

Set MailList = db.OpenRecordset("MyEmailAddresses3")

I've edited the original post to include it.

Doesn't this mean I've opened the query as a recordset?

 

[boblarson]

Doesn't this mean I've opened the query as a recordset?

Yep, and you should be able to say

If MailList.Count = 0 Then
  Msgbox "Nothing to send"
  Exit Function
End If

Or whatever you want to do if no records are there.

 

[froggiebeckie]

Thanks, boblarson.
I'll play around with that. I should be able to get it, now.

Again,

Thanks for taking the time.

BeckieO

 

[froggiebeckie]

That did the trick.
I did have to change ".count" to ".RecordCount", but that worked great.
Thanks again.

BeckieO