Selected Answer
Mark
My suggestion is that you loop through worksheets using the code below (added as Module 6 in the revised file attached) which can be run from the button "Advise Sellers" on the Daily... sheet.
It runs though all sheets (ignoring the 2 mentioned) and, if there's a sale (indicated by something in cell A8) adds that to a string (and prints it to the Immediate window). Once all sheets have been examined, it gives a message (but you can inspect/check the results in the Immediate window):
Sub OnlySales()
Dim ws As Worksheet, Sellers As String
'Loop though sheets
For Each ws In ThisWorkbook.Sheets
If ws.Name = Sheet1.Name Or ws.Name = Sheet17.Name Then
' do nothing for those (Daily Sales... and Trader info...)
Else
' check if first sale is on the sheet
If ws.Range("A8").Value <> "" Then
'add the string
Sellers = Sellers & ws.Name & ", "
' print to VB Explorer's Immediate window
Debug.Print ws.Name
End If
End If
Next ws
' confirm list
MsgBox "Sent emails to " & Sellers
End Sub
You could replace the lines in bold with ones to create an individual email. I'd suggest you put the seller's email somewhere on their individual sheet e.g. in row 1 (which seems to be hidden in most) so your code within the Else portion can include lines like (from Module 4):
' Build and Send the Email
With emailItem
.To = ws.Range("A1").Value
Hope this helps.
