As far as I can tell, this isn't available in Mail.app, but a workaround is possible using AppleScript using the basic text-to-speech script.
- Make the sound file in AppleScript:
on run
Save and run this AppleScript to create the sound file.
set theText to "You've got mail from yo momma."
say theText using "Victoria" saving to "YoMomma.aiff"
end run
- Put the resulting file in ~/Library/Sounds.
- In Mail -> Preferences -> Rules, Add or Edit the Rule you wish to add the sound to.
- Add an Action of "Play Sound," and select "Add/Remove" at the bottom of the list. Your new sound sound shows up. Highlight it and click "Done."

