I think the problem arises because (I quote) "An Auto_Close procedure
runs just before the workbook containing the procedure closes."

So the auto_close procedure shouldn't itself need to close  the
workbook as it's already in the process of being closed.

Two answers:
1. Don't close the workbook from within the auto_close procedure -
it's there to tidy up just before the workbook is closed by
something/one else. I guess in your case the prompt  you're getting is
coming up before Auto_Close is called.

2. If you call this procedure from within VBA and you want it to close
the workbook then call it something else, eg. Sub myClose.

ActiveWorkbook.Saved is a property which is True if no changes have
been made to the workbook since it was last saved; False otherwise.
But you can set this property to True if you want to close a modified
workbook without either saving  it or being prompted to save  it.

Set DisplayAlerts property to False if you don't want to be disturbed
by prompts and alert messages while a macro is running; any time a
message requires a response, Microsoft Excel chooses the default
response. You shouldn't need to use this if you've used the .Saved
property set to True.