Salesforce Email Notification to Customer

About the company: Dwelo is bringing smart device technology to apartment communities. Our software, hardware, and service platform enables the owners and managers of multifamily properties to offer smart homes as an amenity to their residents and simultaneously improves the efficiency of property operations. Dwelo smart apartments are live in thousands of apartments from coast to coast. We are a growing team making a lot happen, we are taking on some big names, and while we’re leaders in the space, we’re just getting started. Why do we need this project done? We have a team of field technicians that installs the smart hardware in various apartment communities across the US. The work they do is tracked in Salesforce. We work directly with the staff that manages each apartment community to schedule new installations in their apartment units that have recently become vacant. Prior to the arrival of a Dwelo technician, the apartment staff needs to be ready and available to give them access to the units in order to complete the installation. However, the apartment staff has lots of things they are juggling and they often forget about our scheduled work and are not prepared for our tech when they arrive which either causes a delay or results in a reschedule. We want to do all we can to help remind our communities when we have a technician coming and it's not realistic to do this manually. We'd like Salesforce to email the apartment staff 72 hours and 24 hours before any scheduled work. We have all the data we need to automate this but have no in-house Salesforce developer that can build what we need. Project scope: We have three types of custom objects that will need to be used for this email trigger. The "Community" object has the main details of the apartment community while the "Work Ticket" object as the details of the specific work that is being performed by a tech at a particular apartment community on a particular date. The "Unit" object shows the details of the specific apartment units at the community. Work Tickets are associated to the Community object when they are created. Units are associated to both the Community and the Work Ticket objects. We only want communities with the field "Community Type" equal to "Retrofit" to be included in this email trigger. From there we also only want to include Work Tickets that have the "type" picklist field equal to "Install-Full". After filtering out the Communities and Work Tickets using the above logic we want the email to trigger if the "Scheduled Date" of the Work Ticket is 3 days in the future and then again if it's 1 day in the future. The email should be sent to the contact listed in the "POC" field on the community record. The all caps items in the below template are the related Community and Work Ticket fields we would need to have pulled into the email. Email template: Hello, We wanted to remind you that a Dwelo technician will be at your community on "SCHEDULED DATE" to install the Dwelo system in the unit(s) below. Please make sure we have permission to enter the units and that each unit is ready for the full installation of Dwelo. If you have any questions or need to reschedule please contact your Community Success Manager. RELATED LIST OF UNITS ON THE WORK TICKET Timeline: We'd like to have this done in the next 2-3 weeks if possible.