VBA code to run a code at specific times

Question

I wrote a piece of code that allow me to copy some data from a workbook and send it via Outlook automatically. It all works fine so far but all I need is to have this macro to run automatically at specific times like at 7:00 am, 10:00 am, 1:00 pm and 5:00 pm.

Sub email()



    Set objIE = CreateObject("InternetExplorer.Application")
    WebSite = "https://example.com"
    logsite = "https://example.com/logoff.do"
    With objIE
        .Visible = True
        .Navigate WebSite
        Do While .Busy Or .readyState <> 4
            DoEvents
        Loop

        Set unElement = .Document.getElementsByName("username")
        unElement.Item(0).Value = "myusername"
        Set pwElement = .Document.getElementsByName("password")
        pwElement.Item(0).Value = "mypassword"
        .Document.forms(0).submit
        '.quit

        Do While .Busy Or .readyState <> 4
            DoEvents
        Loop


        Set expElement = .Document.getElementsByClassName("nav__action dropdown-trigger js--tooltip")
        expElement(0).Click

        Do While .Busy Or .readyState <> 4
            DoEvents
        Loop

        .Document.getElementById("obb_EXPORT_EXCEL").Click

    End With


 application.Wait (Now + TimeValue("0:00:02"))
SendKeys "%S", True
application.Wait (Now + TimeValue("0:00:04"))


   Const file_PATH As String = "C:\Users\mypc\Downloads\"


Dim file As String


file = Dir$(file_PATH & "BFUK*" & ".xlsx")

application.Wait (Now + TimeValue("0:00:02"))
If (Len(file) > 0) Then
  Workbooks.Open(file_PATH & file).Activate


End If

ActiveSheet.Range("A4:BC600").Copy



Windows("macro testing final.xlsm").Activate

Range("A3").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

     [L:L].Select
With Selection
    .NumberFormat = "General"
    .Value = .Value
End With
Range("A3").Select
application.CutCopyMode = False

application.Wait (Now + TimeValue("0:00:20"))
Workbooks(file).Activate
application.Wait (Now + TimeValue("0:00:03"))
ActiveWorkbook.Close savechanges:=False


With New FileSystemObject
    If .FileExists(SOME_PATH & file) Then
        .DeleteFile SOME_PATH & file
    End If
End With



   Windows("macro testing final.xlsm").Activate
   Worksheets("Pivots").Activate

    ThisWorkbook.RefreshAll

    application.Wait (Now + TimeValue("0:00:03"))

    Worksheets("Email").Activate
    application.Wait (Now + TimeValue("0:00:03"))



Dim EmailSubject As String
Dim SendTo As String
Dim EmailBody As String
Dim ccTo As String
Dim r As Range
Set r = Sheets("Email").Range("A1:E72")
r.Copy

EmailSubject = "tNPS Update at " & Format(Time, "hh:mm")
SendTo = Range("Q10")
ccTo = Range("Q10")


Dim outlookApp As Outlook.application
Set outlookApp = CreateObject("outlook.Application")
Dim outMail As Outlook.MailItem
Set outMail = Outlook.CreateItem(olMailItem)



With outMail
.Subject = EmailSubject
.To = SendTo
.CC = ccTo
.body = EmailBody
.display
.send



outMail.display
Dim wordDoc As Word.Document
Set wordDoc = outMail.GetInspector.WordEditor

'Paste as Picture
'wordDoc.Range.PasteAndFormat wdChartPicture

'paste as Table (remove the comma)
wordDoc.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False



End With
Set outlookApp = Nothing
Set outMail = Nothing

   Windows("macro testing final.xlsm").Activate
   Sheets("Raw").Select
   Range("A3:BC900").Select
   Selection.ClearContents
   Range("A3").Select

End Sub

Please can you let me know how to schedule this macro to run automatically at the above given times?

What I first thought of is to write it in vbscript and run it using task scheduler on windows. You will have to modify the code such that it is running on windows outside of excel/outlook. — shahkalpesh, Aug 07 '17 at 16:33
thanks buddy...i check the internet for solutions before i post my question here and found that both options aren't convenient. is there any way like a code to run to call my code at specific time you might be aware of? forgive me i'm new to VBA and still trying to figure it out — Ali S, Aug 07 '17 at 16:35
https://stackoverflow.com/questions/2319683/vba-macro-on-timer-style-to-run-code-every-set-number-of-seconds-i-e-120-secon — braX, Aug 07 '17 at 16:37
Possible duplicate of [VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds](https://stackoverflow.com/questions/2319683/vba-macro-on-timer-style-to-run-code-every-set-number-of-seconds-i-e-120-secon) — Ken White, Aug 07 '17 at 16:39
@AliS: For any vba code to run, the host application will have to remain open. I dont know why you think vbscript with task scheduler is not convenient though. — shahkalpesh, Aug 07 '17 at 16:40
@braX: What OP is looking for is to run the task at specific times of the day instead of at regular interval. — shahkalpesh, Aug 07 '17 at 16:41
The timer can trigger once every minute and check to see what time it is, and take action only at certain times. — braX, Aug 07 '17 at 16:43
Without getting too crazy with the VBA and leaving Excel open all the time, I second @shahkalpesh's suggestion. That way you don't have to worry about keeping a macro running all the time, or otherwise holding up Excel. — BruceWayne, Aug 07 '17 at 16:49
@braX: Sure. I think it is better to run a job at specific time than to poll every minute to see whether it is time. — shahkalpesh, Aug 07 '17 at 18:43
I would also recommend (like @shahkalpesh and others) to use task scheduler to trigger a vbscript file which automates the whole process of running the macro. I have done this before to update a small manual Excel based report and it worked perfectly. — Roan, Aug 08 '17 at 07:29

score 0 · Answer 1 · answered Aug 09 '17 at 02:10

You could set up appointment reminders at these times in Outlook and then trigger an event in VBA.

Check out this link: Running Outlook Macros on a Schedule

And this link: How do I make the application.reminder event work?

Hope this points you in the right direction.

score 0 · Answer 2 · answered Aug 09 '17 at 09:04

Try using Windows Task Scheduler to automate the code execution. Or you can create a .bat file that calls your function, and have Task Scheduler run the .bat file. Make sure you run the Task under an account that will be logged in during the times it should be run.

VBA code to run a code at specific times

2 Answers2