macro to send certain range and to filter depending on value

Attached are two workbooks.

Work book one contains a spreadsheet with a number of rows containing supplier terms.
You will also see a button "press to send terms to suppliers" - This button will collate all the rows where the email address in column T are the same (and the column headings) and will send a separate workbook to the appropriate email address. PLEASE TEST

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)

Thanks
THS-Query-Log.xlsm
Master-Query-Log.xlsm
mikes6058Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
I'll look at this when I get home from work
Roy CoxGroup Finance ManagerCommented:
In master Query log there is a second sheet. Does this contain the headers that will need to be sent?

I am thinking that we can use AdvancedFilter to populate a template sheet then save that template as a new workbook and attach to an email.

Let me know whether this is what you have in mind.
gowflowCommented:
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
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

gowflowCommented:
If your answers to my last post are YES then pls find the macro for the file
Master-Query-Log.xlsm
that is included in the attached file.

Sub test2()


Dim original_wb As Workbook
Dim new_wb As Workbook
Dim row_count As Long, col_count As Long, I As Long, J As Long
Dim attachname As String, emailaddress As String
Dim findprevem
Dim OutApp As Object, OutMail As Object

Set original_wb = ActiveWorkbook 'Workbooks.Open("P:\Cindy Simmers\Query Log\Rob - Query Log Work\THS_Query_Log.xlsm") 'adjust file location

row_count = original_wb.Sheets(1).UsedRange.Rows.Count
col_count = original_wb.Sheets(1).UsedRange.Columns.Count

For I = 2 To row_count Step 1
    
    attachname = "THS_Direct_Trading_Terms_Contact_Details.xlsx"
    emailaddress = original_wb.Sheets(1).Cells(I, "W").Value 'email address from column T
    If emailaddress <> "" Then
        findprevem = Application.Match(emailaddress, original_wb.Sheets(1).Range(original_wb.Sheets(1).Cells(2, "W"), original_wb.Sheets(1).Cells(I - 1, "W")), 0)
        If Not IsError(findprevem) Then
          Set new_wb = Workbooks.Add
          
          original_wb.Sheets(1).Range("A1:W1").Copy Destination:=new_wb.Sheets(1).Range("A1")
          For J = I To row_count
            If original_wb.Sheets(1).Cells(I, "W").Value = original_wb.Sheets(1).Cells(J, "W").Value And _
               original_wb.Sheets(1).Cells(J, "X").Value <> "RESOLVED" And _
               original_wb.Sheets(1).Cells(J, "Y").Value <> "INTERNAL QUERY" Then
                original_wb.Sheets(1).Range("A" & J & ":W" & J).Copy Destination:=new_wb.Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1)
            End If
          Next J
          
          Application.DisplayAlerts = False
          new_wb.Sheets(1).UsedRange.EntireColumn.AutoFit
          new_wb.SaveAs Environ("temp") & "\" & attachname
          new_wb.Close SaveChanges:=False
          Set new_wb = Nothing
          Application.DisplayAlerts = True
        
          Set OutApp = CreateObject("Outlook.Application")
          Set OutMail = OutApp.CreateItem(0)
          With OutMail
                  .To = emailaddress
                  .CC = ""
                  .BCC = ""
                  .Subject = "This is the Subject line" 'adjust subjectline!
                  .Body = "Dear Supplier," & vbCrLf & vbCrLf & "Attached are the terms/details we hold on file for your current trading terms and contact details with THS direct. Please can you confirm these details are correct by placing a 1 in cell Z3." & vbCrLf & "If there are any differences please overwrite the current terms in red font." & vbCrLf & "Once confirmed please return the complete spreadsheet to rob.marr@thstools.co.uk" & vbCrLf & vbCrLf & "These terms will be incorporated into our THS Supplier Buying Agreement (SBA) which will subsequently be sent to yourselves to sign and return." & vbCrLf & vbCrLf & "Rob"
                  .Attachments.Add (Environ("temp") & "\" & attachname)
                  .Send
                  
          End With
          
          Set OutMail = Nothing
          Set OutApp = Nothing
          
          Kill Environ("temp") & "\" & attachname
        End If
    End If
Next

End Sub

Open in new window


Let me know.
gowflow
Master-Query-Log.xlsm
Roy CoxGroup Finance ManagerCommented:
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.
mikes6058Author Commented:
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
mikes6058Author Commented:
Roy CoxGroup Finance ManagerCommented:
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.
mikes6058Author Commented:
That's great thanks for the update Roy
gowflowCommented:
Well looking at your macro closer it is obvious that your original macro have the same falut as this one except that in your original one the data starts at row 5 meaning you have couple of blank rows prior hitting the rows with valid emails in Col T where as in this file emails starts on row 2 directly this is why iit was failing.

I corrected the macro to perform correctly.
Pls check this version.

NOTE
It is a total fallacy to believe that looping will be slow as here it is not meaningful. Pls post large dataset and will check if this is correct.

PLs test this macro and let me know.
gowflow
Master-Query-Log-V01.xlsm
mikes6058Author Commented:
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
Roy CoxGroup Finance ManagerCommented:
Note: it is not a fallacy that loops are slower than filtering data.
gowflowCommented:
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
gowflowCommented:
@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
mikes6058Author Commented:
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
gowflowCommented:
ok clear here you are.
Let me know if this is what you want.
gowflow
Master-Query-Log-V02.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.