VBA code to send email on a specific date

[sn152]

Good Morning All,

Iam trying to write a vba code to send a mail from Userform. In the user form when I enter the ID in "Emp ID" field, it will automatically show the email id in the "Email ID" field.

Now what I am trying to do is, when the user enters a date in the textbox2 or textbox3 or textbox4 and click on submit button, the data will be automatically transferred to sheet1 and also an email needs to be sent on the latest date entered in these textboxes.

It would be great if anyone sould help me with this.

Thanks in advance!

 

[Luke M]

Check out the code Chandoo used for his birthday reminder, which uses a similar idea for sending email on certain day.
http://chandoo.org/wp/2010/10/26/birthday-reminder-template/

 

[sn152]

Hi Luke,

Thanks for replying. I already saw this thread. But it is different. I want the email to be sent based on the largest date given in the textboxes in the userform.

 

[Luke M]

If all the information from textboxes is being stored to the sheet, you could use something like
=MAX(A2:A4)
to get the largest date.

 

[sn152]

Hi Luke

When I enter the data in the userform and click submit it will be transferred to sheet1, Each row in sheet1 will have dates in column D, F and H. I want an email to be sent on the largest date in these columns for each row. How can we do this? Please help.

 

[Luke M]

Either pre-setup a helper column with this formula, or have the code write it:
=MAX(D2,F2,H2)

 

[sn152]

Yes I have a helper column now. And I user .DeferredDeliveryTime = Range ("K2") to send email on the date mentioned in the cell K2. But i have dates in each row of column K. What is the code to send email for all these rows.

 

[Luke M]

Roughly, it would look like this

With ActiveSheet
    'Define last row to look at
    lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
   
    'Loop through each cell in our range
    For Each c In .Range("K2:K" & lastRow)
        'Check if the date matches
        If c.Value = Date Then
            'Send email
            'Put code here
        End If
    Next c
End With