The tight end is the jack of all trades of an American football offense. He is expected to block like an offensive lineman and run passing routes like a wide receiver.
Of course, no tight end is quite as fast as a wideout or quite as strong as a guard. But he can do both pretty well, which makes life difficult for the defense, because on any given play it's hard to guess what exactly the tight end will do.
The tight end gets its name not from his snug spandex pants but from its position on the line of scrimmage. A tight end will usually start a play right next to one of the offensive tackles in a three-point stance. In this way he is an "end" because he's on the end of the offensive line, but he is a "tight end" because he is snug against the blockers. His opposite is the "split end," which is a glorified name for a wide receiver.
Here's a lovely diagram. Defense is on the top.
LB LB LB
CB DE DT DT DE CB
SE OT OG C OG OT TE
FS = free safety | SS = strong safety | CB = cornerback
LB = linebacker | DE = defensive end | DT = defensive tackle
* = football, to be snapped by the center
OT = offensive tackle | OG = offensive guard | C = center
SE = split end | TE = tight end | Q = quarterback | FL = flanker | RB = running back | FB = fullback
If you split an offensive into a right side and a left side, you'll see that in this diagram the tight end is on the right side. This is known as the strong side, because the offense has more blockers here if they choose to use a running play; the other half is the weak side. Oftentimes one of the LB's will cheat towards the tight end and may even go on the line of scrimmage.
On the other hand, the offense could use a passing play, in which case the tight end will either run a pattern or stay behind to help block. (Likewise, the running back and fullback can either run patterns or block.) If the tight end goes out for a passing play, he'll be covered by a linebacker or perhaps the strong safety.
The best tight ends are fast enough to out-run the linebackers and strong enough to block a defensive end. Tony Gonzalez, Shannon Sharpe, and Jeremy Shockey are three current NFL tight ends who are pretty good blockers and excellent receivers. Sharpe is the safest bet to make the Pro Football Hall of Fame; the people he would join are, in alphabetical order: Dave Casper, Todd Christiansen, Mike Ditka, John Mackey, Ozzie Newsome, Charlie Sanders, Jackie Smith, and Kellen Winslow. Newsome and Winslow were the best receivers of the lot; the best all-around tight end was probably the gigantic Mackey. Of course, I wouldn't say that to Ditka's face ...
Thanks to discofever for reminding me of Sharpe's existence.