Hello, I am looking to express some Excel functions in VBA. The purpose of it is that i want to add the result as an email body with email signature and the only way to do it that i have found out is with .HTMLBody, which does not keep the formatting where cell is referenced, but makes it one string. Email is generated on worksheet change event (dropdown list in column C in Sheet "VAT registrations") using Target.Offset references. Thanks in advance!



 IFERROR(IF(F3="Finalised",CONCATENATE("Hi ",LEFT('VAT registrations'!H3,FIND(" ",'VAT registrations'!H3)-1),","&CHAR(10)&CHAR(10),"I am happy to report that the VAT registration of ",'VAT registrations'!A3," in ",PROPER('VAT registrations'!B3)," is now finalised. Please note the following:"&CHAR(10)&CHAR(10)," *   The VAT number of the company is .....please double check.....",'VAT registrations'!K3,". Attached you may find the official confirmation we received from the Taxation Office in ",PROPER('VAT registrations'!B3)," confirming the finalised registration with the relevant information."&CHAR(10)," *   The effective date of the registration is .....please double check.....",TEXT('VAT registrations'!P3,"dd/mm/yyyy"),"."&CHAR(10)," *   The Business Activity Statements filling frequency is .....please fill....."&CHAR(10)," *   The deadline for submission is .....please fill.....Therefore the deadline for submitting the first declaration is .....please fill....."&CHAR(10)&CHAR(10),"The client will be charged EUR ",TEXT('VAT registrations'!R3,"#,##0.00")," for the registration."&CHAR(10)&CHAR(10),"Kind regards,"),""),"")