It's heresy I know, but look at DrudgeReport.com. It's raw, hand written HTML, it's plain, boring typography, it's something any UX or Graphic designer would scoff at if you handed it across their desk. But it works, and it works well.
Drudge's design has been fairly consistant and it's pretty much exactly what it looks like, it's raw HTML and it has barely been changed since 1997. Lots of mud is slung at it's design, but it's considered by some to be one of the best on the web.
Drudge's design scores great on conventional measures of usability: It's effective, users find stories that they consider interesting and stories are prioritized to indicate "big" news. It's always clear what's a link (underlined) and what's not. It's efficient, there's hardly any interface and a simple click on a headline takes you where you need to go. Arguably it's satisfying, it's a highly ranked site, though I don't have any user satisfaction surveys on hand.
So when can something aesthetically "ugly" give great UX and how? What else does deliver this ugly but beautiful level of effective plainness? Craigslist.com is another example of this, though arguably not as great from a usability standpoint. I don't often see sites like Drudge Report, but it's interesting to consider such an unconventionally minimalistic implementation and where it can be applicable.
Update: I'm not talking about just usability; it's long been known that you can have a super efficient and effective design and people hate it because it looks like crap. I'm talking about designs that look "ugly" but still leave the user with a good or even a great experience, experiences people enjoy for than just the bare functional reasons, but not because of pleasant, trumped up aesthetics either.