Stop Hard-Coding Recipients: Streamlining Email Automation with Dataverse and Power Automate for a U.S.-Based Window and Door Manufacturer

Summary

A window and door manufacturing company based in the United States, specializing in energy-efficient fenestration products, eliminated brittle hard-coded email recipients from their sales automation by adopting a Dataverse-driven approach in Power Automate.

CloudFronts implemented a dynamic recipient resolution pattern using coalesce and createArray expressions, pulling the To and CC parties directly from opportunity record lookups in Microsoft Dynamics 365 CRM.

The solution handles null lookups gracefully, scales as team structures change, and requires zero flow edits when personnel or roles shift.

Business impact: Reduced flow maintenance overhead, eliminated misdirected emails caused by stale hard-coded addresses, and established a reusable pattern applicable across multiple automation scenarios.

The customer is a U.S.-based manufacturer of custom steel windows and doors, serving commercial, residential, and architectural projects. Established in the mid-1980s, the company specializes in high-performance, energy-efficient fenestration systems designed for both modern and heritage applications.

They rely on Microsoft Dynamics 365 CRM to manage their sales pipeline, opportunity tracking, and customer communications across a distributed sales network.

Their sales process involves multiple stakeholders per opportunity, including an opportunity owner, primary customer contact, forwarding representative, and regional sales representative, all of whom may need to be included in outbound communications at different stages of the deal lifecycle.

When the organization first automated opportunity-related emails through Power Automate, recipient addresses were defined statically inside the flow. A specific mailbox was hard-coded as the CC address, and To recipients were manually entered per scenario.

This approach worked initially but quickly became a source of ongoing problems:

1. Stale recipients: When team members changed roles or left the organization, flows continued sending emails to incorrect or inactive addresses, requiring a developer to open the flow and update it manually every time.
2. No relationship to CRM data: The recipient list in the flow had no connection to who was actually assigned to the opportunity in Dynamics 365 CRM. The two could easily fall out of sync.
3. Scalability and maintenance burden: As the number of automated flows grew, so did the number of places where email addresses were hard-coded. A single personnel change could require updates across multiple flows, increasing both effort and the risk of missing one.
4. Inability to handle variable stakeholders: Not every opportunity has the same set of involved parties. Some have a forwarding representative, others do not. Some have a dedicated sales representative assigned, while others rely only on the owner. A static recipient list cannot handle this variability.

The organization needed a recipient model that was driven entirely by what was recorded in CRM, not by what a developer had typed into a flow months earlier.

CloudFronts redesigned the email automation to resolve all recipients dynamically at runtime, using lookup field values from the opportunity record in Dataverse. No email addresses are stored in the flow itself.

Technologies Used

1. Microsoft Dynamics 365 CRM, Source of opportunity data, ownership, and stakeholder relationships
2. Power Automate, Orchestration layer for the email automation
3. Dataverse connector, Real-time retrieval of opportunity record and related lookup fields
4. Email activity (CRM), Target entity for structured email creation with party list support

What CloudFronts Configured

The flow fetches the opportunity record from Dataverse as its first action after the trigger. From that single record, four lookup fields are evaluated, the record owner (_ownerid_value), the opportunity contact (_cf_opportunitycontact_value), a forwarding sales representative (_ow_forwardingtosalesrep_value), and the primary sales representative (_ow_salesrep_value).

Each lookup is conditionally included in the recipient array only if it is not null. If a lookup field has no value on a given opportunity, it is excluded entirely, the flow does not error, and no placeholder address fills the gap.

The recipient array is constructed using a single coalesce + createArray expression, producing a clean party list that is passed directly into the email activity creation step. The participationtypemask value distinguishes the To recipient (mask 1, the owner via systemusers) from CC recipients (mask 2, contacts).

The diagram above illustrates the end-to-end structure of the flow. Below is a breakdown of each stage.

Step 1, Trigger
The flow is triggered by a CRM event such as an opportunity stage change, a manual button, or a scheduled recurrence.

Step 2, Get opportunity record
A Dataverse action retrieves the full opportunity record including all lookup fields.

Step 3, Build the recipients array
This is the core of the solution:

coalesce(
  createArray(
    if(
      not(equals(outputs('Get_Opportunity_Record')?['body/_ownerid_value'], null)),
      json(concat(
        '{"participationtypemask": 1,"partyid@odata.bind": "systemusers(',
        outputs('Get_Opportunity_Record')?['body/_ownerid_value'],
        ')"}'
      )),
      null
    ),
    if(
      not(equals(outputs('Get_Opportunity_Record')?['body/_cf_opportunitycontact_value'], null)),
      json(concat(
        '{"participationtypemask": 2,"partyid@odata.bind": "contacts(',
        outputs('Get_Opportunity_Record')?['body/_cf_opportunitycontact_value'],
        ')"}'
      )),
      null
    ),
    if(
      not(equals(outputs('Get_Opportunity_Record')?['body/_cf_forwardingtosalesrep_value'], null)),
      json(concat(
        '{"participationtypemask": 2,"partyid@odata.bind": "contacts(',
        outputs('Get_Opportunity_Record')?['body/_cf_forwardingtosalesrep_value'],
        ')"}'
      )),
      null
    ),
    if(
      not(equals(outputs('Get_Opportunity_Record')?['body/_cf_salesrep_value'], null)),
      json(concat(
        '{"participationtypemask": 2,"partyid@odata.bind": "contacts(',
        outputs('Get_Opportunity_Record')?['body/_cf_salesrep_value'],
        ')"}'
      )),
      null
    )
  )
)

Each lookup is checked for null and included only when present, producing a clean, variable-length recipient list from CRM data.

Step 4, Null checks per lookup
Missing stakeholders are simply excluded without breaking the flow.

Step 5, Create email activity
The recipient list is passed into Dataverse email activity creation.

Step 6, Email sent
Recipients are resolved dynamically from CRM data at runtime.

The organization now operates email automation where the flow itself never needs to be edited when team structures shift. Updating the opportunity record in CRM is the single source of truth, and the flow responds accordingly at runtime.

What if all lookup fields are null on an opportunity?
The createArray expression will produce an array of null values, and coalesce will return an empty or minimal array. It is recommended to add a condition step before the email creation to check that at least one valid recipient exists and to handle the empty case, such as logging a CRM note or notifying an administrator, rather than attempting to send an email with no recipients.

Can this pattern be extended to BCC recipients?
Yes. BCC corresponds to participationtypemask value 3 in the Dataverse email activity party schema. Adding a BCC entry follows the same pattern as any other lookup, wrap the field check in an if block and include the appropriate mask value.

Does this work if the contact lookup points to a lead rather than a contact?
The OData binding entity type must match the actual record type. If a lookup can point to either a contact or a lead, the expression needs to be extended to check the entity type of the lookup value and construct the appropriate binding, contacts(…) vs leads(…), accordingly.

Can the same pattern be used for other CRM email scenarios?
Yes. The coalesce + createArray + conditional binding pattern is reusable across any Power Automate flow that sends email via Dataverse. It is particularly well-suited for quote notifications, case assignments, and renewal reminders, anywhere the recipient list varies by record.

Hard-coding email recipients in Power Automate flows is a form of technical debt that compounds quietly. It works until a team member leaves, a role changes, or a new stakeholder is added, and then it fails in ways that are easy to miss and slow to fix.

By resolving recipients dynamically from Dataverse lookup fields, this implementation ties email automation directly to the data that the business already maintains in CRM. The flow becomes self-maintaining for recipient changes, and the organization’s investment in keeping CRM data accurate pays dividends across every automated communication.

The expression pattern is concise, composable, and reusable. Once understood, it becomes the default approach, not the exception.