My latest project was designed using Adobe / Macromedia Fireworks CS3, then I coded the templates / CSS by hand using a text editor SKedit. I then took them into Sharepoint Designer to create the masterpage templates.

Previously before SP Designer, I just sent to the development team and they came back with the results.

Overall I'm a believer that you can create "spiffy" sites, as you put it, without the need of a fancy design tool like Dreamweaver or Frontpage, depending upon the platform.

I find Fireworks the best at creating web graphics and Photoshop overkill, and not designed well for my workflow.