Find Patients Without Email Addresses

If you are trying to increase your digital communication or transition to a paperless patient portal, you need to know which active patients do not have an email address on file. This open dental missing email query allows you to generate a clean list of these patients in seconds so your front desk team can update their contact information during their next visit.

The Query

Copy and paste the following code into your User Query window. This query specifically targets active patients who have no email address entered in their Family Module.

/* Query to find active patients with no email address */
SELECT 
    PatNum AS 'ID', 
    LName AS 'Last Name', 
    FName AS 'First Name', 
    HmPhone AS 'Home Phone', 
    WirelessPhone AS 'Cell Phone'
FROM patient
WHERE PatStatus = 0
AND (Email = '' OR Email IS NULL)
ORDER BY LName, FName;

How to Run This Query

1. In Open Dental, go to Reports in the Main Menu.
2. Click User Query.
3. Paste the query provided above into the large text box.
4. Click Submit Query.
5. The results will display in the grid below, showing you exactly which patients need their email addresses updated.

Understanding the Results

The output grid provides the following columns to help your team identify and contact the patients:

• ID: The unique PatNum assigned to the patient in your system.
• Last Name / First Name: The patient's name for easy identification.
• Home Phone / Cell Phone: These columns are included so your staff can quickly call the patient to request their email address while looking at the list.

How to Customize

You can easily modify this query to narrow down your search.

• Filter by Provider: If you only want to see patients assigned to a specific provider, add a line to the WHERE clause. For example, to see patients for Provider ID 1:
  AND PriProv = 1
• Filter by Clinic: If you have multiple locations and only want to see patients for a specific clinic, add this line:
  AND ClinicNum = 1
  (Replace '1' with your specific ClinicNum found in Setup > Clinics).

Variations

If you want to focus your efforts on patients who have been seen recently, you can filter by their last visit date. This helps you prioritize patients who are currently active in the practice.

/* Active patients with no email who have been seen in the last 2 years */
SELECT 
    p.PatNum, p.LName, p.FName, MAX(pl.ProcDate) AS 'LastVisit'
FROM patient p
LEFT JOIN procedurelog pl ON p.PatNum = pl.PatNum
WHERE p.PatStatus = 0
AND (p.Email = '' OR p.Email IS NULL)
AND pl.ProcStatus = 2
GROUP BY p.PatNum
HAVING MAX(pl.ProcDate) > DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
ORDER BY LastVisit DESC;

Skip the Query — Use DentalCanvas Instead

Don't want to write SQL? DentalCanvas connects to your Open Dental database and shows you this data automatically in a visual dashboard — no queries required.

This article is provided by opendentalsupport.com, an independent community resource. We are not affiliated with Open Dental Software, Inc.