Formatting text to match Red/Amber/Green reports

[keith]

Please can someone suggest a way to do this - I've had to give up.

I have a sheet of project progress reports with columns that run

A B C D
Manager Project Statustext Flag

where the flag is text saying Red Amber or Green.

I'd like to produce a a report I can copy/paste into an outlook email
giving a oneliner per project.

Simple answer is to use a column E with =A&B&C&D and copy E and paste
special text.

BUT I want to do it so that the Flag is highlighted in RED/AMBER/GREEN
colour or background, and survives the copying into outlook.

Is there a way to do this without resorting to pasting it in as a picture?

cheers

Keith