I have a macro that defers sending messages that are composed during a specific time period, such as during the overnight hours. After several users asked how to send responses or forward messages if the new messages arrived during the overnight hours, it's time to add macros for incoming messages as well.
I would like to forward the emails between 23.00 and 6 in the morning.
How do I set a recurring auto response rule every day between 6PM to 1AM with a custom message?
Obviously, you could use a rule and turn it on every evening and turn it off in the morning. If you use Exchange Server, you could use Automatic Replies (out-of-office) and create a rule to forward messages. Automatic replies can be set to turn on and off at a specific time but would need to be reset each day.
There are a few ways to automate this for incoming mail that will work with any account type. The only caveat: Outlook may need to be running for these methods to work.
The choices: 1) A reminder triggers a macro that turns the rules on and off at the appointed time or 2) a run a rule script that checks the time. In some cases, an ItemAdd macro that checks the time might be better than a rule, but the script is similar for both.
If you need to filter the messages and only reply or forward specific messages, it's easier to use a rule. The script at Enable or Disable an Outlook Rule using Reminders works great if you need the rule running at different times on different dates as you can create tasks or appointments for each start or end time. If you have an Exchange mailbox or Outlook.com configured using Exchange and the rule is server-side, you can close Outlook after the reminder macro turns the rule on.
You can leave the rule running all the time and use a script check the time. Warning: the script needs to do all actions; you can't move messages using the rule and reply using the script.
The example below sends a reply if it's after 5:45 PM or before 6:45 AM. If you are working in whole hours, you can use Hour(Time) in the If statement, with afternoon hours in 24-hour time format. In this example, messages that arrive after 5PM or before 7AM are processed by the rule.
If Hour(Time) >= 17 Or Hour(Time) <= 7 ThenThis sample script replies to the sender using a template. To reply with the original message, use
Set oRespond = Item.Reply
To forward the message, use
Set oRespond = Item.Forward
Public Sub AutoReplyOvernight(Item As Outlook.MailItem) If Now() > DateSerial(Year(Now), Month(Now), Day(Now)) + #5:45:00 PM# _ Or Now() < DateSerial(Year(Now), Month(Now), Day(Now)) + #6:45:00 AM# Then 'send the autoreply Set oRespond = Application.CreateItemFromTemplate("C:\path\to\reply.oft") With oRespond .Recipients.Add Item.SenderEmailAddress .Body = "The office is closed. " & vbcrlf & item.body .send End with End if End Sub
Use an ItemAdd macro
An ItemAdd macro can process more messages per minute than a rule. This can be helpful to POP3 users who receive a large amount of email. Other advantages are that you don't need to create a rule to use it and it can watch any folder, while the disadvantages are that it can't be turned off and will run each time a new message arrived in the watched folder, all day long.
This macro goes in ThisOutlookSession
Option Explicit Private objNS As Outlook.NameSpace Private WithEvents objItems As Outlook.Items Private Sub Application_Startup() Dim objWatchFolder As Outlook.Folder Set objNS = Application.GetNamespace("MAPI") 'Set the folder and items to watch: Set objWatchFolder = objNS.GetDefaultFolder(olFolderInbox) Set objItems = objWatchFolder.Items Set objWatchFolder = Nothing End Sub Private Sub objItems_ItemAdd(ByVal Item As Object) If Hour(Time) >= 17 Or Hour(Time) <= 7 Then Set oRespond = item.forward With oRespond .Recipients.Add Item.SenderEmailAddress .Send End with End if End Sub
Â
How to Use Macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 and above, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, itâs at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
To use the macro code in ThisOutlookSession:
- Expand Project1 and double click on ThisOutlookSession.
- Copy then paste the macro into ThisOutlookSession. (Click within the code, Select All using Ctrl+A, Ctrl+C to copy, Ctrl+V to paste.)
Application_Startup macros run when Outlook starts. If you are using an Application_Startup macro you can test the macro without restarting Outlook by clicking in the first line of the Application_Startup macro then clicking the Run button on the toolbar or pressing F8.
More information as well as screenshots are at How to use the VBA Editor
There exists a ready-to-use solution: OffHours.
It gives an error at Set oRespond = Item.Forward, variable not defined.
Either remove option explicit or add Dim oRespond before that line.
Thank Diane, it worked with the recommended steps.
However I have another concern, my automatic replies are forwarding blank mails as per macros used.
What I have done:
I have saved the template file (C:\path\to\reply.oft) with the content that I want to reply during off hours.
From the module macros, i have not included anything. and Just keeping the same line.
.Body = "The office is closed. " & vbcrlf & item.body
This Outlook session macros is same as you have mentioned.
Set oRespond = Item.Reply
With oRespond
.Recipients.Add Item.SenderEmailAddress
.Send
End With
How can I pull the mail template as a response?
use
Set oRespond = Application.CreateItemFromTemplate("C:\path\to\reply.oft")
Yes, I have set it in the Module macros. It's still not working.
Use this macro to run a run a script macro on the selected message - change "YourMacroName " to the name of your macro. Does it work?
Sub RunScript()
Dim objApp As Outlook.Application
Dim objItem As Object ' MailItem
Set objApp = Application
Set objItem = objApp.ActiveExplorer.Selection.Item(1)
'macro name you want to run goes here
YourMacroName objItem
End Sub
There exists an add-in for Outlook named OffHours: https://www.ivasoft.com/offhoursaddin.shtml
Microsoft FLOW is a new solution for Office 365, to automate treatments!
I am Using windows 10, Excel 2013 and Outlook 2013
I am new to Macro. I need macro to perform below Task.
1) From Excel I want to open Outlook if Outlook is closed and move Point.2, If outlook is already open then move to Point.2
2) Search for a specific email in outlook in all folders and sub folders with criteria âAâ and âBâ
a) Latest dated received or sent email.
b) With specific Subject contains âStatus of EB Proposals - Sam Lucasâ.
3) Open the found latest mail as per above criteria do âReply allâ.
4) Copy excel sheet as reply message on outlook body and send