EMail & Excel

[Stanman]

Hi,
I have the hyperlink working fine but when I pull the date into the email, it is not coming across as a date buyt a number like 44567.

I have the data as a table and formula is below. The data filed is "&[@[Start Date]]&", I have tried "&TEXT[@[Start Date], "dd/mm/yy"]&" but not joy, anyway it can be fixed?

The extracted date works as I pulled the whole date as just a month and year in another column. I could do the same here but would appreciate if I could do it int he formula


=IF([@[Monthly rent]]="","","We have made a partial rent adjustment for "&[@[Extracted Date]]&". The Tenancy started on the "&[@[Start Date]]&" Property address is "&[@Address]&", tenancy reference is "&[@[Tenancy Reference]]&", adjustment amount is £"&[@[Rent adjustment]]&", This is for "&[@[Days to pay]]&" days.%0AThank You")

 

[Stanman]

Partial Monthly Calculator - Email Version V2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Tenancy Reference	Rent Account Name	Address	Start Date	Monthly rent	Finance Approved	Date applied to account	Misc	Last date of month Tenant moved in	Days to pay	Daily Rate	Rent adjustment	Send Email	Email address	Body of Email	Extracted Date	Extracted Start Date
2	12345	Joe Vlogger	12 Every Street	01/02/2021	£789.89				28/02/2021	28.00	£25.97	£727.13	#VALUE!	myemail@emailaddress.org.uk	We have made a partial rent adjustment for. The Tenancy started on the 01/02/2021 Property address is 12 Every Street, tenancy reference is 12345, adjustment amount is £727.13, This is for 28 days.%0AThank You	February 2021	01/02/2021
3	1234	Jiohn Smith	123 Any street	01/11/2020	£560.00				30/11/2020	30.00	£18.41	£552.33	Send email	myemail@emailaddress.org.uk	We have made a partial rent adjustment for November 2020. The Tenancy started on the 01/11/2020 Property address is 123 Any street, tenancy reference is 1234, adjustment amount is £552.33, This is for 30 days.%0AThank You	November 2020	01/11/2020
Account List

Cell Formulas
Range	Formula
I2:I3	I2	=EOMONTH([@[Start Date]],0)
J2:J3	J2	=[@[Last date of month Tenant moved in]]-[@[Start Date]]+1
K2:K3	K2	=[@[Monthly rent]]*12/365
L2:L3	L2	=ROUND([@[Daily Rate]]*[@[Days to pay]],2)
M2	M2	=HYPERLINK("mailto:"&[@[Email address]]&"?subject="&[@[Tenancy Reference]]&""&"&body="&$O$2,"Send email")
N2:N3	N2	=IF([@[Tenancy Reference]]>0,"myemail@emailaddress.org.uk","Please complete all fields in green")
O2	O2	=IF([@[Monthly rent]]="","","We have made a partial rent adjustment for. The Tenancy started on the "&[@[Extracted Start Date]]&" Property address is "&[@Address]&", tenancy reference is "&[@[Tenancy Reference]]&", adjustment amount is £"&[@[Rent adjustment]]&", This is for "&[@[Days to pay]]&" days.%0AThank You")
P2:P3	P2	=TEXT([@[Start Date]],"mmmm yyyy")
Q2:Q3	Q2	=TEXT([@[Start Date]],"dd/mm/yyyy")
M3	M3	=HYPERLINK("mailto:"&[@[Email address]]&"?subject="&[@[Tenancy Reference]],"Send email")
O3	O3	=IF([@[Monthly rent]]="","","We have made a partial rent adjustment for "&[@[Extracted Date]]&". The Tenancy started on the "&[@[Extracted Start Date]]&" Property address is "&[@Address]&", tenancy reference is "&[@[Tenancy Reference]]&", adjustment amount is £"&[@[Rent adjustment]]&", This is for "&[@[Days to pay]]&" days.%0AThank You")

 

[Stanman]

I have uplaoded the spreadsheet.

I also have a 2nd issue, it will not generate an email when body is pointed to column O.

Could it be because of all the linked cells in the body message?

Thank you

 

[Stanman]

Any help on this would be appreciated. If I change the location of the cell in the formula in M2 to point anywhere else it works.
I have counted the cell characters and there less then 255.