Help with auto emails

[Biglou1221]

Hi all,
I've searched for a few days now and have come to the conclusion that I know absolutely nothing about VBA/Coding. What I would like to do is set up two auto emails (from outlook) from the same worksheet to aid in staying on top of my tracker. Using the attached sample worksheet, I would like to send an auto email 30 day's prior to "due 1" to the email address in row "J" with the info from Row "B". i.e. the email would be something like:

"SFC Droopy,
Joe Schmoe has an evaluation due in 30 days. Please email your draft to me or 1SG Dopey as soon as possible.
Respectfully,
Who I. Am

and record the date the email was sent in row "K"


Secondly,
I would like to send ANOTHER email reminder to blah@blah.blah 15 days prior to "Due 2"
No "date notified for this" (unless it's needed to prevent recurring emails for the same row)

I've seen the tutorials on rondebruin.nl and tried for several hours but just ended up butchering it. Unfortunately, I won't be able to check back for until the morning, but your responses are greatly appreciated.

p.s. The book attached is on excel 2007, but Im currently on my home computer and my work computers have excel 2010 (and some have 2013) will this affect the outcome?

 

[chirayu]

I'll give this a try. Are the dates in your file YYYYMMDD or YYYYDDMM

 

[Biglou1221]

Thanks for your effort dates are yyyymmdd

 

[chirayu]

Needs to be run manually. Alternatively you could make another macro with an Application.OnTime event & tell it to run this macro at a specified time.

This will run through all rows in the file & create mail for each if criteria met. Sample file attached. Macro is able to run from Personal Workbook as well as .xlsm file. Please check if its correct.

Sub AutoMailer()

'Made by Chirayu Walawalkar @chandoo.org/forum on 14-Jul-2015

Dim Due1 As Date
Dim Due2 As Date
Dim MyText As String
Dim CurrFile As Workbook

'=================================================
'Ron De Bruin - modified by Chirayu Walawalkar
Dim OutApp As Object

Set OutApp = CreateObject("Outlook.Application")
'=================================================

Set CurrFile = ActiveWorkbook
Range("A2").Select

Do Until IsEmpty(ActiveCell)

    'Convert Due1 to Date format
    Due1 = _
    Right(Range("F" & ActiveCell.Row).Value, 2) & "-" & _
    MonthName(Mid(Range("F" & ActiveCell.Row).Value, 5, 2)) & "-" & _
    Left(Range("F" & ActiveCell.Row).Value, 4)
 
    'Convert Due2 to Date format
    Due2 = _
    Right(Range("G" & ActiveCell.Row).Value, 2) & "-" & _
    MonthName(Mid(Range("G" & ActiveCell.Row).Value, 5, 2)) & "-" & _
    Left(Range("G" & ActiveCell.Row).Value, 4)
 
    'Due1 - Todays Date = 30 Days? checker
    If Due1 - Date = 30 Then
     
        MyText = _
        Range("I" & ActiveCell.Row).Value & "," & vbCrLf & vbCrLf & _
        Range("B" & ActiveCell.Row).Value & _
        " has an evaluation due in 30 days. Please email your draft to me or " & _
        Range("L" & ActiveCell.Row).Value & _
        " as soon as possible." & vbCrLf & vbCrLf & _
        "Respectfully," & vbCrLf & _
        Range("M" & ActiveCell.Row).Value
     
'=========================================================================
        'Ron De Bruin - modified by Chirayu Walawalkar
        On Error Resume Next
        With OutApp.CreateItem(0)
            .to = Range("J" & ActiveCell.Row).Value 'abc@abc.com
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line" 'change this to whatever you want
            .Body = MyText
            .Display  'or use .Send to send the file
        End With
        On Error GoTo 0
'=========================================================================
     
        CurrFile.Activate
        ActiveCell.Offset(1, 0).Select
     
    'Due2 - Todays Date = 15 Days? checker
    ElseIf Due2 - Date = 15 Then
     
        MyText = _
        Range("I" & ActiveCell.Row).Value & "," & vbCrLf & vbCrLf & _
        Range("B" & ActiveCell.Row).Value & _
        " has an evaluation due in 15 days. Please email your draft to me or " & _
        Range("L" & ActiveCell.Row).Value & _
        " as soon as possible." & vbCrLf & vbCrLf & _
        "Respectfully," & vbCrLf & _
        Range("M" & ActiveCell.Row).Value
     
'=========================================================================
        'Ron De Bruin - modified by Chirayu Walawalkar
        On Error Resume Next
        With OutApp.CreateItem(0)
            .to = "blah@blah.blah" 'change this to whatever you want
            .CC = ""
            .BCC = ""
            .Subject = "Here Chicky Chicky" 'change this to whatever you want
            .Body = MyText
            .Display  'or use .Send to send the file
        End With
        On Error GoTo 0
'=========================================================================
     
        CurrFile.Activate
        ActiveCell.Offset(1, 0).Select
     
    Else
     
        ActiveCell.Offset(1, 0).Select
     
    End If
 
Loop


End Sub

 

[Biglou1221]

Ran a few tests and so far everything is good. Going to change all the values to what I need them to be and will get back if I have any issues. If you have the time, can you elaborate on the Application.Ontime event? I'm a complete noob at this, but what Im getting is that the tracker would check at a specified time everyday and send emails as appropriate. Would the spreadsheet need to be open during this time in order for the script to run? I appreciate all your help.

Also, would the script for the application.ontime would look like:

 Application.OnTime TimeValue("09:00:00"), "AutoMailer()"

I'm assuming the .ontime event would have to be placed in a separate module? Again, I appreciate your time and effort.

 

[chirayu]

You'll need to use a VBS script & run it through Windows Task Scheduler if you want it to run without opening the file. The Application.OnTime will need a definite path to the file that has the macro stored so its up to you how you store & use the AutoMailer with it. Look at the links below.

http://chandoo.org/forum/threads/au...ly-based-on-weekdays-and-specific-time.24035/

http://www.snb-vba.eu/VBA_Application.OnTime_en.html

Obviously if you try to automate that far the AutoMailer() macro will need to be modified a bit so that it'll open the file you want & work on it so start of the code would change to something like

Sub AutoMailer()

'Made by Chirayu Walawalkar @chandoo.org/forum on 14-Jul-2015

Dim Due1 As Date
Dim Due2 As Date
Dim MyText As String
Dim CurrFile As Workbook

'=================================================
'Ron De Bruin - modified by Chirayu Walawalkar
Dim OutApp As Object

Set OutApp = CreateObject("Outlook.Application")
'=================================================

Workbooks.Open _
Filename:="C:\Example Eval Tracker.xlsx" 'Change this to whatever

Set CurrFile = ActiveWorkbook
Range("A2").Select

The end of the code will change to close the file after work is done

Workbooks(CurrFile).Close savechanges:=False

End Sub

Easiest option if this is too complicated is to just open the file & run the code manually everyday at a set time.

 

[Biglou1221]

I will definitely look into this. Just got swamped so I don't know when I'll have a chance, but I appreciate all your help.