In short: no, it will suspend the method from the moment it reaches the yield statement. The remainder is executed in case you ask for the next element.
Given you write result = tmp_csv_file() nothing is done: so not even tmp_path='tmp.csv' is executed.
Now if you call next(result), Python will start to evaluate the function until it hits the first yield statement. So it executes tmp_path = 'tmp.csv', open(..)s the file and __enter__s the environment. It hits the yield statement and thus returns the csv_file. Now you can do whatever you want with that file. The file will remain open (as long as you do not close() it explicitly), and __exit__ will not be called.
If you however call next(result) a second time, Python will continue its quest to find the next yield statement. It will thus __exit__ the with environment, and remove the file (os.remove(tmp_path)). Then it hits the end of the method. This means we are done. And thus next(..) will throw an error that the iterable is exhausted.