Search code examples
listsharepointpowerappspower-automate

Building an email householding email function in PowerApps

Would appreciate guidance on how to proceed with a householding email function in PowerApps/Automate. (householding is a specific mailing industry term)

I have a PowerApps tool that collects scan codes in the field (literally, we are using a QR code scanner and scanning boxes like crazy). For example, "RITM1234567" is a scan code.

Each scan code is associated with a Customer. For example, "RITM1234567" belongs to, say, "Edward". And of course, "Edward" maps out to Edward's email address (for example "[email protected]")

Each scan code is also associated with a description, for example "banana."

In my list of scan codes, there are many scans (can be anywhere from 1-200 or more scan codes in one pass). And the ones that are going to Edward are anywhere from 0 to all of them. But I only want to send Edward a SINGLE EMAIL (householding), something like this:

Hey Edward, the following items are ready for you to eat:

RITM1234567 - banana
RITM7654321 - banana
RITM7162534 - grape

Bring a fork and come on over!

So, those associations I mentioned, those are part of a Sharepoint List, something like:

[RITM VALUE] [CUSTOMER NAME] [CUSTOMER EMAIL]       [ITEM DESCRIPTION]
RITM1234567  Edward          [email protected]  banana
RITM7654321  Edward          [email protected]  banana
RITM7162534  Edward          [email protected]  banana

And if there are scans for other people, then I want to look them up in the same SharePoint List and send them an email as well.

So if a collection of scans were 10 items: 5 for Edward, 3 for Pat, and 2 for Kit

Then it should send THREE emails, not ten.

How might you start approaching that problem, given that PowerApps seems to have some weird limitations (no arrays or other cardinal way to access data)? (see footnote)

I admit it's a little high-level, so even high-level suggestions may be useful. And I can generally follow pseudocode.

Thank you kindly,

Edward

  1. Thought: is it possible to construct a Collection/Data Table by looking at a list of scan values and saying “if this value appears in column x of a reference list, then add a new item to the Collection consisting of these other things in the same record on that reference list”…?

-=-=-=-=-

Footnote: I had previously solved this in an Excel-VBA app by creating a new worksheet with all the individual entries, sorted by Customer name, and then de-duplicating starting from the bottom. Then I did a lot of concatenation in a big loop to create each email's subject/body info. But I'm not sure how I would do that here. The Excel VBA app has lasted a couple years, but I am tasked to replace it with something that is more broad across our environment (thus a Powerapps Canvas app). Would Data Tables be a structure that lends itself to this?

Solution

  • Okay, so the data prep part (where I build the main Collection that feeds the email routine), I completed and the answer to that is located here:

    PowerApps - One Collection feeds another, with List lookups

    -=-=-=-=-

    The emailing part I completed and this is my example of that (some content redacted):

    // Current test email
Set(TestEmailAddress,"[email protected]");

// Appropriate subject for FIRST CONTACT email
Set(TestEmailSubject,"IMPORTANT - Your Equipment is Ready to Deliver");

ClearCollect(Mailings,{CustomerName: "Edward Martin III",CustomerEmail: "[email protected]",ManagerName: "Edward's Boss",ManagerEmail: "[email protected]",ItemListHTML: "<li><strong>REQ1791779 - RITM2037855 - Microsoft Surface Dock 2</strong></li><li><strong>REQ1791779 - RITM2037856 - Mouse - Wireless</strong></li><li><strong>REQ1791779 - RITM2037854 - Microsoft Surface laptop 4 15in</strong></li>"}, {CustomerName: "Joe Customer",CustomerEmail: "[email protected]",ManagerName: "Bosso Joe",ManagerEmail: "[email protected]",ItemListHTML: "<li><strong>REQ1791780 - RITM2037855 - Microsoft Electric Banana</strong></li><li><strong>REQ1791780 - RITM2037856 - Banana - Wireless</strong></li><li><strong>REQ1791780 - RITM2037854 - Microsoft Electric Banana Holder</strong></li>"});
//ForAll(Mailings,
//Office365Outlook.SendEmailV2(CustomerEmail,TestEmailSubject,
//  "<p>Dear " & CustomerName & ",</p>"
//  & "<p><em>Great news!</em> Your equipment has arrived and is ready for deployment from "
//  & DD_Location.SelectedText.Value & " (hours of operation: " & LocationHours & ").</p>"
//  & "<p>Items in your order include the following:</p>"
//  & "<ul>" & ItemListHTML & "</ul>"
//  & "<p>Thank you so much for your time. Let's complete this order.</p>"
//  & "<p>Sincerely,</p>"
//  & "<p>" & User().FullName &"</p>",
//  {Importance:"High",ishtml:"true",From: User().Email,ReplyTo: User().Email});
//);

    I am calling this done and done.

    Onward!