The Lazy Newb Pack is about as easy as it gets for using the mods / various graphics included with it. However, if someone wants to create a mod, but doesn't want to learn how to use a computer a little more than normal, they are being quite unrealistic in their expectations. The steam release looks like it's going to be amazing as is, unmodded. So the need for casual style gamers to add mods, with no interest in learning how to copy and paste, would hardly ever arise.
I too want as many people as possible to enjoy Dwarf Fortress at least as much as I do. And I agree that some would be put off by the current steps required to add a mod. I just don't think it's going to be a statistically significant number of people who fall into that category. But it certainly could be.
I suppose given that the steam release is going to have graphics, and more music in addition to just being a fun game in general; the group most likely to be adversely affected by adding mods on top of this will be the modders themselves who want their mods to get more exposure with the steam player base.
I suggest you hang out at some other forums for a while. The myth that dwarf fortress requires mods to be playable is still highly prevailant (however wrong). The opinion that Steam Workshop will therefore make Dwarf Fortress a playable game and therefore something people want to buy is being spouted at a regular rate.
You can't just ignore that, say tough luck, learn to love copy-paste, anyone who can't shouldn't play my game, and then expect to make money from releasing on Steam (the whole point).
Steam Workshop is happening. Would be mad for it to not. But it only becomes a problem if people go exclusively Steam workshop to release their mods (besides premium tileset adaptations which will have to be locked).
If the place to learn about modding is here, the wiki and Reddit and this is made known and mentioned regularly, and if the forums continue to welcome new modders, this wont ever become a problem as people will already be part of the community.
Makes no sense to block your mod off from half the community. Except if you don't know it's there.