Mac OS X Lion users can change the sound for new message for their Mail App using the Preferences menu. Click the Mail menu and select Preferences from the menu list.
Navigate to New messages sound section under the General tab.
Pick your desired sound for the new messages using the Sound drop down.
If the sound you are looking for is not listed in the drop down then using Add/Remove you can pick the sound from your Mac. Also if you do not want to have any sound when a new message arrives in Mail app then select None from the drop down list.