Short: A second-hand manual focus lens of suitable quality will probably do what you want well at a small fraction of the cost of a new AF lens.
You may be able to get a newer lens that is superior in quality of results than any older lens - but you may not, and that part of the answer is subjective and up to you to decide.
As far as usefulness goes, in this context using any manual-focus-only lens that does what you want optically will be just as good as using an AF lens. The Sony e-mount allows the use of adapters to mount the vast majority of lenses from other mount systems. In the macro context you need to be able to mount and focus the lens and ideally alter aperture - see below. Essentially all older lenses will allow focusing by manual control on the lens body independent of the camera.The Sony "peaking" focus detection system works with manually focused lenses and allows extremely good manual focusing. The Sony focus magnifier (typically up to about 10x) also allow superb manual focusing by eye.
So, focusing is not a problem and even with an AF lens manual focusing would usually be preferred. If necessary or desired focusing can be achieved by changing the camera to target distance by moving one or both. While this is not usually desirable or convenient it may prove useful in some cases. (eg micro moving a "stage" may be more precise and easily semi-automated.
Aperture control is "highly desirable", but as most (but not all!) dumb adapters allow manual variation of aperture this is also easily achieved. It's worth noting that "smart" adapters that allow aperture control by the camera also show the aperture used in-camera when used in A or M modes, while dumb adapters allow aperture to be set BUT do not show what aperture is used in-camera. The results can be the same but if actual aperture used is desired to be known the adapter needs to have an aperture calibration scale relevant to the lens used.
In macro work the main requirements for using a 3rd party lens on an e-mount camera are
Ability to mount "correctly" and easily in proper relationship to the camera
Ability to focus across a useful range of distances.
Ability to control aperture - highly desirable but may not be essential.
E-mount to "other" adaptors do one or more of the following
All allow positioning the lens at the correct distance from the e-mount camera sensor plane.
Most allow control of the lens aperture ring - usually manually with an external lever of the adapter. ALL adapters should allow aperture control (except for fixed aperture lenses such as "mirror"lenses. Adapters which do not allow aperture control or which are designed to open the aperture to maximum (lowest f number) without control should be avoided unless there is some other very good reason to use them*). "Dumb" adapters for a wide range of lens mounts are typically available in the $US10 - $30 range.
Some allow passing electrical signals via contacts between camera and lens with analysis and conversion as required. Adapters that allow this are usually much higher cost than "dumb" adapters. This will usually allow AF (contrast only and VERY slow. These typically cost $100 - $300.
More expensive again are adapters that allow phase focusing - typically by using a part reflective mirror to send typically 30% (1/2 stop) of light to phase detectors in the adapter. These typically cost in the $500 +/-$? range.
If you are prepared to "play" yourself you can easily enough make an adapter for almost any lens using a short length e-mount adapter ring and something that will mount the target lens and then joining them "in some manner". You could eg use a lens rear-cap from the target lens, and connect it to the e-mount ring or even (gasp) use Plasticine of a 3D printed plastic adapter or some other material.
- Aperture control using dumb adapters:
I recently acquired a used Sony NEX-5N.
I purchased a used Sony LA-EA1 A-mount to e-mount adapter at the same time.
I ordered two dumb adapters from China via ebay.
1 x Sony/Minolta A-mount to e-mount
1 x Olympus OM series to e-mount adapter.
Both dumb adapters appeared to have aperture control rings and neither noted that they did not have. The A-mount adapter DOES have aperture control and works as expected. The OM mount adapter appears to have such a control but it is cosmetic and in fact uses a "finger" to provide maximum lens aperture during mounting. This is a sub-standard corner-cutting approach and my feedback will reflect this if I get around to placing it. In my case it is not important as I wish to use the adapter with an Olympus constant f/8 aperture mirror lens, but other buyers would be less well served.
While the LA-EA1 adapter shows aperture in-camera and allows aperture to be set by the camera the dumb adapter does neither (as expected). The dumb adapter allows aperture to easily be set using the control ring and the amount of light being admitted is visible on the NEX display but the ring is not calibrated or graduated in any way. I intend to add a scale but expect that different lenses will have different apertures for a given ring position - an acceptable limitation given the under $20 landed cost and under 2 week delivery time from China.