I think you'll be ok. Most of the Tower of Terror experience isn't scary in a "roller coaster" sense, but is instead really good, creepy theming. It's only the very end of the ride that is physically thrilling, and it's over quickly.
SPOILER WARNING:
You start out moving through the old lobby of the Hollywood Tower Hotel. It looks eerily like its occupants have left abruptly- everything is in place as if someone just got up for a moment and expected to return. You then move into a library, where you watch a film of The Twilight Zone's Rod Sterling telling you about a night long ago where an elevator full of hotel guests disappeared in a lightening strike. You're then invited to take a maintenance service elevator to the upper floors of the hotel. You move through the boiler room of the hotel (line area) to board the elevators (where you're obviously seated and buckled in). On the way up, the elevator makes several stops where you see creepy things. On the last one, the elevator actually moves forward on a track, passing more eerie scenes. You finally end up in the section of the ride where the elevator drops. This part is random, but lasts less than a minute (or at least it feels that quick). Often, you pause at an open window that gives you a great view of the park- this part may make your husband nervous since he's afraid of heights, but it's over very quickly and you're all strapped in, so I've never felt unsafe (and I don't like heights either). Really quickly, it's over and you're back on solid ground.
I think you should try it. It's really well-done theme wise!