Macro to copy paste data as transpose and to send email

Hi Team

I need to create a macro as follows

My data

Client Versionref Product Emailid
A 1 EL abc@gmail.com
A 10 PL abc@gmail.com
A 18 CL abc@gmail.com
B 8 CCI efg@gmail.com
B 6 EL efg@gmail.com

What I want

The above values needs to be copy pasted in a new workbook as follows
Client EL PL CL
A 1 10 18
Mail Sent Yes Yes YEs ----- This row is standard text for all

the above file should be saved under Temp file by client name and gets emailed to abc@gmail.com through macro

same for client B
Client CCI EL
B 8 6
Mail sent Yes Yes

the above file should be saved under Temp file by client name and gets emailed to efg@gmail.com through macro

Is it possible to do through macro?

Thank you