Log In
Register
Home
Active Member
February 27, 2020
Offline
Hello to everyone. I am just started using VBA and I need your help.
Scenario: I am admin in a scoolhaus and receive several emails day by day from students with different course requests. There are courses which are thaught by same instructors. I need to send emails to the instructors to have confirmation for the requests. But instead of sending single emails with single requests I would like to send it like one email to each instructor but with all the requests in 1 email which are belong to him/her.
In sheet1 I have
A B C D E F G
| Request_Date | Last_Name | First_Name | Course_Title | Reqest_From | Request_To | Instructors_email_adresses |
In sheet2
A1=request date B1=01-01-2020 get data button send email button
If I type to B1 01-01-2020 and click get data button i retrieve data from sheet1 and insert it to sheet2 range A3:G100 like
| Request_Date | Last_Name | First_Name | Course_Title | Reqest_From | Request_To | Instructors_email_adresses |
| 01-01-2020 | StudL1 | StudF1 | Course1 | 10-05-2020 | 12-05-2020 | Instructor1@mail.com, Instructor2@mail.com |
| 01-01-2020 | StudL2 | StudF2 | Course2 | 10-01-2020 | 13-01-2020 | Instructor3@mail.com |
| 01-01-2020 | StudL3 | StudF3 | Course1 | 10-05-2020 | 12-05-2020 | Instructor1@mail.com, Instructor2@mail.com |
| 01-01-2020 | StudL4 | StudLF4 | Course3 | 10-06-2020 | 12-06-2020 | Instructor4@mail.com |
| 01-01-2020 | StudL1 | StudLF1 | Course4 | 02-05-2020 | 03-05-2020 | Instructor1@mail.com, Instructor2@mail.com |
Now I need the code if I click send emails button only once it executes 3 emails to the instructors like
email1 to Instructor1@mail.com, Instructor2@mail.com with the emailbody
"Please confirm if you can provide the following trainings:
StudL1 StudF1 Course1 from 10-05-20 to 2012-05-2020
StudL3 StudF3 Course1 from 10-05-20 to 2012-05-2020
StudL1 StudLF1 Course4 from 02-05-2020 to 03-05-2020
"Thanks in advance"
email2 to Instructor3@mail.com with the emailbody:
"Please confirm if you can provide the following trainings:
StudL2 StudF2 Course2 from 10-01-2020 to 13-01-2020
"Thanks in advance"
email3 to Instructor4@mail.com with the emailbody:
"Please confirm if you can provide the following trainings:
StudL4 StudLF4 Course3 from 10-06-2020 to 12-06-2020
"Thanks in advance"
Is that possible somehow? Thank you in advance for all your advices.
October 5, 2010
Offline
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
Active Member
February 27, 2020
Offline
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. 🙂
Active Member
February 27, 2020
Offline
Attached the sample workbook.
Active Member
February 27, 2020
Offline
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
October 5, 2010
Offline
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
July 13, 2016
Offline
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!?
October 5, 2010
Offline
Thanks Adrian.
Yes you can attach the workbook (or sheet). Do you want it sent as an Excel file or as a PDF?
Phil
July 13, 2016
Offline
Oh handy!
I would be looking to send as the excel file itself 🙂
1 Guest(s)
