I don't think it's possible on an application by application basis. There is a registry key that defines the sound file to be played, at:

HKEY_USERS\DEFAULT\AppEvents\Schemes\Apps\.Default\SystemNotification\.Current

By default it's %SystemRoot%\media\Windows XP Balloon.wav

You could modify that but it would switch the 'Pop' sound off for all System Notifications (not just your app)