Yes, you can definitely polish metal with sandpaper, but like many things in life, you have to know what you're doing so you don't damage what you're working on. Many sandpaper manufacturers sell finishing paper specifically designed for polishing metal. Most experts suggest starting with 600-grit sandpaper and moving up to finer sandpaper, along the lines of 1000-grit or even 2500-grit. If your local hardware store doesn't carry the finest grades of sandpaper, your best bet is to look in an auto repair shop.
It's recommended to use aluminum oxide sandpaper or silicon carbide sandpaper when polishing metal; aluminum oxide is the best option for polishing hard metals such as iron and steel, and silicon carbide is good for softer metals such as aluminum and brass. When sanding metal, you should use water to cool down the metal surface and the abrasive. Although water will reduce the amount of dust and debris in the air, you'll have to wipe away the slurry on the surface of the metal that forms as you sand.