How to send an email with a csv attachment

[dyordanov]

Hi guys, 

I have a question. So I saw there is a thread here that somewhat answers the question but I think Microsoft have stopped the ability to a user to be able to attach a csv o anything in the Office 365 activity. 

So I have a medallion structure. I get my data ,clean it and so on, my last notebook creates a csv of the Sales Report we need for the psat month and saves it as a csv file in the lakehouse. The problem is how to attach this file dynamically to an email in my pipeline which runs the last notebook to create the file on the 1st of each Month and  then send the csv to certain members from my company. Has anyone had any luck with this or any other way ? I've tried sendin the mail from the notebook itself but smtp settings on our tenant doesn't allow me and our IT can't seem to figure it out even after changing the settings. 

All help would be appreaciated, thanks ! 

[dyordanov]

Hi guys,

So finally i managed to do it by creating azure data lake storage and copied the data from the notebook to the storage. Then after I could see the 3 files i need for each country I created a logic app where I use a trigger "When a blob is added or modified" and then I have 3 branches where each then blob catches each file and then outlook should send each file for each counry. However I get 3 countries X 3 emails = total of 9 emails... so how can I get each country to have only 1 file and not 3. Any suggestions ?

[spaceman127]

Hi @dyordanov,

If I were you, I would do the following.
If you created the files using Notebook, they are initially stored in the storage account. Then, in the pipeline (after Notebook), create a lookup activity that lists the files. Transfer this to a variable, which in turn transfers it to a web activity that controls the Logic APP.

I hope that was reasonably clear 🙂

Here an simple Guide.

1. Lookup Activity

- Source: your Blob Storage container.
- Configuration:

   - First row only = false (so that all files are listed).
   - Query or path filter: e.g., only files with a specific prefix or extension (.xlsx).

- Result: JSON array with all files found.

2. Set Variable Activity

 - Transfers the list from the Lookup Activity to a pipeline variable (e.g., fileList).
 - Optional: You can transform the list (e.g., extract names only).

3. Web Activity

 -Target: your Logic App HTTP Trigger.
 - Method: POST.

I haven't tested this in this configuration yet, but I use something similar at our place.

Best regards

Feel free to leave kudos or accept it as a solution. This will also help other community members.

[dyordanov]

Thanks for the answer but since I have it so far isn't there a simpler solution to fix what I have rather than make everything more complicated ? Otherwise thanks for the answer. 

[spaceman127]

I understand :),

another option is to switch to Event Grid in the Logic APP. I've done that before, too.
You simply replace the trigger that triggers the action with When a resource event occurs (Azure Event Grid). I think that's what it should be called.

The advantage is that you only get one event instead of multiple events.

Here is the procedure.

Go to the Storage Account and click Events.

1. Create and configure the event grid accordingly. Important: the event type is Microsoft.Storage.BlobCreated.

2. Change Logic APP.
- Remove the old trigger “When a blob is added or modified”.
- Add a new trigger:
- When a resource event occurs (Azure Event Grid) - 
Configure:

Subscription: your Azure subscription.
Resource Type: Storage Account.
Event Type: Microsoft.Storage.BlobCreated.

I hope that helps.

Best regards

[v-dineshya]

Hi @dyordanov ,  could you please try the proposed solution shared by  @spaceman127  ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[v-dineshya]

Hi @dyordanov ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @spaceman127 , @nielsvdc  and @spencer_sa , Thank you for your prompt responses.

Hi @dyordanov ,  could you please try the proposed solutions shared by  @spaceman127  and @nielsvdc ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[dyordanov]

So thanks for all answers as of now, so the file i am about the send is about 15mb each. After I send it another notebook will calculate things based on the file I sent and the excel is about 50 mb. So would that change any of the workflow ? 

[spaceman127]

Hi @dyordanov,

What @spencer_sa  writes is another good option, or you can use the Logic App variant I described to you. Have you checked it out?

Best regards

[spencer_sa]

One alternative if you're not too set on actually emailing a csv (and is better for large files) is to write the CSV file to an ADLS Gen II container, build an SAS token/link for it, and email that link to your recipient.  You can then apply an expiry date if you need to and all you're emailing is a a URL so won't hit any size limits.
I have tested this and it works exactly as promised - Copy Activity to upload, notebook to generate the SAS token/URL, and an Email activity to send the email.

[spencer_sa]

As others have mentioned, the email pipeline activity doesn't appear to be able to send attachments.
The way we've bypassed this limitation is via a notebook that writes to the SMTP server of our mail system.  Our legacy system uses pretty much the same method, so it was a matter of porting it over.

[nielsvdc]

Hi @dyordanov, currently it is not possible to add an email attachment to the Office 365 Email activity. Question is if it ever will be an option. As Fabric is all about not duplicating saved data, sending an attachment kinda is. So you have some other options.

1. Don't send the file is attachment. Save the file on OneDrive, SharePoint, OneLake or storage account, get the link to the file and share the link in the email.
2. What you already tried, use a notebook to send an email. But then you need to have a service principal to be able to connect to Microsoft Graph and send an email using the Graph API.
3. Create a Power Automate Flow or Azure Logic App with a HTTP request trigger. From the Fabric pipeline use the Web activity with a POST request and pass the file content as part of the request body to be handled by he flow of your choice.

Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others.

[dyordanov]

Thanks @spaceman127  will certainly try it in a bit. I was trying to avoid anything besides the fabric eco system. The Logic App ? what exactly does it make , attach the file and send the email so similar to what Office 365 Email used to have ? 

[spaceman127]

Hi @dyordanov ,

It's understandable that you want to stay in the Fabric ecosystem.

Here's a screenshot of what that might look like, so you can get a better idea of how it works.

In this example, I am sending paginated reports.
But you can add attachments in the O365 send connector.

This is a Logic APP. It basically controls the process.

Best regards

[spaceman127]

Hi @dyordanov ,

sending attachments with Outlook activities is not currently possible.
The Fabric Roadmap shows you what is planned next.

Here the site.

https://roadmap.fabric.microsoft.com/?product=datafactory

Regarding your problem.

This is how I solve the problem at our company.

1. Notebook creates the CSV file.

2. In Azure, there is a Logic App that then sends it. The Logic App has a connector for this and is easy to configure.

3. I then trigger this via Data Pipeline in Fabric. Of course, you can also use a schedule in the Logic App.

For me, this is a valid approach, but there are certainly other ways.

If you have any questions, just let me know.

Feel free to leave kudos or accept it as a solution. This will also help other community members.

Best Regards