The first step in automation success is knowing when to automate and when not to automate. Here are some guidelines:
DO automate repetitive tasks.
Recently I needed to add over 600 records to our application in order to fully test a feature. Rather than add just a few records to partially test it, I created a simple test that would add as many records as I specified. Another great thing to automate is a feature with a number of different permutations.
DON'T automate brand-new features.
Yes, you will probably want to automate the feature eventually, but not the instant it's coded. A good QA tester will spend a lot of time doing manual, exploratory testing to make sure that she fully understands the feature and knows its limitations.
DO automate basic smoke-level tests.
I like to think of smoke-level tests as tests that we would be really embarrassed by if they failed in the field. One company where I worked had a search feature broken for weeks, and no one noticed because we hadn't run a test on it. Unfortunately, the bug was pushed out to production and seen by customers. Automating these tests and running them at every build or early in the morning can help catch major problems quickly.
DON'T automate fragile features or works in progress.
There may be a feature of your company's software that seems to break every time someone looks at it funny. Automating tests of this feature practically guarantees that the tests will fail daily. Similarly, a feature that is still in progress will have many changes that will cause you to make frequent code changes in your tests. It's better to keep testing the feature manually and reporting bugs on it until it becomes more stable.
DO automate things users will do every day.
What is the primary function of your software? What is the typical path that a user will take when using your software? This is the kind of thing that should be automated. Rather than running through this manual path every morning at 9 AM, you can set your automated test to do it at 8 AM and you'll know right away if there is a problem.
DON'T automate weird edge cases.
There will always be bugs in software, but some will be more likely to be seen by users than others. You may be fascinated by the bug that is caused by going to a specific sequence of pages, entering non-UTF-8 characters, and then clicking the Back button three times in a row, but since it's very unlikely that an end-user will do this, it's not worth your time to design an automated test for it.