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

 

[dchaney]

Add the following to your "ThisWorkbook" module within the VBA editor and each time the workbook is open it will look at the date in column B and send out an email if the date = today...

Now, if you open this 15 times in a day, it will send out 15 emails...

Private Sub Workbook_Open()

Dim objOutlook As Object, objEmail As Object

For Each c In ActiveSheet.Range("B:B")

    If c = Int(Now()) Then
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objEmail = objOutlook.CreateItem(0)

        stEmail = Application.WorksheetFunction.VLookup(Range("A" & c.Row), Sheet2.Range("A:B"), 2, False)
        myFile = Range("C" & c.Row) 'CHANGE "C" TO THE COLUMN CONTAINING THE FILENAME TO BE REVIEWED
        
            With objEmail
                .Subject = "Please perform your file check on " & myFile
                .Body = "Please perform the scheduled file maintenance on " & myFile
                .To = stEmail
                .Send
            End With
    
    Set objEmail = Nothing
    Set objOutlook = Nothing

    End If
Next

End Sub

 

[Mikeymike_W]

Hi dchaney,

It worked a treat thanks

Do you know of any way to prevent the multiple emails?

Thanks again,

Mike

 

[dchaney]

Off the top of my head (since I am still not an expert at VBA) I would add a "bookmark" type thing in my code. Basically add the date to a hidden cell or a cell on a different page and have VBA look at that cell prior to running, like the following. This will add a today's date to Cell C1 on Sheet2, after it runs, when you open it again the first thing it will do is look at Cell C1 and if it is today's date it exits the sub.

Private Sub Workbook_Open()

Dim objOutlook As Object, objEmail As Object

If Sheet2.Range("C1") = Int(Now()) Then Exit Sub

    For Each c In ActiveSheet.Range("B:B")
    
        If c = Int(Now()) Then
        
        Set objOutlook = CreateObject("Outlook.Application")
        Set objEmail = objOutlook.CreateItem(0)
    
            stEmail = Application.WorksheetFunction.VLookup(Range("A" & c.Row), Sheet2.Range("A:B"), 2, False)
            myFile = Range("C" & c.Row) 'CHANGE "C" TO THE COLUMN CONTAINING THE FILENAME TO BE REVIEWED
            
                With objEmail
                    .Subject = "Please perform your file check on " & myFile
                    .Body = "Please perform the scheduled file maintenance on " & myFile
                    .To = stEmail
                    .Send
                End With
        
        Set objEmail = Nothing
        Set objOutlook = Nothing
    
        End If
    Next

    Sheet2.Range("C1") = Int(Now())

End Sub

 

[Mikeymike_W]

Hi dchaney,

Thanks for giving this some thought.

Not sure this would work as I rely on the email being sent using todays date so if it exited the sub beforehand then the email wouldn't be sent... I think?

Thanks,

Mike

 

[dchaney]

The way this works, when you open the file for the first time and it runs it will look at the second sheet and the date should be blank, so it will send out your emails, then it will add today's date to the second sheet. Then, when you open it later in the day it will look at the second sheet again, if the date is today's date it will not send emails, then when you open it tomorrow it will see that the date in C1 was yesterday and it will send emails..

 

[Mikeymike_W]

Hi dchaney,

Just had a thought.

What if there was a helper column?
when the initial email is sent it would fill the cell in this column with a true or false.
Then having a statement within the existing code which checks this column before sending the email. if false carry on with sending the email but if true then it just exits?

What do you think?
I would have no idea how to go about writing this into the code though.

Thanks again,

Mike

 

[dchaney]

Hi dchaney,

Just had a thought.

What if there was a helper column?
when the initial email is sent it would fill the cell in this column with a true or false.
Then having a statement within the existing code which checks this column before sending the email. if false carry on with sending the email but if true then it just exits?

What do you think?
I would have no idea how to go about writing this into the code though.

Thanks again,

Mike

Are the emails time specific or just date specific? Not sure if you seen my previous message as I think we were typing at the same time, but the Date cell idea is a helper column per-say, but if the time matters then we could do something for each row... let me know and I can look into it for ya...

 

[Mikeymike_W]

Sorry, yeah I didnt see your message but it sounds like you have covered what I was thinking

The time doesn't really matter as long as the email is sent on the specified date, I'll give your code a bash and see what happens.

Thanks again bud

 

[dchaney]

My pleasure