OK so my sleep problem is resolved, I cannot pinpoint the fix but since upgrading to 10.6.4, Mail 4.3, Safari 5 and iTunes 9.2 the problem is no longer. Unfortunately I only found this to be fixed by accident as I have been closing the lid on the laptop instead of relying on power saver settings, today I got called away from the laptop in a hurry and left the screen open only to find that on my return the Macbook Pro was indeed asleep. Just though I would update the thread and let you know that one of the above is most likely the culprit as these are the only only changes that have been made to my system since my comment below in May. I have tested that sleep works with the lid open a number of times this afternoon and it has worked every time.
Original comment (may be useful for some)....
Yes I am experiencing exactly the same issue on exactly the same OS version. Although old, I have tried the fixes mentioned here but this did not work for me, you may want to try it yourself... http://www.windley.com/archives/2007/10/fixing_macbook_pro_sleep_problems.shtml
I have tried a reboot without loading anything except the startup items which are DropBox, Mail, ISyncIt, VMware Fusion Helper, iTunes helper and GrowlHelperApp and I am still encountering this problem. I am going to try a reboot and disable everything in the startup to see if one of these apps or helpers is the culprit and I will post back the results, I seem to think this is a Snow Leopard issue though and have found various other sites where people are mentioning exactly the same problem.
EDIT Sorry for posting this in the answer section, i'm new here and this was accidental :/ Just wanted to add comment.