Forum

VBA code to send em...
 
Notifications
Clear all

VBA code to send emails to multiple email adresses with outlook

11 Posts
3 Users
0 Reactions
211 Views
(@lalicapri)
Posts: 4
Active Member
Topic starter
 

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 [email protected], [email protected]
01-01-2020 StudL2 StudF2 Course2 10-01-2020 13-01-2020 [email protected]
01-01-2020 StudL3 StudF3 Course1 10-05-2020 12-05-2020 [email protected], [email protected]
01-01-2020 StudL4 StudLF4 Course3 10-06-2020 12-06-2020 [email protected]
01-01-2020 StudL1 StudLF1 Course4 02-05-2020 03-05-2020 [email protected], [email protected]

 

Now I need the code if I click send emails button only once it executes 3 emails to the instructors like

 email1 to [email protected], [email protected] 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 [email protected] 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 [email protected] 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.

 
Posted : 27/02/2020 6:03 pm
Philip Treacy
(@philipt)
Posts: 1629
Member Admin
 

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 

 
Posted : 28/02/2020 10:10 pm
(@lalicapri)
Posts: 4
Active Member
Topic starter
 

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. 🙂

 
Posted : 29/02/2020 9:37 am
(@lalicapri)
Posts: 4
Active Member
Topic starter
 

Attached the sample workbook.

 
Posted : 29/02/2020 9:53 am
(@lalicapri)
Posts: 4
Active Member
Topic starter
 

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

 
Posted : 29/02/2020 5:31 pm
Philip Treacy
(@philipt)
Posts: 1629
Member Admin
 

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

 
Posted : 02/03/2020 9:50 pm
(@adrianutas)
Posts: 16
Active Member
 

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!?

 
Posted : 11/03/2020 6:11 am
Philip Treacy
(@philipt)
Posts: 1629
Member Admin
 

Thanks Adrian.

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

Phil

 
Posted : 11/03/2020 8:06 pm
(@adrianutas)
Posts: 16
Active Member
 

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

 
Posted : 11/03/2020 9:09 pm
(@adrianutas)
Posts: 16
Active Member
 

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

 
Posted : 12/03/2020 8:32 pm
Philip Treacy
(@philipt)
Posts: 1629
Member Admin
 

no worries

 
Posted : 12/03/2020 9:49 pm
Share: