HOW: Gallery workflow to send email with excel output attached

[PeterHlavacs]

Hi,

I have been working on a workflow that would send to specific requestors their share of an excel report.

As of now how my workflow works:

1. Use table tool and group data by requestor email.

2. Use email tool and and insert the Table output in the body.

This way all requestors get their share, however for some people this means an email with over 700 rows, which is obviously not good.

I don't know how to simply attach the Table output as excel attachments - I cannot use a "path" dependent option as this workflow needs to work in the Gallery.

(In General, I don't understand why the render tool or output tool cannot be directly connected to the email tool)

Thank you,

Peter

[Galactica]

Hi,

I am also working on similar WF and I am dealing with almost same issue.

Therefore, I would like to see that render tool or output tool can be directly connected to the email tool in the future.

Thanks,

Selim

[LordNeilLord]

Hey @PeterHlavacs 

In this situation, I will write out an excel file to a temp location (using engine.tempfilepath in a formula tool to build the file path and name) then you can use this same tempfilepath to read the excel sheet back into the email tool as an attachment.

You may encounter read/write errors where the email tool tries to attach the file before it has finished being written to but a simple block until done can solve this.

[PeterHlavacs]

Hi @LordNeilLord 

Could you please expand on the engine.tempfilepath option?

I have about 1000+ outputs coming out of this workflow, because there are so many requestors. The number of requestors also changes dynamically.

Would the engine.tempfilepath work with this many outous? And most importantly, would it work in the Gallery?

Thank you,

Peter

[LordNeilLord]

Hey @PeterHlavacs 

There is a guide on it here: https://help.alteryx.com/current/TempFiles.htm

Think of the tempfile folder in the same way as you would a normal drive on your computer....but the folder only exists for the time that the workflow is running.

If you need to write 1000 outputs, no problem! They will get deleted as soon as the workflow finishes. You can utilise the temp file space anyway you want.

With Gallery, it's perfect for use with the gallery....you don't want to be writing files to you Server drive that get stored forever and take up valuable space, tempfiles get round this.

[afinzerillo · Accepted solution]

What is the formula used to output an Excel file?  Can you provide a sample workflow?

[pdxsvrider]

To output an Excel file:

%temp%\excel_file.xlsx|||Sheet1

To read the Excel file for email:

[Engine.TempFilePath]+[Field1] 

where [Field1] is the name of my file: excel_file.xlsx

Sample workflow attached.

[pdxsvrider]

Updated to include a "Block Until Done" tool per the recommendation of a colleague.

[premumesh]

Hi, I have related problem, i have a workflow in gallery that runs once every day. It pulls data from an SQL, then appends this data to an excel file and then emails it to a specific group. However, on the next run, it does not append to the newly updated data. So, e.g., if i have 100 rows for Mar 20, the next day when it is run, it should have Mar 20 and Mar 21 - 200 rows in one sheet and so on... this is not happening, it always retains Mar 20 and then appends Mar 21, next time replaces 21 with 22 and so on. Is there a command/tool that i should run additionally to save the newly updated excel file in gallery and use for next run, instead of the older version.

[afinzerillo]

I think you would want to read in your Excel and join that data with your SQL pull before writing back out to the same Excel file.  You would want to use a block until done after reading in SQL and the file before emailing.

I've attached a mocked up workflow.