Outlook VBA - Run a code every half an hour

Question

I want run a specific code in outlook(VBA) every half an hour.

Also the outlook user should not get disturbed when the code runs. It should run in back-end only.

There is an event called Application_Reminder. It runs when a at each occurrence of reminder in outlook. But this still involves user interaction. I want a complete back end procedure.

Answer 1

I've updated the code slightly from Max to 64 bit and change ActivateTimer so that it can take in less than 1 e.g. 0.5 min

'
'  64 Bit Outlook
'
Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As LongPtr, ByVal lpTimerfunc As LongPtr) As LongPtr
Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As LongPtr

Public TimerID As Long 'Need a timer ID to eventually turn off the timer. If the timer ID <> 0 then the timer is running

'
'  Code to be added here
'
Public Sub TriggerTimer(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idevent As Long, ByVal Systime As Long)
  ' Debug.Print "The TriggerTimer function has been automatically called!"
    If idevent = TimerID Then
        Debug.Print "The TriggerTimer function has been automatically called!"
    End If
End Sub

'
'
'
Public Sub DeactivateTimer()
Dim lSuccess As LongPtr
  lSuccess = KillTimer(0, TimerID)
  If lSuccess = 0 Then
    Debug.Print "The timer failed to deactivate."
  Else
    TimerID = 0
    Debug.Print "Timer deactivated successfully."
  End If
End Sub

'
'
'
Public Sub ActivateTimer(ByVal dMinutes As Double)
  Dim nMinutes As Long
  nMinutes = CLng(dMinutes * 1000 * 60) 'The SetTimer call accepts milliseconds, so convert to minutes
  If TimerID <> 0 Then Call DeactivateTimer 'Check to see if timer is running before call to SetTimer
  TimerID = SetTimer(0, 0, nMinutes, AddressOf TriggerTimer)
  If TimerID = 0 Then
      Debug.Print "The timer failed to activate."
  Else
      Debug.Print "Timer activated."
  End If
End Sub

Answer 2

For Win64, I needed to change it to this:

Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongLong, ByVal nIDEvent As LongLong, ByVal uElapse As LongLong, ByVal lpTimerfunc As LongLong) As LongLong
Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongLong, ByVal nIDEvent As LongLong) As LongLong

Public TimerID As LongLong 'Need a timer ID to eventually turn off the timer. If the timer ID <> 0 then the timer is running

Public Sub TriggerTimer(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idevent As Long, ByVal Systime As Long)
  MsgBox "The TriggerTimer function has been automatically called!"
End Sub


Public Sub DeactivateTimer()
Dim lSuccess As LongLong
  lSuccess = KillTimer(0, TimerID)
  If lSuccess = 0 Then
    MsgBox "The timer failed to deactivate."
  Else
    TimerID = 0
  End If
End Sub

Public Sub ActivateTimer(ByVal nMinutes As Long)
  nMinutes = nMinutes * 1000 * 60 'The SetTimer call accepts milliseconds, so convert to minutes
  If TimerID <> 0 Then Call DeactivateTimer 'Check to see if timer is running before call to SetTimer
  TimerID = SetTimer(0, 0, nMinutes, AddressOf TriggerTimer)
  If TimerID = 0 Then
    MsgBox "The timer failed to activate."
  End If
End Sub

Answer 3

Correct for upper answer for 64-bit:

Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongLong, ByVal nIDEvent As LongLong, ByVal uElapse As LongLong, ByVal lpTimerfunc As LongLong) As LongLong
Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongLong, ByVal nIDEvent As LongLong) As LongLong

Public TimerID As LongLong 'Need a timer ID to eventually turn off the timer. If the timer ID <> 0 then the timer is running

Public Sub TriggerTimer(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idevent As Long, ByVal Systime As Long)
  MsgBox "The TriggerTimer function has been automatically called!"
End Sub


Public Sub DeactivateTimer()
Dim lSuccess As LongLong              '<~ Corrected here
  lSuccess = KillTimer(0, TimerID)
  If lSuccess = 0 Then
    MsgBox "The timer failed to deactivate."
  Else
    TimerID = 0
  End If
End Sub

Public Sub ActivateTimer(ByVal nMinutes As Long)
  nMinutes = nMinutes * 1000 * 60 'The SetTimer call accepts milliseconds, so convert to minutes
  If TimerID <> 0 Then Call DeactivateTimer 'Check to see if timer is running before call to SetTimer
  TimerID = SetTimer(0, 0, nMinutes, AddressOf TriggerTimer)
  If TimerID = 0 Then
    MsgBox "The timer failed to activate."
  End If
End Sub

Answer 4

http://www.outlookcode.com/threads.aspx?forumid=2&messageid=7964

Place the following code in the ThisOutlookSession module (Tools->Macros->VB Editor):

Private Sub Application_Quit()
  If TimerID <> 0 Then Call DeactivateTimer 'Turn off timer upon quitting **VERY IMPORTANT**
End Sub

Private Sub Application_Startup()
  MsgBox "Activating the Timer."
  Call ActivateTimer(1) 'Set timer to go off every 1 minute
End Sub

Place the following code in an new VBA module

Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerfunc As Long) As Long
Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long 'Need a timer ID to eventually turn off the timer. If the timer ID <> 0 then the timer is running

Public Sub ActivateTimer(ByVal nMinutes As Long)
  nMinutes = nMinutes * 1000 * 60 'The SetTimer call accepts milliseconds, so convert to minutes
  If TimerID <> 0 Then Call DeactivateTimer 'Check to see if timer is running before call to SetTimer
  TimerID = SetTimer(0, 0, nMinutes, AddressOf TriggerTimer)
  If TimerID = 0 Then
    MsgBox "The timer failed to activate."
  End If
End Sub

Public Sub DeactivateTimer()
Dim lSuccess As Long
  lSuccess = KillTimer(0, TimerID)
  If lSuccess = 0 Then
    MsgBox "The timer failed to deactivate."
  Else
    TimerID = 0
  End If
End Sub

Public Sub TriggerTimer(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idevent As Long, ByVal Systime As Long)
  MsgBox "The TriggerTimer function has been automatically called!"
End Sub

Key points:

1) This timer function does not require that a particular window is open; it works in the background

2) If you don't deactivate the timer when the application closes it will likely crash

3) The example shows the timer being activated at startup, but it can just as easily be called by a different event

4) If you don't see the msgbox indicating that the timer was activated upon startup, your macro security is set too high

5) To have the timer deactivate after one iteration of the time interval add: If TimerID <> 0 Then Call DeactivateTimer after the msgbox statement in sub TriggerTimer

Someone else suggested

"one point to note, if you don't check if TimerID is the same as idevent in the TriggerTimer, you get every so often, and not the time you asked for."

Public Sub TriggerTimer(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idevent As Long, ByVal Systime As Long)
    'keeps calling every X Minutes unless deactivated
    If idevent = TimerID Then
        MsgBox "The TriggerTimer function has been automatically called!"
    End If
End Sub