Here's a couple that stand out:
John Rudy (known as PCC John R back then) used a hip hop song to create a great atmosphere in the intro for The Crawling Chaos. I want to say Gangsta's Paradise, but I don't recall for sure.
And the module Amway Attacks, by Brad Foster, played the Village People's "YMCA" to the author's own lyrics, in a song sung by Popeye(!). It was a memorable scene that led into the climactic battle.
Oh, and one of Ray Dyer's Realm mods opened up with "Song 2" from Blur. That choice stuck in my mind for some reason.
That's all I can think of right now.