Sending individual Excel files from SharePoint to Individual Contact email addressed from Specific Cons Record

Christine Prete

Hi! The goal is to build a process where I send 25 schools their own donor listings via email as an xlsx attachment to a specific email address type that lives on their school cons record in RE. I have been doing this previously with 25 flows and with email addresses hard coded. It was the quick dirty way to get it done almost 2 years ago. That is not sustainable, and I want to have one email template to use that shares general campaign information. Each school would get that same email message body.

First flow, creates the attachments. This is completed and the files are stored in a SharePoint library.

Second flow is where I am getting stuck and could use your help. The school cons record has a fund relationship of the schools fund, there are 25 funds one for each school. The schools fund is the name of the Excel file created in the first flow. I recently started to use Switches and thought possibly that could be the way to do this, if the fund relationship name is = to XYZ, and the file name is XYZ - attach to email, not positive thinking I would have to create the send email process 25 times, which is not the goal. Goal is - One email to send individual reports to individual schools based off of a fund relationship name and file name that match, and use the cons record emails listed. Is there another posting on this process? or a video someone could suggest? Appreciate your time and help.

-Christine

Alex Wong

@Christine Prete
I don't really see a need for more than 1 flow to do this, even the create attachment part.

First of all, I don't think the “fund relationship" matters here (at least from what your post say).

• If you know specifically the 25 constituent records and always the same, then have a array variable with the 25 constituent records system id
  • Better option for more dynamic-ness is to have a “tag” on the constituent that can be used in filter for list constituent API call, either constituent code or constituent custom fields
• then loop through the list of 25 constituent records
  • if you use a static array variable, you will need to call “get constituent” using the system id to get info on the constituent, otherwise, no need as the list constituent will retrieve info of constituent
  • create the attachment for the constituent in loop
  • save the attachment in sharepoint
  • compose email
    • send to the primary email in constituent record
    • subject and body however you want it, with “varying” info using conditional expression in the body or use string variable
    • attach the file that was created inside the loop (depending on how you created the file, you may need to call “get file” from the sharepoint connector to get file content to use in the email.

Christine Prete

@Alex Wong Thank you for the reply Alex and suggestions. I wanted to clarify the gifts that are building the lists in excel all live on various constituents records, and not specific to the 25 constituent records who will be receiving the list of gifts associated with their schools fund.

Cons ID 55555 - St Jerome School
Email: jdoe@stjerome.edu (Email type = Email)

That email address would be receiving an excel list of gifts from various constituents who have a gift with the fund (S5555). This list would/could have 10 different gifts from 10 different individuals who are not part of the school record.

I am not sure if when I originally posted, that the post read that I was pulling the gift history of the 25 school constituent records. That is not the case.

Alex Wong

@Christine Prete
This isn't too hard to resolve:

I know there is a constituent relationship API call, so maybe from there you get “fund relationship”, not sure

or

you can still do the same thing as you do with flow 1 right now, but instead, take what you do in flow 1, do it inside the apply to each constituent loop. How you do this, depends on how your flow 1 currently “gather” the gifts to put in excel.

Heather McLean

@Christine Prete - Have you made progress here? Do you want to workshop this or share at User Group tomorrow?