Limiting a script to run once per day

[JSR]

Good morning everyone,

I'm hoping to get some advice on a section of code, and whether or not it is possible to include a check to prevent the script running twice within a certain time period? I'm a newcomer to VBA and to these forums, so please forgive me if I am making any glaring mistakes. I have assembled/snatched the below code from other help threads here, and the odd tutorial out on the wider web, and it seems to work for everything I need so far:


Option Explicit
Sub AR_EXAMPLE(olItem As MailItem)
Dim vText As Variant
Dim sText As String
Dim sAddr As String
Dim vItem As Variant
Dim i As Long
Dim olOutMail As MailItem
Const strTemplate As String = "file path to email template oft"

With olItem
sText = olItem.Body
vText = Split(sText, Chr(13))
sAddr = "***xx" 'Recipient email address
Set olOutMail = CreateItemFromTemplate(strTemplate)
With olOutMail
.To = sAddr
.Send
End With
End With
lbl_Exit:
Set olOutMail = Nothing
Exit Sub
End Sub

What I am trying to do here:
I work for a company providing appliances to a major chain outlet. These outlets must have working services in order to remain open for business, so it's a large part of our business model to ensure reliable fault reports whenever something goes wrong. Our appliances automatically send out a fault message by email to us whenever something happens, though the message is basic and of no use to the end user directly.
The above code is intended to automatically detect whenever one of these fault emails is received and, depending on the location of the appliance, send a reply using a template to the relevant parties for that store. I cannot use the inbuilt out-of-office reply system for this because the "reply to" address is different from the sender address. I repeat this code many times over with different subroutine names, changing the recipient address depending on the source of the message.

This works perfectly so far for what I need, however I have an issue wherein we sometimes get frequent 'nuisance faults', i.e. multiple fault reports might be received during one single breakdown. The above code will react to each fault report and send another email out to the store, which will quickly get annoying for them and devalue the entire point of the notification. As a solution, I am wondering if there any way to incorporate a check to prevent the above code from running multiple times within a set period, e.g. a day.

Could this be done with a simple IF statement, checking to see if any messages have already been sent to the recipient address before processing the rest of the code, and skipping it if so?

I greatly appreciate any help!

Edit: This is using an Outlook 2016 client on Win 7 (x86) Professional, for a (shared) exchange email account. We have a machine running 24/7 with this email box opened so that the script can always be running to process new messages.

[gmayor]

Simple is relative, but you can check whether an e-mail has been sent to an address 'today' by using the following function to check the address you are replying to

Function IsSent(strAddress As String) As Boolean
Dim olItems As Outlook.Items
Dim olItem As Outlook.MailItem
Dim olRecipients As Outlook.Recipients
Dim olRecip As Recipient
Dim olPA As Outlook.PropertyAccessor
Dim i As Long, j As Long
Const PR_SMTP_ADDRESS As String = _
"http://schemas.microsoft.com/mapi/proptag/0x39FE001E"
Set olItems = Session.GetDefaultFolder(olFolderSentMail).Items
olItems.Sort "[Received]", True
j = 0
For i = 1 To olItems.Count
Set olItem = olItems(i)
If Format(olItem.ReceivedTime, "yyyymmdd") >= _
Format(Date, "yyyymmdd") Then
j = j + 1
Else
Exit For
End If
Next i
For i = j To 1 Step -1
Set olItem = olItems(i)
Set olRecipients = olItem.Recipients
For Each olRecip In olRecipients
Set olPA = olRecip.PropertyAccessor
If olPA.GetProperty(PR_SMTP_ADDRESS) = strAddress Then
IsSent = True
End If
Next olRecip
Next i
lbl_Exit:
Set olItems = Nothing
Set olItem = Nothing
Exit Function
End FunctionIf the function responds False it is OK to send your message.

[JSR]

That's excellent, thankyou very much!

Might you be able to help me incorporate this function into the code above?

[gmayor]

You need to setup a condition to check the address, which appears to be fixed e.g.


Sub AR_EXAMPLE(olItem As MailItem)
Dim vText As Variant
Dim sText As String
Dim sAddr As String
Dim vItem As Variant
Dim i As Long
Dim olOutMail As MailItem
Const strTemplate As String = "file path to email template oft"

sAddr = "***xx" 'Recipient email address
If IsSent(sAddr) = False Then
With olItem
sText = olItem.Body
vText = Split(sText, Chr(13))
Set olOutMail = CreateItemFromTemplate(strTemplate)
With olOutMail
.To = sAddr
.Send
End With
End With
End If
lbl_Exit:
Set olOutMail = Nothing
Exit Sub
End Sub

[JSR]

Thanks, this is all very helpful for me. I will continue to experiment and learn from here!

[gmayor]

Note that I took you at your word that your macro did what you wanted. Having looked at it since, I am not sure what it is that you are trying to do in relation to the original message, which does not seem to be reflected in any way in your new message. In particular, you have
sText = olItem.Body
vText = Split(sText, Chr(13))but have not used either sText or vText to create your message?

[JSR]

Yes, it's likely I have a good few redundant lines in there - I'm still a complete beginner with VBA I'm afraid, and this code was mostly borrowed and meshed together from other questions that have been asked here before. I suspect I removed the code that called on those functions, but not the lines themselves. There is probably a more efficient way of doing it all - but running the above code as a script, alongside rules to detect incoming messages based on subject/body text, does successfully load a template and email it out to a different recipient as I need. Any needless or shoddy coding hasn't caused problems thus far!

[gmayor]

Outlook is not the easiest place to start coding, as it is poorly documented, but as long as the code does what you want without causing problems elsewhere, that is the main thing.