[RESOLVED] Outlook VB Macro - Deffered Delivery Time
Trying to write a macro for Outlook 2003 that will check the current date/time and either send the current email immediately, or will delay it until the next business day at 8:30am. I'm a VB rookie, but have put together the following code (which isn't compiling correctly) to show where I'm at:
Code:
Public Sub CheckSendTime()
Dim obj As Object
Dim Mail As Outlook.MailItem
Dim WkDay As String
Dim SendHour As Integer
Dim SendDay As Date
Dim SendTime As Date
SendDay = DateValue(Now)
SendTime = TimeValue(Now)
SendHour = Hour(Now)
If SendHour < 8 Then
SendHour = 8 - SendHour
SendTime = SendTime.AddHours(SendHour)
End If
If SendHour > 18 Then
SendHour = 32 - SendHour
SendTime = SendTime.AddHours(SendHour)
WkDay = Weekday(Today)
If WkDay = 1 Then SendDay = SendDay.AddDays(1)
End If
If WkDay = 7 Then SendDay = SendDay.AddDays(2)
End If
Set obj = Application.ActiveInspector.CurrentItem
If TypeOf obj Is Outlook.MailItem Then
Set Mail = obj
Mail.DefferedDeliveryTime = SendDay & SendTime
Mail.Send
End If
End Sub
Any help you can give to assist would be greatly appreciated. Thanks in advance!
Thread moved to Office Development/VBA forum (note that the "VB Editor" in Office programs is actually VBA rather than VB, so the VB6 forum is not really apt)
Believe I've figured out the problem(s). Here's the corrected code:
Code:
Public Sub CheckSendTime()
Dim obj As Object
Dim Mail As Outlook.MailItem
Dim WkDay As String
Dim MinNow As Integer
Dim SendHour As Integer
Dim SendDate As Date
Dim SendNow As String
'Set Variables
SendDate = Now()
SendHour = Hour(Now)
MinNow = Minute(Now)
WkDay = Weekday(Now)
SendNow = Y
'Check if Before 8am
If SendHour < 8 Then
SendHour = 8 - SendHour
SendDate = DateAdd("h", SendHour, SendDate)
SendDate = DateAdd("n", -MinNow, SendDate)
SendNow = N
End If
'Check if after 7PM
If SendHour > 19 Then 'After 7 PM
SendHour = 32 - SendHour 'Send a 8 am next day
SendDate = DateAdd("h", SendHour, SendDate)
SendDate = DateAdd("n", -MinNow, SendDate)
SendNow = N
End If
'Check if Sunday
If WkDay = 1 Then
SendDate = DateAdd("d", 1, SendDate)
SendNow = N
End If
'Check if Saturday
'If WkDay = 7 Then
' SendDate = DateAdd("d", 2, SendDate)
' SendNow = N
'End If
'Send the Email
Set obj = Application.ActiveInspector.CurrentItem
If TypeOf obj Is Outlook.MailItem Then
Set Mail = obj
'Check if we need to delay delivery
If SendNow = N Then
Mail.DeferredDeliveryTime = SendDate
End If
Mail.Send
End If
End Sub
As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)
You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).
As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)
You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).
I know this thread has been resolved, but found the code above to give me variable dates and times when used, so modified code to do the following:
Not send before 7am (deferred to 7:03am)
Not send after 9pm (deferred to 7:03am next morning)
Not send on Saturday or Sunday (both deferred to 7:03am)
Great for keeping email velocity down late at night and on weekends!
Sub SendLater()
Dim obj As Object
Dim Mail As Outlook.MailItem
Dim WkDay As String
Dim MinNow As Integer
Dim SendHour As Integer
Dim SendDate As Date
Dim SendNow As String
'Check if Sunday
If WkDay = 1 Then
SendDate = DateAdd("d", 1, SendDate)
SendDate = DateAdd("h", -SendHour, SendDate)
SendHour = 7 'Send a 7 am next day
SendDate = DateAdd("h", SendHour, SendDate)
SendDate = DateAdd("n", -MinNow, SendDate)
MinNow = 3 'Send a 7 am next day
SendDate = DateAdd("n", MinNow, SendDate)
SendNow = N
End If
'Check if Saturday
If WkDay = 7 Then
SendDate = DateAdd("d", 2, SendDate)
SendDate = DateAdd("h", -SendHour, SendDate)
SendHour = 7 'Send a 7 am on Monday
SendDate = DateAdd("h", SendHour, SendDate)
SendDate = DateAdd("n", -MinNow, SendDate)
MinNow = 3 'Send a 7 am on Monday
SendDate = DateAdd("n", MinNow, SendDate)
SendNow = N
End If
'Check if Before 7am
If SendHour < 7 Then
SendDate = DateAdd("h", -SendHour, SendDate)
SendHour = 7 'Send a 7:03 am today
SendDate = DateAdd("h", SendHour, SendDate)
SendDate = DateAdd("n", -MinNow, SendDate)
MinNow = 3 'Send a 7:03am today
SendDate = DateAdd("n", MinNow, SendDate)
SendNow = N
End If
'Check if after 9PM
If SendHour > 20 Then 'After 9 PM
SendDate = DateAdd("d", 1, SendDate)
SendDate = DateAdd("h", -SendHour, SendDate)
SendHour = 7 'Send a 7 am next day
SendDate = DateAdd("h", SendHour, SendDate)
SendDate = DateAdd("n", -MinNow, SendDate)
MinNow = 3 'Send a 7 am next day
SendDate = DateAdd("n", MinNow, SendDate)
SendNow = N
End If
'Send the Email
Set obj = Application.ActiveInspector.CurrentItem
If TypeOf obj Is Outlook.MailItem Then
Set Mail = obj
'Check if we need to delay delivery
If SendNow = N Then
Mail.DeferredDeliveryTime = SendDate
End If
Mail.Send
End If
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
I'm posting one more alternative to the code, key changes:
1) By pasting this code into ThisOutlookSession the check is now happening automatically every time the user presses Send.
2) Adapted code so that mails sent Friday night after 7pm get sent three days later (Monday) rather than next day
3) User receives a Yes No prompt to ask whether they want to postpone the sending to a proposed date (Pressing no sends immediately)
4) Change the WkDay variable type from String to Integer because was not working reliably as evaluation tests were integers not "inverted comma strings"
5) Put the SendMail Y N checks in inverted commas to be treated reliably as strings
Dim obj As Object
Dim Mail As Outlook.MailItem
Dim WkDay As Integer
Dim MinNow As Integer
Dim SendHour As Integer
Dim SendDate As Date
Dim SendNow As String
Dim UserDeferOption As Integer
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
'On Error GoTo ErrorHandler
'This sub used to delay the sending of an email from send time to the next work day at 8am.
'Check if Before 7am
If SendHour < 7 Then
MsgBox ("Before seven")
SendHour = 8 - SendHour
SendDate = DateAdd("h", SendHour, SendDate)
SendDate = DateAdd("n", -MinNow, SendDate)
SendNow = "N"
End If
'Check if after 7PM other than Friday
If SendHour >= 19 Then 'After 7 PM
SendHour = 32 - SendHour 'Send a 8 am next day
SendDate = DateAdd("h", SendHour, SendDate)
SendDate = DateAdd("n", -MinNow, SendDate)
SendNow = "N"
End If
'Check if Sunday
If WkDay = 1 Then
SendDate = Now()
SendHour = Hour(Now)
SendDate = DateAdd("d", 1, SendDate)
SendDate = DateAdd("h", 8 - SendHour, SendDate)
SendDate = DateAdd("n", -MinNow, SendDate)
SendNow = "N"
End If
'Check if Saturday
If WkDay = 7 Then
SendDate = Now()
SendHour = Hour(Now)
SendDate = DateAdd("d", 2, SendDate)
SendDate = DateAdd("h", 8 - SendHour, SendDate)
SendDate = DateAdd("n", -MinNow, SendDate)
SendNow = "N"
End If
'Check if Friday after 7pm
If WkDay = 6 And SendHour >= 19 Then 'After 7pm Friday
SendDate = Now()
SendHour = Hour(Now)
SendDate = DateAdd("d", 3, SendDate)
SendDate = DateAdd("h", 8 - SendHour, SendDate)
SendDate = DateAdd("n", -MinNow, SendDate)
SendNow = "N"
End If
'Send the Email
Set obj = Application.ActiveInspector.CurrentItem
If TypeOf obj Is Outlook.MailItem Then
Set Mail = obj
'Check if we need to delay delivery
If SendNow = "N" Then
UserDeferOption = MsgBox("Do you want to postpone sending until work hours (" & SendDate & ")?", vbYesNo + vbQuestion, "Time to stop working!")
If UserDeferOption = vbYes Then
Mail.DeferredDeliveryTime = SendDate
MsgBox ("Your mail will be sent at: " & SendDate)
Else
End If
End If
End If
Exit Sub
'ErrorHandler:
' MsgBox "Error!"
End Sub
Last edited by adamthorne; Jan 30th, 2017 at 04:44 AM.
Reason: Code improved
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Change #4 was to change the WkDay variable type from String to Integer because was not working reliably as evaluation tests were integers not "inverted comma strings"
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Originally Posted by adamthorne
Change #4 was to change the WkDay variable type from String to Integer because was not working reliably as evaluation tests were integers not "inverted comma strings"
Quick question - I have used the above code to delay delivery. I suddenly realise I need to send it right away. How do you remove the delay?
Thanks
Josh
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
I have used the above code to delay delivery.
which code? post #8 item #3 states
3) User receives a Yes No prompt to ask whether they want to postpone the sending to a proposed date (Pressing no sends immediately)
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Hi All,
I've been following the above code and have it working great but I want to change the time so mail isn't sent until 7.30am (weekdays). How would the code need to change to reflect this?
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
here is my take on that code, but slightly easier to modify times etc
Code:
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
dim delay as date
delay = setdelay
if delay then item.DeferredDeliveryTime = delay
End Sub
Function setdelay(Optional forwarddays As Integer = 0) As Date
Dim sndtime As Date, ndtime As Date, n As Date
sndtime = TimeSerial(7, 30, 0) ' 7:30 AM
ndtime = TimeSerial(19, 0, 0) ' 7 PM
n = Date + forwarddays ' forwarddays not used
setdelay = 0
If Time < sndtime Then setdelay = n + sndtime
If Time > ndtime Then setdelay = n + 1 + sndtime
If Time > ndtime And Weekday(n) = vbFriday Then setdelay = n + 3 + sndtime
If Weekday(n) = vbSaturday Then setdelay = n + 2 + sndtime
If Weekday(n) = vbSunday Then setdelay = n + 1 + sndtime
End Function
if you want user interaction to delay email, change to
Code:
If delay And vbYes = MsgBox("delay sending email", vbYesNo) Then item.DeferredDeliveryTime = delay
Last edited by westconn1; Jan 17th, 2018 at 04:52 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Thats great I have it working perfectly, thank you - now I can look like Im a early bird whilst I am actually getting an extra 30minutes sleep
Originally Posted by westconn1
here is my take on that code, but slightly easier to modify times etc
Code:
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
dim delay as date
delay = setdelay
if delay then item.DeferredDeliveryTime = delay
End Sub
Function setdelay(Optional forwarddays As Integer = 0) As Date
Dim sndtime As Date, ndtime As Date, n As Date
sndtime = TimeSerial(7, 30, 0) ' 7:30 AM
ndtime = TimeSerial(19, 0, 0) ' 7 PM
n = Date + forwarddays ' forwarddays not used
setdelay = 0
If Time < sndtime Then setdelay = n + sndtime
If Time > ndtime Then setdelay = n + 1 + sndtime
If Time > ndtime And Weekday(n) = vbFriday Then setdelay = n + 3 + sndtime
If Weekday(n) = vbSaturday Then setdelay = n + 2 + sndtime
If Weekday(n) = vbSunday Then setdelay = n + 1 + sndtime
End Function
if you want user interaction to delay email, change to
Code:
If delay And vbYes = MsgBox("delay sending email", vbYesNo) Then item.DeferredDeliveryTime = delay
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
now I can look like Im a early bird
just remember the code does not take into account any public holidays
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Originally Posted by westconn1
just remember the code does not take into account any public holidays
Hi I have adjusted the code slightly in terms of the message box but on sending emails today at 09:50 I am getting a message asking if I want to defer. Have I done something wrong?
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim delay As Date
delay = setdelay
If delay And vbYes = MsgBox("Do you want to postpone sending until work hours (" & setdelay & ")?", vbYesNo) Then Item.DeferredDeliveryTime = delay
End Sub
Function setdelay(Optional forwarddays As Integer = 0) As Date
Dim sndtime As Date, ndtime As Date, n As Date
sndtime = TimeSerial(7, 30, 0) ' 7:30 AM
ndtime = TimeSerial(18, 0, 0) ' 6 PM
n = Date + forwarddays ' forwarddays not used
setdelay = 0
If Time < sndtime Then setdelay = n + sndtime
If Time > ndtime Then setdelay = n + 1 + sndtime
If Time > ndtime And Weekday(n) = vbFriday Then setdelay = n + 3 + sndtime
If Weekday(n) = vbSaturday Then setdelay = n + 2 + sndtime
If Weekday(n) = vbSunday Then setdelay = n + 1 + sndtime
End Function
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Have I done something wrong?
Not at all, it was me, i did not actually test with the message box
change to
Code:
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim delay As Date
delay = setdelay
If delay Then
If vbYes = MsgBox("Do you want to postpone sending until work hours (" & delay & ")?", vbYesNo) Then Item.DeferredDeliveryTime = delay
end if
End Sub
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Originally Posted by westconn1
Not at all, it was me, i did not actually test with the message box
change to
Code:
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim delay As Date
delay = setdelay
If delay Then
If vbYes = MsgBox("Do you want to postpone sending until work hours (" & delay & ")?", vbYesNo) Then Item.DeferredDeliveryTime = delay
end if
End Sub
This is great. What happens if I change my mind and want to send a delayed message?
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Originally Posted by westconn1
Not at all, it was me, i did not actually test with the message box
change to
Code:
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim delay As Date
delay = setdelay
If delay Then
If vbYes = MsgBox("Do you want to postpone sending until work hours (" & delay & ")?", vbYesNo) Then Item.DeferredDeliveryTime = delay
end if
End Sub
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
What happens if I change my mind
at what point? after the message box any mail without delay has gone already
while you may be able to get and send outlook emails on your android device, i am sure it can not install outlook or run any vba
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
What if you want to really send ir al the moment?
click the no button, it will go immediately
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
you can go to the folder where the deferred delivery items are and change the email options, at a guess i would think it would sit in outbox until the delivery time
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
but it doesnt send the mail even if you press no
that may be an outlook setting as to when the mail is sent, it may only send on the mails when it checks for incoming mails, not sure on that, you can try send/receive to see it any go immediately
if you have an ongoing problem, try setting the deferred delivery time to Now
ones with deferred delivery times can be changed to send now, by opening the email and change the setting for deferred delivery, then clicking send, on my old version, the setting is in view > options > Do not deliver before > uncheck
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Originally Posted by westconn1
Not at all, it was me, i did not actually test with the message box
change to
Code:
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim delay As Date
delay = setdelay
If delay Then
If vbYes = MsgBox("Do you want to postpone sending until work hours (" & delay & ")?", vbYesNo) Then Item.DeferredDeliveryTime = delay
end if
End Sub
Every thing is great, except if my computer is put to sleep, the mail isnt sent until I turn it on again. Is there any fix for this?
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
disable sleep
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
You should have your computer on a surge suppressor at the very least, and a UPS would be better. Mine run 24/7/365 and I have not had any issues. If you want mail sent at specific times, the computer sending it has to be on at those times.
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Originally Posted by jdc2000
You should have your computer on a surge suppressor at the very least, and a UPS would be better. Mine run 24/7/365 and I have not had any issues. If you want mail sent at specific times, the computer sending it has to be on at those times.
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
I would not attempt to use a laptop for functions that should be reserved for a desktop or server type of system. Laptops usually have more heat build-up issues and less than optimal cooling. Even so, the biggest reliability issue for laptops is the fact that they are carried around and subject to shock and impact. You should still consider moving this functionality to a system more suited to the 24/7 requirements.
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
i run now my server on a laptop 24/7, but it is not running windows 10 which seems to heat them up a lot more, fans always seem to be running flat out with windows 10, generally it is cooler at night, and not really doing much, most of the work is being done during day /evening, laptops are less susceptible to power fluctuations and brief power outs as they can switch to their own battery for a short time, with out need to external power devices
like most things on life, all depends on your needs and cash
many desktops and presumably some laptops have settings in the bios to wake the computer at a set time, so you could use that to have the computer running before you want the mails to send
this is all pretty much off topic anyway
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
[QUOTE=adamthorne;5133275]I'm posting one more alternative to the code, key changes:
1) By pasting this code into ThisOutlookSession the check is now happening automatically every time the user presses Send.
2) Adapted code so that mails sent Friday night after 7pm get sent three days later (Monday) rather than next day
3) User receives a Yes No prompt to ask whether they want to postpone the sending to a proposed date (Pressing no sends immediately)
4) Change the WkDay variable type from String to Integer because was not working reliably as evaluation tests were integers not "inverted comma strings"
5) Put the SendMail Y N checks in inverted commas to be treated reliably as strings
Works great when an email is opened in it's own dialogue box, but when trying to reply 'within' the Outlook thread (i.e. by NOT clicking "Pop Out"), I receive the following error message: Run-time error '91': Object variable or with block variable not set
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Any ideas?
you should have started anew thread for this, the original question was over 10 years ago
it would help if you post the entire code you are using (post in code tags), and specify on which line the error occurs
code in the itemsend event should always fire, but some code posted earlier, probably what you are using, tries to send the mail from within the itemsend event, which would cause an ongoing endless loop
the code i posted earlier in this thread, should not have the same problem
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete