I'd like to make my MacBook make a sound when I receive a new email, even when it is sleeping.
Is there any way to do that?
No, appple docs state that no sounds are made in "power nap" mode. Not even the mac equivalent of "find my iphone" 'play sound' will work.
Apple makes it very clear that power nap will not make any sounds and so it doesn't turn on the fans either, and will not work if it's too hot. Power Nap is a "bonus" mode for the sleep state where a limited amount of work will be done, but it's guaranteed not to disturb or even wake near by people.
If you want alerts I guess you will have to stop the computer from going into sleep mode
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.