Make MacBook Pro announce emails when sleeping
No - sleep precludes the making of sounds. 

You can set the Mac to prevent sleep and instead just put the screen to sleep but anything that runs on the CPU will need to machine to exit from sleep anytime a mail arrives in order to produce the sound you wish to be made.