I've always looked down upon camera phones and haven't really found a good use for them. I have changed that stance slightly. I've been experimenting with the iPhone 4 camera for the last few weeks and I have to say I am a believer. No, it isn't going to replace my big honking SLR anytime soon but for some simple stuff it is actually useful. One particular use I've found very intriguing is to use it to shoot landscapes particularly the sky and the skyline. It does a decent job of capturing detail that you can easily pull up in post-processing -- see example above. However, I haven't had much luck indoors or with close-ups of people or objects. I haven't really figured out how to focus properly.
However, what is really exciting is the potential that lies ahead. Camera phones have been popular only for a few years now. I see point-and-shoots, both still and movie, almost completely disappear over the next 10 years or so in favor of phone cameras.