macro to send certain range and to filter depending on value

I'll look at this when I get home from work

Just to be clear when you say first workbook presume you point to
THS-Query-Log.xlsm

and second is
Master-Query-Log.xlsm

So if I hear correctly your request you want the macro that is in
THS-Query-Log.xlsm
namely Sub test() to be modified and adapted to your request and reside in
Master-Query-Log.xlsm

Is my understanding correct ?
gowflow

Hi Mike

Have you read my post yet? let me know if my suggestion will work for youand I'll provide code that will produce the workbook to email. The advantage of using Advanced filter will be that it will be faster than Looping for matches.

Hi all,

Firstly, yes gowflow your understanding of what I'm trying to achieve is correct.

I have run the macro on the master query log - there are a few issues listed below.

1. The macro is sending 3 emails rather than one. I have attached copies of the files attached to each email. The result 1 sheet is correct. Just to confirm, only one excel file should be sent in one email.

2. You will notice a visual error in cell E1?

3. Eventually this will be a sizeable document with multiple email addresses. Perhaps Roy's suggestion of  using advanced filter would be useful?

Thanks
result-1.xlsx

result-2.xlsx
result-3.xlsx

Hi Mike

I'll review your workbook tomorrow, I have a quite day. I'll be home late tonight

There is no doubt that filtering will be much more efficient than looping, remember the loop has to check 3 columns of data. I've proved this many times before.

That's great thanks for the update Roy

That works perfectly, great work. Potentially the sheet could have up to 500 rows with 200 different email addresses.

One final thing. I've attached a copy of the master sheet as I need it. Would it be possible to add a small piece of final code to the macro assigned to button "send supplier update". I would like this final piece of code to populate column Z with the date the update is sent. Obviously I only want it to populate the rows that have been sent and not the ones that have been excluded e.g. rows containing RESOLVED or INTERNAL QUERY

Thanks again great work.
Master-Query-Log-V01--2-.xlsm

Note: it is not a fallacy that loops are slower than filtering data.

ok just some clarifications to your last request of date in Col Z

1) My understanding is that you want when the macro runs to: when it find an item that is to be emailed ie with an email address in Col W and no RESOLVED in Col X and no INTERNAL QUERY in Col Y to add today's date in Col Z.

2) If my understanding of item 1) is correct then here is my next question:
If we yesterday say have run the macro and have sent emails to suppliers and dates were stamped with yesterday's date in certain rows and we have updated the file with new information and we are today running again the macro, shall we DISREGARD all items that have a date in Col Z (as already sent to supplier) or still we ignore date in Z and if the item meet the criteria to still send it today as well ???


Please let me know
gowflow

@Roy_Cox
Maybe my statement about 'fallacy' is not the correct intention in this context but should be rather 'not appropriate in this context.

Please remember that OP has clearly asked in his question:

I would like to assign the same macro to workbook 2 only with the following differences......

 1. Only send columns in the range A:W - do not include any columns after this.
 2. Change it so it sends to the email address in column W
 3. Do not collate and send rows where column X contains (RESOLVED) or column Y contains (INTERNAL QUERY)

So I clearly answered the request and adapted the existing macro to the new requirements. for sure there could be several ways of doing it among which filtering the data and copy/paste ranges and this would involve sorting the data per emails so we don't fall in the trap of sending the same data twice and avoid also too many loops but at the end this would still entail looping somehow etc.. but this would not be answering the question as the OP wanted. do not have the intention to open a discussion on this but just needed to clarify this point.

gowflow

2) If my understanding of item 1) is correct then here is my next question:
If we yesterday say have run the macro and have sent emails to suppliers and dates were stamped with yesterday's date in certain rows and we have updated the file with new information and we are today running again the macro, shall we DISREGARD all items that have a date in Col Z (as already sent to supplier) or still we ignore date in Z and if the item meet the criteria to still send it today as well ???

yes I want the macro to continue to send to all email addresses (excluding those containing internal query & resolved) even if there is a date Z. The date would simply be updated.

Thanks