Can anyone help me with this effect? I am curious as to how it can be re-created. I would like to use it on some other artwork. Or something similar.

http://www.kamloopsembroidery.com/careffect.jpg

It seems to be HDR - you can get tons of tutorials in here on that, simply search "hdr"

Looks HDR but with a LucisArt filter applied afterwards to bring out that contrasty somewhat painting look. Check out these for more examples http://www.flickr.com/groups/lucisart/

Also check out one of my favorite photographers, he gives a lot of examples/tutorials for HDR shots and using various plugins like Lucis. His photos are just incredible, and his thought process behind why he shoots and edits the way he does is one I very much relate to.
http://www.stuckincustoms.com