VBA - Sending an email to different email addresses based on set parameters

[PaulFletcher]

Hi

I am completely lost with the following and really need some help please, not even sure where to start!

I am trying to create a macro which based on a set parameter (the date specified in a cell being within 3 days of todays date) that an email is generated.
This email would have the same Subject Field in all instances "Late Booking Notification" but would need to pull the value from one Cell and put this into the body of the email
Finally, based on a further cells value it would determine which email address(es) it then sends to.

 

[Norie]

Paul

How is your data organised?

Will it be multiple emails being sent?

 

[PaulFletcher]

Thanks you for your reply.

The data is organised as a data table; however is droppping through to this using a button and macro from a front "input" page.
There is only one email which needs to be sent for each time it occurs, however this would be multiple emails throughout the day.

Thansk

Paul

 

[PaulFletcher]

Data table as follows:

Dealership
WIP
Booking Type
Date Booked
MyService Option
Date
Week Commencing
Month
Year
Agent Name

 

[Norie]

Are those the table headers?

Are you actually using a table?

 

[PaulFletcher]

Those are the headers - it is a table however pasting it in didn;t look too good...

 

[PaulFletcher]

OK - so I believe it may be easier to work this from the input sheet than from the data sheet, although happy to be advised otherwise.

On the input sheet I contain the date the booking is made for in Cell "B11" - this could be used to work out if the date is within 3 days from TODAY
On the input sheet I contain the value which will determine where to send the email in cell B5

This is what I have so far... I am just not sure how to get this to determine that the date is within 3 days of today's date to send or not send an email as well as determine who to send the email too...

Sub Sendemail()
 
Dim bookings As Worksheet
 
Set bookings = Worksheets("Bookings Data")
 
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
 
Dim Mail_Object, Mail_Single As Variant
 
Email_Subject = "Late booking notification - WIP " & ThisWorkbook.Sheets("Input").Range("B7") & "- Date of Booking " & ThisWorkbook.Sheets("Input").Range("B11")
Email_Send_To = ""
Email_Cc = ""
Email_Bcc = ""
Email_Body = "Thanks"
 
On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.send
End With
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

 

[PaulFletcher]

Further update; now determining whetehr to send an email based on Date <= 3
Just need to know how to determine where to send it based on a list of names?

Sub LateBookingEmail()


Dim r As Range
Dim cell As Range


Set r = Range("B11")


For Each cell In r
    If cell.Value <= Date + 3 Then
    
    Dim Email_Subject, Email_Send_From, Email_Send_To, _
        Email_Cc, Email_Bcc, Email_Body As String
    Dim Mail_Object, Mail_Single As Variant


    Email_Subject = "Late booking notification - WIP " & ThisWorkbook.Sheets("Input").Range("B7") & "- Date of Booking " & ThisWorkbook.Sheets("Input").Range("B11")
    Email_Send_To = "paul.fletcher@sandown-mercedes.co.uk"
    Email_Cc = "florin.trifa@sandown-mercedes.co.uk"
    Email_Bcc = ""
    Email_Body = ""


On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.send
End With


End If


Next


debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub