Loop through autofilter and send via email to address in column L

Hi

I would appreciate your help in my problem that I am having.

I have a spreadsheet that I need to loop through the autofilter and send the results to the email address in column K. The autofilter needs to be where J is not blank and an email address in column K.

The results need to be copied into a new workbook each time. The workbook does not need to be saved each time. Once the email has been sent the rows need moving from "To be allocated" sheet to the relevant sheet which is decided by the first letter in column I, If M needs to go to sheet named "M" if C needs to go to sheet named "C".

Example attached.

Kind Regards
Mike

I have just found a similiar post where the results are saved each time which is below. But can't get this working there only difference would be the workbook does not need to be saved and that the workbook needs to be emailed to the address contained in column K.

http://www.excelforum.com/excel-prog...ered-list.html

Any help would be appreciated.

Cheers
Mike

Once the autofilter has been done, the columns A to J need to be copied into the new workbook and sent via email to the address in column K.

I will have a look at the thread you have provided.

Cheers
Mike

Almost done it with the below code. Just need to move the rows onto the other sheet once the email has been sent. I have changed the formula in column K to only show the address if J is not blank therefore only needing one filter.

Please Login or Register  to view this content.

Everything is now working but how do I stop it from trying to autofilter for blanks?

Cheers
Mike