If you (preferably on a copy) right-/ctrl-click on Mail.app, there's an option to "Show package Contents". In there you find a Contents folder, in that go into Resources, find the annoying sound (No Mail.aiff?) and replace that with another, less annoying, aiff file with the same name...