Assistance Required - Using VBA to send reminder emails. RRS feed

  • General discussion

  • Hi Experts!

    I am new to VBA and currently I am exploring using VBA to help with sending reminder by just clicking one button. I have finished creating the coding for sending email via outlook but now I couldn't fix certain issue as follow:

    - To send reminder email for certain date. Example if the due date is today,  the program can auto detect/lookup the due date with today date in that column and send out the reminder email without sending email for other dates.
    - To a command button which by clicking it, it will prompt the system to send email.

    Below is the excel listing and VBA code

    Column 1 is Name

    Column 2 is Prof

    Column 3 is Email

    Column 4 is Due Date


    Sub SendMail()

    Dim OlApp As Outlook.Application
    Dim OlMail As Outlook.MailItem

    For i = 2 To 4

    Set OlApp = New Outlook.Application
    Set OlMail = OlApp.CreateItem(olMailItem)

    With OlMail
    .To = Cells(i, 3).Value
    .Subject = "Oral report Submission for " & Cells(i, 1).Value
    .Body = "Dear " & Cells(i, 2).Value & "," & vbNewLine & vbNewLine & "This is a gentle reminder that student " & Cells(i, 1).Value & " oral report is going to due on " & Cells(i, 4).Value

    End With

    Set OlMail = Nothing
    Set OlApp = Nothing


    End Sub

    Your Help is greatly appreciated!
    Saturday, June 11, 2016 1:03 PM

All replies

  • Sub ReminderMails() Dim lRow As Long Dim LastRow As Long Dim varrTmp As Variant Dim i As Long Dim OlApp As Outlook.Application Dim OlMail As Outlook.MailItem Dim strTo As String Dim strSubj As String Dim strBody As String LastRow = Cells(Rows.Count, "A").End(xlUp).Row For lRow = 2 To LastRow If Cells(lRow, 4).Value = Date Then i = i + 1 If IsEmpty(varrTmp) Then ReDim varrTmp(1 To 4, 1 To i) Else ReDim Preserve varrTmp(1 To 4, 1 To i) End If varrTmp(1, i) = Cells(lRow, 1) varrTmp(2, i) = Cells(lRow, 2) varrTmp(3, i) = Cells(lRow, 3) varrTmp(4, i) = Cells(lRow, 4) End If Next lRow If IsEmpty(varrTmp) Then MsgBox "There are no messages to send.", vbInformation Else If MsgBox("There are " & UBound(varrTmp, 2) & " messages to send." & vbLf & _ "Do you want to send them?", _ vbQuestion + vbYesNo + vbDefaultButton1) = vbYes Then Set OlApp = New Outlook.Application For lRow = 1 To UBound(varrTmp, 2) Set OlMail = OlApp.CreateItem(olMailItem) strTo = varrTmp(3, lRow) strSubj = "Oral report Submission for " & varrTmp(1, lRow) strBody = "Dear " & varrTmp(2, lRow) & "," & String(2, vbNewLine) & _ "This is a gentle reminder that student " & varrTmp(1, lRow) & _ " oral report is going to due on " & varrTmp(4, lRow) Call SendMail_1(OlMail, strTo, strSubj, strBody) Next lRow OlApp.Quit Set OlApp = Nothing MsgBox "Ready", vbInformation End If End If End Sub

    Sub SendMail_1(OlMail As Outlook.MailItem, strTo As String, strSubject As String, strBody As String) With OlMail .To = strTo .Subject = strSubject .Body = strBody .Display 'comment '.Send 'uncomment End With End Sub


    • Edited by Artik_ Wednesday, June 22, 2016 2:18 AM
    Wednesday, June 22, 2016 2:17 AM