So the answer to this took some time to find but ended up trivial but not straightforward to spot (to me at least). Damn you PATH...
When my plugin was running, I noted that if I ran os.getcwd() within my plugin, it did not actually output the plugin directory, but instead "C:\Program Files (x86)\QGIS" - the install directory of QGIS for my current machine. So any relative paths (e.g. a DLL looking for a dependent DLL in the current working directory) wouldn't work because I wasn't starting off where I thought I was.
So I made sure that my plugin and DLL directories were included in PATH:
plugin_dir = os.path.dirname(os.path.abspath(__file__))
dll_dir = os.path.join(plugin_dir + 'dlls')
But yet I still found a similar problem. The libspatialite.dll path was correct and all the right directories showed up if I ran:
for i in sys.path:
Also, my code continued to work as a standalone application. But they would not work within QGIS, outputting the same "the specified module could not be found" error.
The answer finally came when I stumbled across (this post). Fundamentally, it seems that sys.path.append doesn't seem to do quite enough if the DLL being loaded has linked DLLS itself - which the version of libspatialite.dll I am using does.
Now, I am not entirely sure why a DLL with linked DLLs would work when run from a script when the Python current working directory is the same directory as the DLLs, and would not work when loaded from within QGIS, but this seems to be the case. I'm lead to believe this could be something to do with "runtime dynamic linking" but I don't know enough about it to be sure.
So, final solution was to permanently modify PATH
if spatialite_dll_dir not in os.environ['PATH']:
os.environ['PATH'] = spatialite_dll_dir + ';' + os.environ['PATH']
And everything is now working! For now at least...and thank you all very much for your help and suggestions.