cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
csegovia
Frequent Visitor

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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
csegovia
Frequent Visitor

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

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

image.png

 

Receipt Contents After Patch

image.png

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:

image.png

 

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

 

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

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

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

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
csegovia
Frequent Visitor

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

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

image.png

 

Receipt Contents After Patch

image.png

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:

image.png

 

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

 

View solution in original post

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (9,804)