need the ability to email, not mail, end of the year benefit

For users or potential users.
Post Reply
rajghuman
Posts: 3
Joined: Tue May 31, 2016 6:25 pm

need the ability to email, not mail, end of the year benefit

Post by rajghuman » Mon Oct 23, 2017 2:57 pm

right now the treatment finder allows us to print and mail out "End of the year benefits"/"Use it or loose it" letter. It would be nice if we can email to all of our patients this letter, rather than having to waste paper, time and stamps and more time or lost mail.

Raj Ghuman

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: need the ability to email, not mail, end of the year ben

Post by jsalmon » Mon Oct 23, 2017 3:37 pm

Vote / pledge / help promote request #2291
http://www.opendental.com/manual/featurerequests.html
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: need the ability to email, not mail, end of the year ben

Post by rhaber123 » Mon Oct 23, 2017 3:53 pm

Code: Select all

  
/*356 List of Patients that have not been in since a specific date that have insurance benefits remaining.  
Includes Insurance Carrier's Name, Patient's Name, Three Phone Numbers, Benefits Remaining,
TP Remaining and Last seen date. Assumes everyone has calendar year benefits*/
SET @FromDate='2017-12-28';
SELECT p.LName, 
p.FName,
p.HmPhone,
p.WkPhone,
p.WirelessPhone,
p.Email,
DATE_FORMAT(lastseen.LastSeen,'%m/%d/%Y')AS LastSeen,
annualmax.AnnualMax '$AnnualMax_',
used.AmtUsed '$AmountUsed_',
annualmax.AnnualMax-COALESCE(used.AmtUsed,0) '$AmtRemaining_',
c.CarrierName
FROM patient p
INNER JOIN patplan ON p.PatNum=patplan.PatNum
INNER JOIN inssub ON inssub.InsSubNum=patplan.InsSubNum
INNER JOIN insplan ip ON ip.PlanNum=inssub.PlanNum
INNER JOIN carrier c ON c.CarrierNum=ip.CarrierNum
INNER JOIN (
 SELECT benefit.PlanNum, 
 MAX(benefit.MonetaryAmt) AS AnnualMax
 FROM benefit
 LEFT JOIN covcat ON covcat.CovCatNum = benefit.CovCatNum
 WHERE benefit.BenefitType = 5 /* limitation */
 AND benefit.TimePeriod = 2 /* calendar year */
 AND (covcat.EbenefitCat=1 OR ISNULL(covcat.EbenefitCat))
 AND benefit.MonetaryAmt > 0 /* (-1) indicates empty */
 GROUP BY benefit.PlanNum
) annualmax ON annualmax.PlanNum=inssub.PlanNum
LEFT JOIN ( 
 SELECT patplan.PatPlanNum,
 SUM(claimproc.InsPayAmt) AS AmtUsed
 FROM claimproc
 INNER JOIN inssub ON claimproc.InsSubNum=inssub.InsSubNum
 INNER JOIN patplan ON inssub.InsSubNum=patplan.InsSubNum 
  AND patplan.PatNum=claimproc.PatNum
 WHERE claimproc.Status IN (1, 3, 4) -- Rec, Adj, Supp.
 AND YEAR(claimproc.ProcDate)=YEAR(CURDATE())
 AND claimproc.InsPayAmt!=0
 GROUP BY patplan.PatPlanNum
) used ON used.PatPlanNum=patplan.PatPlanNum
INNER JOIN (
 SELECT p.PatNum,
 MAX(procdate) AS LastSeen
 FROM patient p
 INNER JOIN procedurelog pl ON pl.PatNum=p.PatNum
 AND pl.ProcStatus=2 /*complete*/
 AND p.PatStatus=0 /*active patient*/
 GROUP BY pl.PatNum
) lastseen ON lastseen.PatNum=p.PatNum AND lastseen.LastSeen<@FromDate
WHERE PatStatus=0
ORDER BY c.CarrierName;

===================================================================================================================================================
1- run the query, export to excel, go through the list and delete what you don't need,
2- keep the Last names, first names, phone numbers, and emails. Save the file as a text file
3- In Google Contacts, create a separate group, and import your text file to this group , ( patients info)
https://www.google.com/contacts/u/0/?cplus=0#contacts
4- Send an email to your group now
5- Don't send more than 200 or 300 emails per hour. you may be penalized by your email provider. check with them before you send your marketing bulk emails
6- You can call any patient that does not have any email
===================================================================================================================================================
We use a special program that extract all the emails, and does all of that automatically, including sending the emails through our personal email account
We use a group email software.

pid_user
Posts: 67
Joined: Thu Jun 04, 2015 9:31 am

Re: need the ability to email, not mail, end of the year ben

Post by pid_user » Fri Dec 08, 2017 10:29 am

Another alternative - Use a plugin from http://www.hrdsq.com. They provide real time insurance verification also - so one can have more updated and accurate info before sending.
It helped us mine more than above 100K remaining on charts with our patients, based on this we changed our recall strategy. Already booked 64K out of it.

Post Reply