Distance works just fine. The problem is you used @s(the executor).
When you run it in chat, you are the executor so you are the one that run and received /tell.
But if you run it in command block, well...
If the executor was not an entity, for instance if the executor was a command block or a server console, this selector will find nothing.
Change it to...
/execute as @a if entity @s[x=X,y=Y,z=Z,distance=..5] run tell @s <message>
/tell @a[x=X,y=Y,z=Z,distance=..5] <message>