Hi Lajos,

Yes this can be done. However, if I write code now based on the sample data you entered above which I have to copy into a new workbook, the code will almost certainly have to be modified to suit your workbook.  Things like sheet names, cell references etc may be different in your workbook.

So it is always best to supply a workbook with your data when asking a question here.  If you can please do this I'll see what I can do to help.

regards

Phil 

Hi Phil to modify the code is not an issue If you could do it with tha sample. I will see than what should I change. I would also learn with modifying it.

Thanks in advance for all your effort helping me out with this. And thanks for the advice. I am pretty sure this will not be my last question. 🙂

Attached the sample workbook.

I use this code to copy all rows from sheet1 if in sheet1 column A there is a match with the value of  sheet2 cell B3 and paste it in to sheet2 to first empty row.

Sub Getdata()
Dim x As Integer
Dim Thisvalue As String

Sheets("Sheet1").Select
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on reference
Thisvalue = Cells(x, 1).Value
If Thisvalue = Sheets("Sheet2").Range("B3").Value Then
Range(Cells(x, 1), Cells(x, 7)).Copy
With Sheets("Sheet2")
.Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).PasteSpecial xlPasteAll
End With
End If
Next x
Sheets("Sheet2").Activate
End Sub

Hi Lajos,

After working on this I think it'll become very complicated to do exactly what you have asked for.  You say that you want 3 emails, 1 of these will be sent to both instructor1 and instructor2.  But you only know that this is the case by looking at your example table above.  There's no way for the code to know ahead of time that both instructor1 and instructor2 will be 'paired up' for the same course.

What if in your 3rd row, the instructors are instructor1 and instructor3?  You're going to end up sending a separate email to instructor1 for that request.

Basically to do what you ask would require reading the first email address from row1 then going through all rows looking for the same address.  Then coming back to row 1 and looking for the 2nd email address through all rows again.  It quickly gets difficult to track all possible combinations of email addresses.

In the attached workbook I have altered your example data a little and added some of my own.  Hopefully you will see what I mean.

So, what I have written is to send 1 email per row.

In the code is a variable called DisplayEmail.  This is set to True and will display each email before it is sent.  You will need to manually press Send.

This is for testing purposes so you can satisfy yourself that things work.  Once you are happy with this, change the value of DisplayEmail to false and they will not be displayed on your screen anymore, they will be automatically sent.

regards

Phil

That's awesome Phil, thank you for the solution (and Lajos thank you for asking the question!) I think this will be very useful.

I assume that you cannot also send the email with the spreadsheet as an attachment!?

Thanks Adrian.

Yes you can attach the workbook (or sheet).  Do you want it sent as an Excel file or as a PDF?

Phil

Oh handy!
I would be looking to send as the excel file itself 🙂

Just spotted your article in the newsletter - that is perfect thank you!!

no worries