A very good example is the Oblivion modding community, never have i seen such an amount of superb mods. I think it would be very good for the game.
I agree, the only reason I'm playing Oblivion on my PC is because a modder created an app., called Oldblivion, that allows the game to play on older computers(reducing shaders, removing bloom & dynamic lighting, etc.)
Mods can allow for even greater sales and publicity, but if you get the wrong, immature modders that create inappropriate mods

, it can really ruin the games publicity.
I'm sort of on the fence with this subject, also because I'm a console gamer.