Automatic email on set date, referencing another cell for email address

[Mikeymike_W]

Hi All,

Thanks in advance for any help you can provide.

I have quite a large spreadsheet.
I want to set up an auto email reminder (using outlook) so I can go back and check on the status of certain files I keep track of.

I have a userform that fills out certain information and on this I have a checkbox that fills a cell with todays date + 10 working days.
I want the email to be sent once this date is reached.

There is another cell within the same row with the initials of whomever is looking after the file.
I have set up a table in another sheet which has the email address set up for each set of initials.

Is there a way for the code to send an email when the date is reached, find the initials of the user on the same line and coinciding email address?

Sheet 1
Column A = Users Initials (3 users in total)
Column B= Date for the email to be sent

Sheet 2
Column A = Users Initials (3 users in total)
Column B = Users Email address

Big thanks for your help,

Mike

 

[Mikeymike_W]

So I tried your code but there are unexpected errors.

Getting a runtime error 1004, "Unable to get the Vlook property of the worksheet function class".
When I debug it highlights:

stEmail = Application.WorksheetFunction.VLookup(Range("A" & c.Row), Sheet2.Range("A:B"), 2, False)

This part of the code worked fine before hand so not sure what the issue is??

Seems like it should be simple enough :/

 

[Mikeymike_W]

SUCCESS!!!!!!

Turns out I was being a bit of a plonker and hadn't completed the required fields in the spreadsheet that the Vlookup relies on.

There was also a missing End If statement after "If Sheet2.Range("C1") = Int(Now()) Then Exit Sub"

dchaney I cant say thanks enough, thanks very much for all your help!!!!