Now, technically, macro photography only applies to situations where the subject of the photo is the same size as the image sensor (35mm or smaller) - and takes up the whole frame. This is known as 1:1 magnification.
For the purpose of this article, however, I'll simplify to things the size of a quarter or smaller being the main subject.
Generally speaking, there's two ways to go about taking a macro-type shot.
The first is the more obvious. Get as close as possible and take the shot. Many Point and Shoot cameras have a Macro mode - usually indicated by a 'flower' icon. This tells the camera to focus on things that are closer to the camera, rather than things that are farther away (like mountains - the complimentary symbol).
While this method can produces the largest and sharpest pictures of the subject, there's a few drawbacks. One is that you're likely to create shadow, and that hampers the camera focusing, or at the very least, reduces your depth of field (amount of things in focus).
Normally this wouldn't be a problem, but the DOF is very small at close distances already. Not an issue when things are flat and perpendicular to the camera, but that doesn't always happen. Even things as small as bees and flies might not be in focus from front to back or edge to edge (depending on your angle and if you can get them to cooperate).
Which leads to the other problem - most living things (that you'd be taking macro shots of) don't like people in their space. Even if they aren't bothered, trying to get to as close as you can and still being able to see the screen/subject might be difficult.
Bright lighting will help the first problem - but if you're using a flash, it can be overkill. Bright, noisy lights don't help with the second issue either.
So what's the other option? I'm glad you asked (even if you didn't, you're still reading).
It might seem counter intuitive, but put the zoom to as high as it will go and be prepared to stand back. Your minimum focus distance will be quite large - at 10x zoom about 1m/3 feet. The larger your zoom, the more unlike the first method it will be and the farther you'll have to be from your subject. Which is, given the problems listed above, not always a bad thing.
The drawbacks to this method include a lower image quality overall - but better than you might think, especially in bright sunlight. At higher zoom, depending on camera/lighting, camera shake might be a problem. Your angles are also somewhat limited - it's hard to get directly under something that's closer to the ground than your minimum focus distance.
As much as I've love to recommend a DSLR camera with dedicated macro lenses to everyone, it's probably not going to happen. While the price of the camera body is coming down a 50mm macro lens is only barely under $400. Still inexpensive for a lens (comparatively), but it's a one-trick-pony. While you *can* take pictures of larger things, a 50mm prime lens will take in quite a bit more light, be a lot cheaper, lighter and quieter. The 100mm and larger macro lenses give a larger working distance and increase magnification as well, but expect the price to double or quadruple (!). Almost as effective is a 250/300mm zoom at the minimum focus distance as seen below - click for full size.
Above: 250mm lens 1.1m/3.6 feet away
Below: 50mm macro lens ~23cm/0.75 feet away
That said, with the sheer amount of information that any camera today takes in, with time, patience and being in the right place at the right time, you can get a excellent shot with just about any camera.