The biggest issue you have is your Graphics card (or lack of one). Instead of spending a full $500 I would suggest doing a partial upgrade and saving your money to get an all new machine in a few years (your CPU is a bit old but should still be somewhat serviceable).
You will also need a new power supply which means removing the old one and connecting all of the components up to the new one.
Also make sure to measure the space in your case for both the power supply and where the video card will go to verify they will fit.
Someone else may have better ideas on power supplies and vid cards but you don't want to go to crazy on the vid card as you have an older quad core.
Also for your issue of running out of memory that should not happen with 6GB. go into your programs that run at startup and disable any you don't use regularly. some things like HP printing programs can be real hogs.
Have you ever done a fresh install of Windows? If you haven't yet, it is probably time. If you have the Vista disks it is super easy. Just make sure to back up anything you want to keep. If you don't want to go through the hassle of doing this, make sure you disable startup programs as Dey suggested. Another decent idea would be to use CCleaner, to clean up your registry and temp files.
I'm not sure how well that motherboard can handle overclocking, but you could also spend $25 on a aftermarket CPU cooler (a Coolermaster Hyper 212 is a good budget cooler) and overclock your cpu. You could get it up to 3.0GHz easily, and up to 3.2-3.3 with a little work.
If you overclock and do some clean up or reinstall, add a $150 graphics card, you comp should be able to continue to play the new titles. I have a Q6600 (OC'd to 3.2GHz), and a GTX 460 (overclocked again), and can play Battlefield and Skyrim, on high with respectable fps. I think I can get at least another 2 years out of this set up before even running on low settings will no longer yield respectable fps.
If this was my computer the first upgrade I would do would be to Install 64 bit Windows7 on it. A reinstall of the operating system would speed up the computer no end and Windows 7 is faster than Vista anyway and would use all of your memory. When it comes to your games, only fitting a better graphics card will help, but unfortunately if you do this the you would probably have to get a bigger power supply.