Go to Profile > Advanced... > select the profile you want to change > click menu > select edit > select your mailbox (messages[email]) > change Tune to your choosing.
if you are a holster person, then I would highly recommend getting one designed for BB as well (magnet in the bottom puts it into standy by mode). You will find that you can customize ring type, tone, volume, email notifications, etc. for "in holster" versus "out of holster" profiles. One of the greatest things I love about my BB - never have to worry about turning it off before a meeting or something. Since I always have my BB in my holster at work, it's always on vibrate and I've never had to be embarrassed over a phone ringing at the wrong time.