Check out my latest screencast on T4 Templates:
Make sure you also check out Damien Guard's LINQ To SQL T4 Templates for a downloadable example.
Although I think T4 Templates are cool, I think the killer use of them are in the various software factories by Microsoft Patterns & Practices as well as other DSL Tools by Microsoft. The guidance packages in the Smart Client Software Factory, Web Client Software Factory, Repository Factory, Web Service Software Factory, etc. all use T4 Templates for code generation within Visual Studio. Download the source code for the guidance packages and check out the templates.