As far as viscerally pleasing guns are concerned, a fair few of the guns in HL2 were awesome (shotgun, pulse rifle, assault rifle's secondary fire, crossbow... even the super grav gun was great fun on the grounds that you could grab enemies with it and then take out entire platoons with their corpse), the nuke launcher in ME2 was amazing even if it was generally bloody useless (and the widow... Oh gods the widow), and I'm really running out of memorable guns at this point...
Ah well, the real point is detail and sound design (the latter of which is so often overlooked in favour of the music and graphical design), and some of the best examples of both I've yet seen were RTSs. Specifically Ground Control (which even had the sound of stray rounds wizzing past the camera and the sounds and sights of spent cases being ejected from the autoloaders on the tanks), C&C3 (anyone else remember the awesomeness of a massed Mammoth tank charge from before they emasculated the things?) and just about everything Relic's ever made (Homeworld series (i'll include cataclysm in this), DoW1 and DoW2, and I haven't played the other one or two games).
EDIT: Upon further reflection I realised that othe genres have turned out some doozies as well such as System Shock 2, the first two Dungeon Siege games (I haven't played the third yet, but considering Square Enix was behind it I'm not hopeful), and Half Life I've already mentioned.