EMail & Excel

Stanman

New Member
Joined
Jan 15, 2020
Messages
26
Office Version
  1. 2010
Platform
  1. Windows
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")
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Stanman

New Member
Joined
Jan 15, 2020
Messages
26
Office Version
  1. 2010
Platform
  1. Windows
Partial Monthly Calculator - Email Version V2.xlsx
ABCDEFGHIJKLMNOPQ
1Tenancy ReferenceRent Account NameAddressStart DateMonthly rentFinance ApprovedDate applied to accountMiscLast date of month Tenant moved inDays to payDaily RateRent adjustmentSend EmailEmail addressBody of EmailExtracted DateExtracted Start Date
212345Joe Vlogger12 Every Street01/02/2021£789.8928/02/202128.00£25.97£727.13#VALUE!myemail@emailaddress.org.ukWe 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 YouFebruary 202101/02/2021
31234Jiohn Smith123 Any street01/11/2020£560.0030/11/202030.00£18.41£552.33Send emailmyemail@emailaddress.org.ukWe 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 YouNovember 202001/11/2020
Account List
Cell Formulas
RangeFormula
I2:I3I2=EOMONTH([@[Start Date]],0)
J2:J3J2=[@[Last date of month Tenant moved in]]-[@[Start Date]]+1
K2:K3K2=[@[Monthly rent]]*12/365
L2:L3L2=ROUND([@[Daily Rate]]*[@[Days to pay]],2)
M2M2=HYPERLINK("mailto:"&[@[Email address]]&"?subject="&[@[Tenancy Reference]]&""&"&body="&$O$2,"Send email")
N2:N3N2=IF([@[Tenancy Reference]]>0,"myemail@emailaddress.org.uk","Please complete all fields in green")
O2O2=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:P3P2=TEXT([@[Start Date]],"mmmm yyyy")
Q2:Q3Q2=TEXT([@[Start Date]],"dd/mm/yyyy")
M3M3=HYPERLINK("mailto:"&[@[Email address]]&"?subject="&[@[Tenancy Reference]],"Send email")
O3O3=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")
 
Last edited:

Stanman

New Member
Joined
Jan 15, 2020
Messages
26
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Jan 15, 2020
Messages
26
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,971
Messages
5,767,391
Members
425,410
Latest member
SmittyT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top