It's not hard to find articles or blog postings on the web talking about when a test should or shouldn't be automated. I also know that there are plenty of "test automators" in the industry who spend too much time trying to automate things that shouldn't be automated. As extreme as the automation camp can be at times, I am even more surprised to see testers at the other end of the spectrum - those who say that only humans (or thinking humans) can test effectively.
Test automation is, perhaps surprisingly, context driven. If I'm testing an internal application, I doubt I would ever write automation for it. I may not even write automation for a shrink wrap application if the context was right.
Of course, the context at Microsoft dictates a higher level of automation. I would estimate that 70% of the automation written at MS is non-UI testing. Given that we create operating systems and platforms, we have a lot of APIs and object models to test. Add to that the number of configurations these platforms and systems run on, testing without automation would be idiotic. I would also estimate that 99% of the UI testing (within the 30% of UI testing for those of you doing math at home) is done via an object model or via an MVC interface such as IAccessible. This sort of UI automation tends to be a bit more resilient to changes and breaks (a little) less often.
The "other" UI automation - that tiny bit done by attempting to send keystrokes and mouse clicks is always problematic, and I don't think it ever has a reasonable ROI.
The problems with test automation, and probably the root cause of everything the anti-automators have with automation, is automating the wrong tests. If you've ever talked to me about this subject, you know that my opinion is that you should automate 100% of the tests that should be automated. Where people make mistakes on both sides is determining which tests should be automated. If you tell me that everything should be automated, or that nothing should be automated, I think you are telling me that you don't know how to figure out which tests to automate - which means you're not thinking about the problem.