Thanks NoSparks. I am new to this coding hence i worte that leanthy code. But your is awesome.
Its working perfect. I just neeed to add one more condition to it. If there are any duplicate value in column D(i.e.header 3), then again macro should highlight those cells( with duplicate values) and pop up a msg stating there are duplicate values in column D.
So ideally this macro should do 2 checks
first : Highlight blank cells if any in mandatory field and pop msg stating the same. (which we have already done)
second: highlight Duplicate value cells in column D and pop up msg stating the same.

and if no blank cells and no duplicate values then send mail.

If you can assist with that then it would be awesome.

Thanks.