I just remembered that I did this before and I believe that I used s a Execute SQL Task.

I defined the From & To as well as the body. You can define an attachment.

Sorry but I do not have the code but it was very easy once I figured out how to do this.

You can also look at sending the result to a variable and look at the MEssage Source Type and Message Source in the Send Mail Task.

Sorry that I can't recall exactly what I did but is was very straight forward.