Create a variable (at project scope) of type "object"

Create a data flow, executing the stored procedure as it's source, with a Record Set as destination, wherein you instruct the results of the stored procedure to be inserted into the above created variable.

Then create a for each loop over the variable, extracting the relevant columns and allocating to your variables for use in the e-mail task.