cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Forms Submission to Excel to Multiple Emails (CC) from indexed data

Hello,

 

I am looking for a solution where Microsoft Form is generated from an excel with key values. When an individual submits a form, I put formulas in the Excel table to index additional email addresses in the table, so I can use them to CC the multiple emails when automatically responding back to the submitter. 

 

The Excel table updates upon the submission, but I am having trouble getting the latest submission information to get the associated email addresses to send.

 

The steps I have are:

  1. When a new response is submitted
  2. Get response detailed
  3. Get a row
  4. Send an email

 

What steps am I missing to make this work? 

 

Thank you in advance.

1 ACCEPTED SOLUTION

Accepted Solutions

Can you screenshot or rebuild the action Send_an_email_(V2)_2. Sounds like you have an invalid reference in there.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

View solution in original post

19 REPLIES 19

Hi @kevinkhkim 

 

If I understand you correctly you have another column in the table that is dynamic - the email?  Your form populates the excel table directly, ie. no flow involved here.  But then you wish to email them with the response and dynamic value (email). You might have to use get rows with a filter on "ID" to retrieve the row relevant to the response details?  ie ID eq 'ID from response details'

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

@DamoBird365 Thank you for your help! Yes, that is correct that the Form automatically updates the Excel that is updated dynamically with the responses along with formulas to add additional contacts based on Submitter names. I used Get a Row action with ID, but now I'm having a challenge savin the flow with the error message below, so I can't test:

 

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The repetition action(s) 'Apply_to_each' referenced by 'inputs' in action 'Send_an_email_(V2)_2' are not defined in the template.'.'.

Correction - The workflow worked and I am able to retrieve details and send accordingly! I still have the error message which does not prevent me from running the flow but how do I get rid of it without impacting the flow?

 

Can you screenshot or rebuild the action Send_an_email_(V2)_2. Sounds like you have an invalid reference in there.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Recreating the Send an Email flow did the trick. Thank you so much!

I'm not sure why the flow is spotty. The Excel is working to dynamically update email addresses, but CC'ing emails from dynamic content is not working - it worked the first time but has not copied emails over after. Thoughts? @DamoBird365 

Hi @kevinkhkim 

 

You might want to check your expression in cc. Maybe by replacing the other action, you’ve stopped that from working. Just a guess. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

What I think is happening is that

1. Send an email is executing before Excel is fully updated with formulated values to pull the data from the source

2. I need the Excel completely update, then get the row to send an email

 

Is there a function I can put either before or after "Get a Row" to make sure data is secured for Send an Email to pull the data to send?

The issue is that the Send an Email is not reading the email addresses from Dynamic Contents and I'm not sure how to make it read them.

Hi @kevinkhkim 

 

You need to share an image of that action and the history of that action, as it is really hard to visualise what you are seeing with the description you have provided.

 

Damien

@DamoBird365 Please see below and let me know if you need further clarification. Thank you for your help!

kevinkhkim_0-1624456746246.png

I have the Form response feeding into Excel and I am getting a row from there. I put delay in case it can support with Excel to finish running the formulas to refresh before "Send an email" can pull the details from the row.

kevinkhkim_1-1624456810230.png

Where I am challenged is the CC part which grabs the email from the Excel as Dynamic Contents. I put semicolons as directed, but did not work. When I actually put the email addresses, then it works.

 

 

Hi @kevinkhkim 

 

The dynamic email you are using is from the original form submission?  Did you not say that the email came from Excel?  Is it a column from the result of get a row?

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

@DamoBird365 That's correct that as Forms are submitted, Email and Name populate in Excel and the green highlighted columns are formulated based on the submitter's "Email" column. The dynamic email I am using for the To is from the original submission. CC is derived from new calculated columns in Excel.

kevinkhkim_0-1624457528987.png

 

Hi @kevinkhkim 

 

My bad, it is the cc that is causing problems, not the to.  Have you tried looking at the history output of the get a row?  Do you see the emails in there?  If not, your 10 second delay maybe needs to be above the get a row action.  It is possible for you to also try office scripts if your get a row action is not able to read the dynamic values from the table.

 

Check the history and see if you can see those emails first.

 

Damien

@DamoBird365 Moving the Delay above Get a Row did not work. Now that reviewing history, it doesn't look like the values are there for appropriate attributes... 😞 I'm not sure what triggers it to work only sometimes...

kevinkhkim_0-1624459406302.png

 

 

I found out from another post that Power Automate cannot read formulated cells from Excel.

Hi @kevinkhkim 

 

Sorry about that one.  I have tried it on my environment and it works, albeit I, like you, am having mixed results.  I have tried writing a basic Office Script to do the same, it works too, albeit it needs to be thrashed to see if it falls over.  

 

Here's the sample script

 

function main(workbook: ExcelScript.Workbook, 
	rowguid: string ) 
{
	let selectedSheet = workbook.getActiveWorksheet();

	let findme = selectedSheet.getRange("A1").find(rowguid, { completeMatch: false, matchCase: false, searchDirection: ExcelScript.SearchDirection.forward });

let row = findme.getRowIndex()+1; //row index starts from 0
let cell = 'E'+row; //my email is in colum E
let email = selectedSheet.getRange(cell).getValue() //get email value

	return(email) //return email to Action
}

 

and the result is based on a lookup expression.

 

DamoBird365_1-1624475766422.png

 

Here's the result:

 

DamoBird365_0-1624475480916.png

 

 

Another option for you, if your email lookup isn't huge, you can define an array in your flow with the email addresses.  Then you can take the field you are looking up from excel and perform a filter on the array in your flow.  Something a bit like https://www.damobird365.com/if-elseif-else-in-power-automate-simplified/

 

Although if you define the list of emails as a table in excel, you could equally do get rows and perform a filter on that instead.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

 

 

@DamoBird365 I'm sorry, I'm not quite following... 

hi @kevinkhkim 

Which bit do you need explaining and I’ll try to help.

 

Damien

 

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (2,123)