But a link to the CSV file does not appear in the 'Notifications'.
Resolution
When exporting more than 500 records, there is an internal job that runs and so if user attempting to export does not have Alert set for Reports and Jobs under Notifications, notification will not appear.
To correct this, follow below steps:
Log into the Classic UI, go to Home->Account Settings
Click on Notifications tab
Ensure Alert is checked for Report and Jobs
Additional Information
In addition, if the job does not complete due to BG related issues, then there is a possibility that the notification does not get displayed.