Automated Number Allocation & Email individuals

[danweeks20]

Hi all,
I am trying desperately to get this spreadsheet to conform, my VBA knowledge is limited, and I have tried recording macros etc, but cannot seem to get this to work.
I have attached the spreadsheet to help out with my explanation.
In Sheet "CP", the allocate button has the following code attached, but I need it to allocate the numbers based on the volume in F5 for block 1, and F6 for block 2 and so on for each individual based on their values.

Here is the code I have so far. I have tried to get the button to check first that the remaining amount of numbers in J4 & K4 and if below 0 a message box appears canceling the request and requesting the person to go back and check their allocation figures.

Please Login or Register  to view this content.

Once it has done this, I am wanting to filter the information on the 'Number Allocation' sheet and email the respective individuals their allocated numbers. I have looked into filtering the data one by one into another sheet and emailing, but this seems a long rounded way to go about it?

Any help would be greatly appreciated.
I have already lost the sheet once due to a '400' error!

Thanks in advance, Dan