I'll second Red Alert and Shogun Total War -- both had some excellent scenes.

As a Star Trek aficionado, I would also tip my hat to Birth of the Federation and Klingon Academy. The intro movie for BOTF had both excellent music & narration and felt very atmospheric, and the campaign intro movies for each of the 5 playable races were fairly well-done as well. As for KA, I have just five words for you: David Werner and Christopher Plummer. Nothing more needs to be said there.