CODEWITHSUNDEEP
excelvbaoutlook
Hayyache
Hayyache

Reputation: 11

Send email reminders based on Excel values in two workbooks

I am attempting to send automated emails to staff when they are within 45 days of any particular training's expiration date, without having to open the file to run macros (if possible). Also, to have this done without an email preview for each training, as hundreds may pop up each time the macro is run.

I want to pull the name of the training that determines the email body from the values in one of my columns.

I want to pull the expiration dates from a separate Excel file that is a record of training dates.

I managed to queue email previews for trainings within 45 days of the expiration date, however the code allows Excel to send reminders on trainings that are already expired.

I would like Excel to stop once the expiration date has been reached. I attempted: If mydate2 - datetoday2 <= 45 Then. This works but will send emails to inactive staff but still have a record of their trainings in the training record file.

I tried to pull the training names from a column and have them autofill in the email body. For instance the email will say "Training due to Expire:" and then have excel fill the training name based on the values in column 4.

Sub datesexcelvba()
Dim myapp As Outlook.Application, mymail As Outlook.MailItem

Dim mydate1 As Date
Dim mydate2 As Long
Dim datetoday1 As Date
Dim datetoday2 As Long

Dim x As Long
lastrow = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For x = 2 To lastrow

    mydate1 = Cells(x, 5).Value
    mydate2 = mydate1

    Cells(x, 11).Value = mydate2

    datetoday1 = Date
    datetoday2 = datetoday1

    Cells(x, 12).Value = datetoday2

    If mydate2 - datetoday2 <= 45 Then

        Set myapp = New Outlook.Application
        Set mymail = myapp.CreateItem(olMailItem)
        mymail.To = Cells(x, 6).Value

        With mymail
            .Subject = "Training Expiration Reminder"
            .Body = "Please contact your supervisor to enroll in the next possible recertification class. Training Expiring:" value=x3

            .Display
            '.send
        End With

        Cells(x, 7) = "REMINDER SENT"
        Cells(x, 7).Font.Bold = True

        Cells(x, 13).Value = mydate2 - datetoday2

    End If
Next

Set myapp = Nothing
Set mymail = Nothing
End Sub

Upvotes: 1

Views: 302

Answers (1)

Cyril
Cyril

Reputation: 6829

Posting comment as answer so this can be listed as Answered.

.Body = "..." value=x3

needs to be 

.Body = "..." & cells(x,3).value

, or similar to your context.

Regarding your question about tips/points, don't be afraid to compartmentalize your code, so it's easier to handle the sections.

You have 2 or 3 sections of your code, and you can do something like the below to call the different sections: 

Public Sub execute()
    If ActiveSheet.name <> "SQDC" Then Exit Sub
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlManual
    End With
    '''
    task_one
    task_two
    task_three
    '''
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlAutomatic
    End With
End Sub

Regarding using Outlook and Excel back and forth, save your key items as variables so Outlook can do what it needs to do without searching for cell references.

'at the top of the module, outside of the subroutine:
public email_address as string, employ_name as string

'where If mydate2 - datetoday2 <= 45 Then is TRUE
email_address = cells(x,6).value
employ_name = cells(x,3).value

Then you can use those in the email portion of your code. It is assumed you are compartmentalizing the excel activities from the outlook activities, so the global variables are there so you can define them in one subroutine and utilize them in another.

One thing that helped me with getting to mediocrity in VBA is reading other people's questions and attempting to answer them (even if just in your head), by finding the correct terms, etc. Eventually you will have enough knowledge to struggle by with the best of 'em!

Upvotes: 1

Related Questions