Drupal sometime gets a bad name because designers have a hard time theming the site. It is true that you need to know more PHP than a designer ever wants to know, but if you get a good theme implementor, working along side your design, you can get sites look like this: Vintage Faith Church:
My church needed a website that they could updated more easily than flat HTML files through Dreamweaver, and one that would use an in house designer, with custom art an photography donated by the community. They also have a Drupal developer willing to theme and implement the entire site. (Showcase page on DrupalCon, Boston, vote for the designer! (I just implemented it))
As you can see from the screenshots, we wanted to avoid the typical boxy feel of websites, but still make it highly usable for members and guests. The fly out menu provided by Nice Menus helps with the usability, along with some thorough, not hard, just thorough uses of the menu system and blocks (with show/hide properties properly defined).
The theme backgrounds shift as you navigate the site. A simple theming function gathers the context of the page and styles them slightly different as needed.
Besides the theming, there is no custom code on the site. GMap was used to a group locator, CCK and Views of course to create and display different announements, events and group content types. Audio was used to let different musical artists upload some of the custom creations. In hindsight I may have used on of the other Media CCK field formats (like mediafield), but no a big deal. Core Aggregator is used to combine many of the pastor's blogs together and flickr is a great tool to embed our photographer's image captures of gatherings, events, and shelter work.
We also used wymeditor, with IMCE, and a couple Panels (version 1) to round things out.
I can't wait to tackle a Drupal 6 theme. With all the new tools out there for themers and designers, the sky should be the limit on great, non-Drupal looking, Drupal sites (and it doesn't have to be solely for our enterprise customers).