I keep reading that Lisp macros are one of the most powerful features of the language. But reading over the specifications and manuals, they are just functions whose arguments are unevaluated.
Given any macro (defmacro example (arg1 ... argN) (body-forms)) I could just write (defun example (arg1 ... argN) ... (body-forms)) with the last body-form turned into a list and then call it like (eval (example 'arg1 ... 'argN)) to emulate the same behavior of the macro. If this were the case, then macros would just be syntactic sugar, but I doubt that syntactic sugar would be called a powerful language feature. What am I missing? Are there cases where I cannot carry out this procedure to emulate a macro?
I can't talk about powerful because it can be a little bit subjective, but macros are regular Lisp functions that work on Lisp data, so they are as expressive as other functions. This isn't the case with templates or generic functions in other languages that rely more on static types and are more restricted (on purpose).
In some way, yes macros are simple syntactic facilities, but you are focused in your emulation on the dynamic semantics of macros, ie. how you can run code that evaluates macros at runtime. However:
eval is not equivalent to expanded codeFunction, like +, do not inherit the lexical scope:
(let ((x 30))
(+ 3 4))
Inside the definition of +, you cannot access x. Being able to do so is what "dynamic scope" is about (more precisely, see dynamic extent, indefinite scope variables). But nowadays it is quite the exception to rely on dynamic scope. Most functions use lexical scope, and this is the case for eval too.
The eval function evaluates a form in the null lexical environment, and it never has access to the surrounding lexical bindings. As such, it behaves like any regular function.
So, in you example, calling eval on the transformed source code will not work, since arg1 to argnN will probably be unbound (it depends on what your macro does).
In order to have an equivalent form, you have to inject bindings in the transformed code, or expand at a higher level:
(defun expand-square (var)
(list '* var var))
;; instead of:
(defun foo (x) (eval (expand-square 'x))) ;; x unbound during eval
;; inject bindings
(defun foo (x) (eval `(let ((z ,x)) (expand-square z))))
;; or expand the top-level form
(eval `(defun foo (x) ,(expand-square 'x)))
Note that macros (in Common Lisp) also have access to the lexical environment through &environment parameters in their lambda-list. The use of this environment is implementation dependent, but can be used to access the declarations associated with a variable, for example.
Notice also how in the last example you evaluate the code when defining the function, and not when running it. This is the second thing about macro.
In order to emulate macros you could locally replace a call to a macro by a form that emulates it at runtime (using let to captures all the bindings you want to see inside the expanded code, which is tedious), but then you would miss the useful aspect of macros that is: generating code ahead of time.
The last example above shows how you can quote defun and wrap it in eval, and basically you would need to do that for all functions if you wanted to emulate the preprocessing work done by macros.
The macro system is a way to integrate this preprocessing step in the language in a way that is simple to use.
Macros themselves are a nice way to abstract things when functions can't. For example you can have a more human-friendly, stable syntax that hides implementation details. That's how you define pattern-matching abilities in Common Lisp that make it look like they are part of the language, without too much runtime penalty or verbosity.
They rely on simple term-rewriting functions that are integrated in the language, but you can emulate their behavior either at compile-time or runtime yourself if you want. They can be used to perform different kinds of abstraction that are usually missing or more cumbersome to do in other languages, but are also limited: they don't "understand" code by themselves, they don't give access to all the facilities of the compiler (type propagation, etc.). If you want more you can use more advanced libraries or compiler tools (see deftransform), but macros at least are portable.