How to create a time trigger that would send the mails every week

Question

I would like to ask you for a favour. I got a spreadsheet with code that sends an email if the cell (I3) contains a txt "YES".

Basically, if cell (J3) is empty then (I3) return the value "YES", then the code sends an email to addresses in cell (B3) once it's done it, the date appears to the cell (J3) and the value in (I3) changes to "NO". So on the next occasion the code knows that no emails needs to be send to to particular person.

I got this code of the internet. Done a little modification to the code to suit the sheet1. I'm very new to this, please be patient with me.

In cell (C3) I have the start date, cell (H3) the finish/due date. I would like my spreadsheet to send emails automatically without me opening the workbook. I would like a time trigger that would send emails if particular task is due in 30 days and if an email could be generated each monday until it reaches 0 days and then one email for overdue - 5.

Not sure if the cell (I3) or (J3) could be still in use.

I hope I explained everything clearly.

 Dim uRange
 Dim lRange
 Dim BCell As Range
 Dim iBody As String
 Dim iTo As String
 Dim iSubject As String
 Dim DaysOverdue

 Public Sub SetEmailParams()

 Set uRange = Sheet1.Range("I2")
 Set lRange = Sheet1.Range("I" & Rows.Count).End(xlUp)

 iBody = Empty
 iSubject = Empty
 iTo = Empty

 For Each BCell In Range(uRange, lRange)

 If BCell.Value = "YES" Then

 If DateDiff("d", Format(Now(), "dd/mm/yyyy"), Format(Range("G3"), 
"dd/mm/yyyy")) <= 0 Then

 DaysOverdue = DateDiff("d", Format(BCell.Offset(0, -6)), 
 Format(BCell.Offset(0, -1)))

 iTo = BCell.Offset(0, -7).Value
 iSubject = "Reminder"
 iBody = "The job assigned to you under this describtion - " & 
 BCell.Offset(0, -4) & " in the name of " & BCell.Offset(0, -3) & " for the 
 confirmation date of " & BCell.Offset(0, -1) & " is due " & DaysOverdue & " 
 days."

 SendEmail

 BCell.Offset(0, 1).Value = Now()

 End If

 End If

 Next BCell

 End Sub

 Private Sub SendEmail()

 Dim OutApp As Object
 Dim OutMail As Object
 Dim strbody As String

 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(0)

 On Error Resume Next
 With OutMail
 .To = iTo
 .CC = ""
 .BCC = ""
 .Subject = iSubject
 .Body = iBody
 'You can add a file like this
 '.Attachments.Add ("C:\test.txt")
 .Send 'or use .Send to automatically send without displaying
 End With
 On Error GoTo 0

 Set OutMail = Nothing
 Set OutApp = Nothing

End Sub

If you want to run your code when the workbook is closed then you'd need to use something like Windows Task Scheduler. https://stackoverflow.com/questions/22771185/how-to-set-recurring-schedule-for-xlsm-file-using-windows-task-scheduler — Tim Williams, Mar 12 '19 at 15:06
@Redas Valantina Your comments suggest that it may not be possible to adopt task scheduler option due to company policies. In such a situation you may like to consider `MailItem.DeferredDeliveryTime Property (Outlook)` of `MailItem Object`. However it is much simpler if `excel vba` is used with `Outlook instance` as programming becomes simpler. You are not planning hourly mails so `Workbook_open` event once a day should not be a problem. If you move all tasks to `Outlook VBA` it may be able to do all such tasks but it would lose flexibility of Excel as `Recipients` are to be criteria based. — skkakkar, Mar 13 '19 at 04:42

score 0 · Answer 1 · answered Mar 12 '19 at 15:08

0

For sending automatic mails you can use SendInBlue APIs or mail gun
For converting excel sheet use sheetjs

i hope it helps

answered Mar 12 '19 at 15:08

Engineer

1,243
11
18

Will have a look at it. Thank you for the info. – Redas Valantinas Mar 12 '19 at 15:24

Raul Guerrero · Answer 2 · 2019-03-12T15:21:45.877

0

You could create a BAT FILE, that open this workbooks and when the workbooks has open then run a Auto_Open macro that reads all cells content.

In ThisWorkbook write this code:

Private Sub Workbook_Open()

    MsgBox "Welcome"

End Sub

This is a example of the BAT file i mentioned before:

1.- Open a notepad

2.- Write this:

start Excel.exe "C:\Temporal\TEST.xlsm"

3.- Save it as MyBat.bat

4.- Go to Panel Control --> Administrative tools --> Task Scheduler --> Create a Basic Task

5.- Set the time you want to execute this bat file!

I hope this works for you! Cheers!

edited Mar 12 '19 at 15:21

answered Mar 12 '19 at 15:13

Raul Guerrero

388
2
10

Hi Raul, thanks for that. I use my company's computer that I work for. So, I wouldn't have an admin rights for this laptop. Thanks very much anyway ;) – Redas Valantinas Mar 12 '19 at 15:22
This means you are not authorized to do the work you are describing here? – Mar 12 '19 at 15:24
Not really, it's my own project. Trying to make something different for seniors to use for the project we're in. I've got many spreadsheets done using Gsheets. But this one wanted to create in MS excel. It's just I don't have the admin rights for this laptop as the head office is looking after/at computer security very highly – Redas Valantinas Mar 12 '19 at 15:40

score 0 · Answer 3 · answered Mar 13 '19 at 07:33

Referring to my comments an Example of using Mail.DeferredDeliveryTime is given below. This sample sends every email with a certain subject the next Monday at 8 o'clock in the morning.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
  Dim Mail As Outlook.MailItem
  If TypeOf Item Is Outlook.MailItem Then
    Set Mail = Item
    If Mail.Subject = "sample" Then
      Mail.DeferredDeliveryTime = GetNextWeekday(vbMonday) & " 08:00 AM"
    End If
  End If
End Sub

Private Function GetNextWeekday(ByVal DayOfWeek As VbDayOfWeek) As Date
  Dim diff As Long
  diff = DayOfWeek - Weekday(Date, vbSunday)
  If diff > 0 Then
    GetNextWeekday = DateAdd("d", diff, Date)
  Else
    GetNextWeekday = DateAdd("d", 7 + diff, Date)
  End If
End Function

How to create a time trigger that would send the mails every week

3 Answers3