Hi Kris,
YOU CAN DO IT!!! I have complete faith in you. Weights are wonderful things. Besides the fact that an increased muscle mass will burn more calories at rest, lifting weights and increased strength have numerous documented positive health effects. Lifting and strength training can increase bone mass and bone density, which can help prevent problems as we get older, plus I just feel darn good when my roommates ask me to open that jar or help lift something heavy. Though sure, sometimes I'm tired or don't want to work out, I always feel better when I'm done - weights or cardio (sounds like you already know that about cardio!).
It sounds like you're intimidated to have other people see you do the weights. I've also felt that way, as I am quite shy by nature. I like to practice a new move on my own first - either find a video on the internet, study it/mock do it at my desk or wherever I am, then give it a try when the gym is not as crowded. Could you go to the gym at a different time when there are less people there, even just a few times, to start your weight lifting/machine use? I feel more confident doing it in front of a crowded gym after I've done it a few times on my own.
You don't necessarily have to hire a personal trainer. At my gym, it is just fine to walk up at the desk and ask the trainer to show you a few moves. The big, hulking, intimidating-looking guy is actually really nice and helpful.
Another thing I've done when I had friends who went to the gym with me - we made a pact to try out the free weights together. Do you have anyone like that?
After a few ventures into the weights section, I learned that the world would not come to a screeching halt, people weren't going to stare at me, mock me, and I got more confidence to lift when people were around. Most people are at the gym to do exactly what you are doing, get in a good workout, and will be perfectly happy and polite to see you there as well.
Good luck!!!