Man, Greg, I know where you're coming from. I messed around w/ it for a couple years, starting and stopping, before I really got back on the bandwagon. I know a few things that helped me, but they were specific to my situation and may or may not have any relevence for you. For me, getting back in shape was more than just getting my gym schedule back. It involved some life-style change as well.
One thing that sapped my motivation was a low-grade depression that I've experienced much of my adult life. Nothing debilitating, but just enough to sap my motivation and hurt my self-esteem. Eventually I got treatement for that and it's made a world of difference. I don't regularly take any medication for it, but initially, just getting "out of the hole" made a huge difference and let me "get started".
Another motivation sapper for me was the time it takes to start seeing results. When I can actually see that I'm making progress, that's all it takes to get my back in the gym for the next workout, but it's hard getting to that point. Doing things to facilitate seeing progress are great motivators. It can be simply tracking your weight (although you can only use it as a baseline, because of the fat/muscle density thing). It can be actually charting your workouts, so you can see the progression of strength gains. It can be daily inspections of your physique in the mirror. Yes it sounds vain, but it makes a difference. Something I've done (an nobody laugh because it sounds extremely vain) was shave my chest. I naturally have a hairy chest and it makes it very difficult to see mucsle gains. Additionally it makes it easy to mislead yourself about your tummy fat 'cause just like it hides muscle definition, the body hair also hides those fat dimples. I'm not suggesting that you do this, but am suggesting that you do things that maximize your awareness of the progress you're making. Go buy yourself some clothes that show off your physique, Not in the "trying too hard" mode, but for example, one of those "clingy" t-shirts that show your chest and arms vs. the big baggy t-shirt we all wear when we're out of shape. Sounds vain, but it makes a difference. Hey I know it's not the most healthy thing so I wouldn't recommend it as a long-term practice, but when you start seeing some progress, go get a sun tan (assuming you're light-skinned) . Seriously. Even a nice physique looks a little sickly when your skin is some pastey yogurt color. Once again, progress is motivating, you just have to take steps to make sure you percieve your progress.
Something else, which I you alluded to in your post, is not working out so hard in the beginning that you injust yourself and have to stop before you even get started. Ease into your workout routine, but do plan on upping the intensity once you get going.
Initially I'd get really bored during the workouts and looked for excuses to stop. Something that really helped there was getting one of those tiny MP3 players. Now when I workout, I kinda lose track of time and "zone out" on whatever I'm listening to, rather than watching the clock. Another advantage of this, is that you tend to be less distracted by those folks that come to the gym to socialize. Not that I'm anti-social, but I don't have time to stand around at the gym and socialize for a couple hours. I go, work out extremely hard, then get back to the rest of my life. Also, it helps if you can go to the gym when it's less croweded. Every minute you have to stand around waiting for a machine or workout station, is another opportunity for that little slackard in all of us to tell you "you've done enough, we can just skip this last one and go home". You want to minimize the opportunities for excuses.
If it helps, keep a picture of a great set of abs on your fridge, so that when you go for that extra dessert, you look at that and have to make a value decision between "abs of steel" and that chocolate cake
The whole thing is synergistic, once you start making progress, you start feeling better about yourself. Once you start feeling better about it, it's that much more motivation to keep it up and get better. I'm still fairly young (37) but I know that it really makes me feel great knowing that I'm in so much better shape than the vast majority of men my age. Not that I get satisfaction out of the failings of others, but when I'm at the beach w/o a shirt and look better than most of the 20 somethings out there, it really makes me feel good (and sexy) and makes me want to keep it up.
Also, remember, that your workout doesn't have to take 3 hours every time. You don't have to be a slave to the gym. I've got mine down to 30 minutes/5 days a week weight training, and 20 minutes cardio a day. That's less than an hour a day. I just make sure that my workouts are intense, so I don't have to spend a ton of time in the gym. Diet also plays a huge roll. You can gain plenty of strengh w/o a diet plan, but if you want that trim, lean look, you have to address your diet. As a side effect, a healthy diet also makes you feel better. A healty, trimming dies doesn't have to be an exercise in self-deprevation. Do some reading about various diets and nutrition.
I've been lucky in that my spouse is extremely fitness concious and keeps herself in tremendous shape. It really helps to have that support from your partner. Just an offhand comment about improvements you're making goes a long way. Also, it helps w/ your diet if your spouse isn't keeping bon-bons, etc. in the fridge. I'm definately not the most self-disciplined person and I love good food. If I kept it in the house, I'd be sorely tempted to eat it. Cooperation from your spouse is a big deal.
I know I've rambled a bit here, but the main theme is that for me at least, seeing results and motivation are synergistic. If I can percieve that I'm making progress it just makes me more motivated which means more progress, etc. The big hump is getting to the point that you actualy start to see progress, so anything that facilitates that is going to really help. The other idea is to try to setup your environment so as to minimze the opportunities for excuses. Most of us, myself included, don't have iron self-discipline, so anything you can do to remove excuses/tempations can only help.
Best of Luck,
JC
ps I hope I don't come off sounding like some sort of fitness fanatic or know-it-all. I'm not, just trying to help if I can.
) there's no way most of us should be doing 5-6 sets of any weight training exercise. Do 2-3 sets max 8-10 reps. If you do more sets or reps than that, you're simply not using enough weight. I get great results w/ just 2 sets of 8 reps for most exercises, although I may do 4 different excercises for a given body part. More variety in your exercises will give you much better results than more repetition. 
