I don't think OP was talking about the MPE-65, merely the EF or EF-S 65mm Macro.
The MPE-65 might work with some things fairly close to the glass, at the lowest macro-zoom (1x), but I can't see you being able to get close enough to get a good shot at higher macro settings, short of having a waterproof DSLR housing, and having the camera literally in the tank. And at that macro range, you would need a focus rail, ring light, and tripod or some sort of stabilization.
I love to see some shots taken that way. Close-ups of the polyps... fish eyes, scales, etc.
The MPE-65 can fill the image screen with a single grain of rice. At full macro, if you take a picture of magazine text, you will maybe get 1-2 letters in the frame. Combine it with a full-frame body, and you'd have some amazing images.
ETA: Ah, I mispoke. I thought for sure there was an EF/EF-S 65mm macro, looks like it's the MPE-65 or the EF-S 60mm.
--Gray