It is a general messaging service and can deliver messages to SQS, AWS Lambda, email and arbitrary HTTP(S) endpoints.
For example, we have some S3 buckets set up to send an SNS message to an SNS topic on new object creation, the topic then in turn notifies our subscribed HTTPS endpoints by sending the SNS message which we can then parse. You can also connect S3 directly to AWS Lambda so that a new event in the bucket sends an SNS message to a lambda function - which is very handy for doing things such as "server-less" thumbnailing, transcoding, etc etc.