Make File Name be a value from a column

[AllTheQuestions]

I want to automate a file I manually send every Monday with data from prior week.

I need the file path to contain the PERIOD WEEK which is already a column in the data. I keep finding how to do this as a sheet name but I want it to create a new file every week for the corresponding week.

Secondly, is there a way to automate this to email the NEWEST file each Monday? Without having to specify the actual file name, since I want this to change each week?

I have been browsing and can't seem to search the right key words for this. It keeps updating the sheet name, not the file itself.

[binuacs]

@AllTheQuestions one way of doing this, one you updated your output file you can attach the same file in the email tool

[AllTheQuestions]

Thank you! I'm familiar with setting up the email however not if the file name always changes. Goal is for this to be automated every week and send the new file every week. Not sure if this is possible.

[binuacs]

@AllTheQuestions since the file name is based on one of your column value in your input file and if the column value changes then the file name will also change according to that. If not you can append the DateTime Stamp on the file name

[AllTheQuestions · Accepted solution]

I figured out a solution to this so I wanted to update.

I had to create 2 fields, "path" and "attachment".  PW is the period week, and it's a column within the data.  The main issue I was having was that I needed to add the sheet name with this path in order to create the file with ||| but then the attachment didn't want that.

Path is:

"\\corporate folder\network\folder\Scrubbed Data Files\" + [PW] + " Scrubbed Data.xlsx" +"|||"+[PW] 

Attachment is:

"\\corporate folder\network\folder\Scrubbed Data Files\" + [PW] + " Scrubbed Data.xlsx"

Note that the attachment does not contain the sheet information.

This now allows for complete automation of pulling data, renaming it to the period week, outputting the file and emailing the file every week!