Immediately what comes to mind for me is the The Someguy Series for Fallout New Vegas. It's a bundle of voiced quest mods that mesh well with the base game. There's also House of Horrors for Fallout New Vegas that I recall being pretty good. I played it probably about eight years ago so I don't recall much about it though.
I'd also be curious to hear people's thoughts on content targeting a specific theme. For example mods that are good with horror or comedy. They might not fit in well with the base game but they can be fun.