Macro to generate email, modify to update subject and body based on date

I have code to double click on a cell in Col D and an email is generated

I need the code amended so that

1) The subject shows Comms for May 2020 (see cell R1)
2) Please find your comms payable in june 2020 amounting to …..

Please Login or Register  to view this content.

It would be appreciated if some could kind amend my code

Your code is getting your dates from Target.Row but your sample file shows the dates in row 1. If you are really getting dates from Target.Row, as your code shows, make the following updates.

If the dates are always in row 1, no matter where the user clicked, then you need to change Cells(Target.Row, "R").Value to Cells(1, "R").Value (same for the value in column S).

Try this:

Please Login or Register  to view this content.