That might be a feature with the new iOS5, but currently, unless you use a third party app, you are tied to what is on the phone.
I have gmail, so I use "push for gmail" app. (not to be confused for push gmail, which hasn't been supported or worked for over a year now.) Push for Gmail uses notifications to let you know when new mail arrives and has different tones to use. It also can be set to have a "quiet time" so that you aren't woken by emails in the middle of the night.
Btw, since the app uses regular notifications, it saves a lot on battery life compared to the native push services, or even settings the account to check every so many minutes.