I used to smoke cannabis pretty much every day for the last 2 years. I didn't feel that it affected my mood or motivation as such, I still managed to hold my job, and do it very well, keep my band going and get everything done I needed to get done, but I did notice my short term memory had become affected and I was becoming less social. Not drastically, I wasn't a hermit or anything, but if I had the choice to go for a drink with the guys after work or go home and get high, more often than not I'd opt to go home.
I would say I developed an addiction to it, my excuse to myself was that it helped my sleep, and honestly, I did have trouble sleeping before then and I always sleep really well after a joint or two.
In relation to my sex life, I'd say when I smoke weed it makes me horny. Some of the best orgasms I've had have been when I've been home alone and had a play with my toys after a smoke. Sex while slightly stoned is fun for me, I've never had a problem getting it up while in that state.
I have since cut right down on the weed front. It's an expensive habit when you smoke every day! While I still enjoy a smoke every now and again I feel I am no longer dependent on it.
I have also tried other drugs, mainly cocaine, mcat, mdma. They always make my evening more memorable and eventful but I hate the comedown. I only ever decide to do them after I've started drinking on a night out, everything seems like a good idea when you're drunk doesn't it? haha. Pills I've done also, but every time I've tried them I've thrown them up before the effects really kicked in, so I just stopped bothering with them.
In my opinion, I think cannabis should be legalised, as mentioned before, the pros outweigh the cons, and when used in moderation, I think it can be acceptable. I don't feel I have enough experience using or knowledge of other drugs to make an educated comment on them.