Email first attachment for all items in a gallery connected to a SharePoint list

[csegovia]

Hi all,

I have a gallery looking at a SharePoint list, each item on the SharePoint list has one and only one attachment (I limited the amount of files from PowerApps).

I need to send all the data by email, so I converted the items from my gallery (all columns with data) into a HTML table and into the body of my email, that works ok.

My next challenge is to extract "the first attachment" from each item on the gallery and attach them to the same email.

I found various topics that explain how to get all the attachments from a single item but I'm still struggling to find a way to get "the" single attachment for each item on my gallery and put them on an collection.

The code below is just an attempt to put all the attachment on a collection but It clearly will not work because of the ID = ID condition, it creates a collection that repeates the same attachment as many items as the amount of items in my gallery.

Clear(EmailAttachments);

ForAll(Gallery.AllItems,
Collect(EmailAttachments, {Name: First(LookUp(SharePointList,ID = ID).'{Attachments}').DisplayName,ContentBytes: First(LookUp(SharePointList,ID = ID).'{Attachments}').Value,'@odata.type':""})
)

More than fixing the code above, I will appreciate any other clever ideas to achieve the same result. I have experience with Flow and I'm sure I could do something with Flow, but I'm sure it must be a simpler solution just using PowerApps functions, thanks

[v-monli-msft]

Hi @csegovia ,

Below thread has the similar situation. According to the resolution, it seems that there is no way to send multiple attachments via Outlook connector within PowerApps currently. You could try to achieve it with the workaround posted by Kris:

https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Send-attachments-on-a-sharepoint-lis...

Regards,

Mona

[csegovia · Accepted solution]

Hi @v-monli-msft,

Thanks for your reply, I have actually used the Office365 Outlook connector before to send multiple attachments and it worked ok without the need of using Flow. My case now was a bit different because I was trying to attach on a single email the first file attachemnt from all the items from a gallery, however I found a work around.

The lack of a For statement on PowerApps doesn't allow to navigate through each attachment from each item of a gallery to extract the attachment Name and Content Bytes, the ForAll function is not good enough since you cannot run functions inside the formula i.e. "Set()".

My workaround was to create 2 additional columns on my SharePoint list: Receipt Name (Single Line of Text) and Receipt Content (Multiple Lines of Text). Every time I submit a form with an attachment on it I copy the file name and contents onto my new columns.

The problem with this is that all file contents are saved as Blob urls which cannot be used to reconstruct the file and attach it on an email. So the solution I found for that was to Patch the Receipt Content, immediately after the form is submitted, with the Attachments.Value from the gallery for the item just submitted. PowerApps will read the contents from the gallery and will display them as a local datasource with the full url and contens instead of a Blob url, this convert the file contents into a valid datasource for emailing.

SubmitForm(CCForm);

Refresh('Credit Card Expenses');

If(newEntry,
Set(fileName,First(Last(CCS.AllItems).Attachments).DisplayName); //CCS is the gallery filtered from SharePoint data
Set(fileContent,First(Last(CCS.AllItems).Attachments).Value);,
Set(fileName,DataCardValue5.Text);
Set(fileContent,DataCardValue6.Text);
);


Patch('Credit Card Expenses',
If(newEntry,
Last(Filter('Credit Card Expenses', Employee.DisplayName = User().FullName)),
First(Filter('Credit Card Expenses', ID = selectedId))),
{'Receipt Name': fileName, 'Receipt Content': fileContent});

Back();

Receipt Contents Before Patch

Receipt Contents After Patch

Once all items on my gallery are saved and all Receipt Name and Receipt Content are populated with the data from the first and only attachment on each item I put them all together on a Collection and email them, these are the contents of my Email button.

ForAll(CCS.AllItems, //CCS is the gallery filtered from SharePoint data
Collect(EmailAttachments, {
    Name: 'Receipt Name',
    ContentBytes: 'Receipt Content',
    '@odata.type':""})
);

Office365.SendEmail(User().Email,
"Action Required (Approve) - " & User().FullName & " - Credit Card Summary " & ddMonth.SelectedText.mon & " " & ddYear.SelectedText.Value,
HtmlText.HtmlText,
{IsHtml: true,
Attachments: EmailAttachments});

This is the resulting email with attachments:

Of course this solution only works if there's one single attachment on each SharePoint item, which in my case is true because the users will upload only one receipt per item. To construct the body of my email I just created a HTML table populated with the data currently filtered on my gallery. 

Let me know if any of the above doesn't make sense, happy to help if someone is having similar issues.

Cristian