Get Custom Audio or Visual Alerts
You can add your own alert sounds by placing audio files in a certain folder. Before you do that however, you will need to convert your sound file to the AIFF format using iTunes. Open up iTunes and choose Preferences from the iTunes menu. In the Advanced section, click on the Importing tab and choose AIFF encoder from the pop-up menu. Click OK.
If the audio file you want to use is in iTunes, select it and then click on the Advanced menu. In here choose Convert selection to AIFF. If you audio file is somewhere else, hold Option while clicking on the Advanced menu and choose Convert to AIFF. This will bring up a dialog that will allow you to navigate to your audio file. Once the file is done converting, go back to the preferences and change the "Import Using" pop-up menu back to AAC.
Now we have to get this file to show up in the Alerts list in System Preferences, Mail and iCal. Open up your user folder, look in the Library for a folder called Sounds and drag the sound here from iTunes. Your new alert should now be in the list in the Sounds section of System Preferences.
Make your alerts visual
An alternative to the audio alert is a visual alert, which is basically a screen flash. You can turn this on in the Universal Access section of System Preferences, under the Hearing tab. This will flash the screen as well as playing an alert sound. If you want to get rid of the sound and just have the flash, go to the Sounds section of System Preferences and move the Alert Volume Slider all the way to the left.
Annoyingly, this will also mute the feedback you get when the volume is changed and the interface sound effects (like when you move something to the trash). If you're fine with this then use the method above. However if you still want these other sound effects, there is a better (but more complicated) solution.
Mac OS X will also flash the screen if for some reason it is unable to play the selected audio alert. To make this happen, open up TextEdit and press Command-Shift-T to convert the document to plain text. Save this empty file as Flash.aiff in the sounds folder inside your Library. If a dialog asks you which extension to use, choose .aiff.
Now if you look in your alerts list in System Preferences there will be an alert called Flash that does exactly that - flash the screen. This is because Flash.aiff isn't really a sound file. When your Mac tries to play it and can't, it flashes the screen instead to make sure you still see an alert.