I still remember getting goosebumps from Baldur's Gate 2's menu theme back in the day. It really set the mood and made me excited to start.
A more recent main menu theme that impressed me was Hellpoint's. Together with the menu showing that black hole - it is just wonderfully ominous.
I have a rather silly one.
Back in the old Youtube days, I think before Google took over. There was a fake parody trailer made for David Lynch's Dune. It basically reimagined the film with fart jokes. As immature as it was, it was still funny. But now it's long gone and I wish I could show it to my friends.