Copying values to buffer and sending an email

Hello Experts!
I've been having a problem with creating mail sending macro from my report.

I have come up with an idea to put header of the report in separate sheet “buffer” and then create a macro which will copy row’s range underneath it and then send it all in msg body. It seems that it will make it less fragile.

I need to have AF column active in a main sheet. For example: When double-clicking on AF2 cell, Range("A2:AD2") should copy over to “buffer” sheet. It looks like:

Please Login or Register  to view this content.

This macro needs to be applied to each row.
Double-click on AE2 cell copy Range("A2:AD2"), And paste it to the same place A3 in “buffer” sheet and send email.
Double-click on AE3 cell copy Range("A3:AD3"), And paste it to the same place A3 in “buffer” sheet and send email.
Double-click on AE4 cell copy Range("A4:AD4"), And paste it to the same place A3 in “buffer” sheet and send email.
And so on…

It seems to be easy but don’t know how to make it possible.

Second part which is send email does not require code to be changed each time when double click is performed. That is why “buffer” has been created. This should make it easier.

I have managed to define recipients and email details using following:

Please Login or Register  to view this content.

I’ve been told that this forum is the best place to ask for help.
Experts, how to make first part applicable for each rows and how to merge those lines into smoothly working macro?
It is a hard nut to crack for me. I am learning every day something new about Excel VBA but the clock is ticking and I am asking for your help.

Thank you so much in advance!
MrJimi