I use Snappy. That's my preferred photo app of all! So, please, keep this in mind.
Ideally I want to be able to sync to picasa and dropbox. But I wouldn't mind if it was flickr, picposterous or whatever, as long as it's really automatic and seamless, unlike this guy's solution. I've tried many apps and couldn't find a single one (apparently unlike this guy, which is the reason why I started this question).
Dropbox app like few others do let you take photos from inside the app and submit the photo. But that's not even a seamless sync - if there's no internet connection the photo won't be sent as soon as internet becomes available unless the app stays open.
Maybe a good solution would be just an app able to monitor the DCIM folder and send any new ones through e-mail as soon as it sees it. Then we could use pixelpipe, instapaper, sendtodropbox or anything! That way the iOS already handles sending e-mails exactly the way we expect - it will go through as soon as a connection is available.
For now, I'm using Picbox - it softens the whole manual process at least... But that's still not it!