Mango introduced the ability to create and add custom ringtones to your phone, but is there a way (programmatic or otherwise) to create a custom tone to be used for a new text message or meeting reminder?
As of now, you can't officially change the notification sounds. You will need to live with what shipped on your phone. What Mango introduced is the ability to have custom ring-tones. You would think they are synonymous, but no dice there.
Now that the official answer is out of the way, it you have a developer-unlocked device, and have it rooted, I believe you can replace the sounds on the phone with new ones. Be warned that this does not add new. It replaces existing. Unless you backed them up, they will be gone forever.
This is finally possible (see UserVoice update here). Just add your audio file (<30k) to the Ringtones folder on your device.
It should now show up under the
Custom section when selecting your new notification sound.