-
[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!
Mark
-
Re: Outlook VB Macro - Deffered Delivery Time
Does it show any error? If so, where does the yellow arrow point to?
-
Re: Outlook VB Macro - Deffered Delivery Time
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)
-
Re: Outlook VB Macro - Deffered Delivery Time
Error is kicking out at SendTime = SendTime.AddHours
Compile error:
Invalid Qualifier
Thanks for the help!
-
Re: Outlook VB Macro - Deffered Delivery Time
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
-
Re: Outlook VB Macro - Deffered Delivery Time
That's the correction I would have recommended. :)
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).
-
Re: Outlook VB Macro - Deffered Delivery Time
Quote:
Originally Posted by
si_the_geek
That's the correction I would have recommended. :)
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
'Set Variables
SendDate = Now()
SendHour = Hour(Now)
MinNow = Minute(Now)
WkDay = Weekday(Now)
SendNow = Y
'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
End Sub
-
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.
'Set Variables
SendDate = Now()
SendHour = Hour(Now)
MinNow = Minute(Now)
WkDay = Weekday(Now)
SendNow = "Y"
'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
-
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
Quote:
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
Quote:
I have used the above code to delay delivery.
which code? post #8 item #3 states
Quote:
3) User receives a Yes No prompt to ask whether they want to postpone the sending to a proposed date (Pressing no sends immediately)
-
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
-
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 :D
Quote:
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
Quote:
now I can look like Im a early bird
just remember the code does not take into account any public holidays
-
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Quote:
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
Quote:
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
-
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
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
Quote:
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
Quote:
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
Will it work on my android cell phone app?
-
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Quote:
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
-
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Quote:
Originally Posted by
westconn1
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
Ayer you click on yes and the mail gets delayed. What if you want to really send ir al the moment?
-
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Quote:
What if you want to really send ir al the moment?
click the no button, it will go immediately
-
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Yes but what happens if i already press yes
-
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
-
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
It does go to outbox but it doesnt send the mail even if you press no un the box
-
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Quote:
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
-
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Quote:
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
-
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
Quote:
Originally Posted by
westconn1
disable sleep
Wont it damage my computer if i leave it ON all Night, every Night?
-
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
Quote:
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.
And it is a Laptop on a Dock Station?
-
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
-
1 Attachment(s)
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
Any ideas?
-
Re: [RESOLVED] Outlook VB Macro - Deffered Delivery Time
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