From the Apple forum:
Message Menu > Preferences > Alerts. Change the top item (which is a drop down) to the action you want. Change Sound in the next item down.
You can use the Application sounds, the System Sounds and any sounds in ~/Library/Sounds These will show up in the list if you have them.
However the Home Folder/Library/Sounds is hidden as the Home folder/Library is hidden Use the Finder > Go Menu > Go to Folder and type in ~/Library/Sounds to see what is in there (or place new sounds in there).