Run Outlook Macros Automatically

Microsoft 365 and Office
1

I have a code that searches the Outlook inbox and copies attachments to a folder. This code works fine when I run it manually.

Sub DownloadIPOInstruments()
Dim objOL As Outlook.Application
Dim objNamespace As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim objMail As Outlook.MailItem
Dim objAttachments As Outlook.Attachments
Dim objAttachment As Outlook.Attachment
Dim strFolderpath As String
Dim strSubject As String
Dim strDate As String
Dim strNewFileName As String
Dim strFilePath As String

' Specify the folder path where you want to save the attachment 
strFolderpath = "C:\Users\hengj\Desktop\IPO_Instrument_Report_Daily\" 

' Specify the date format in the subject 
strDate = Format(Date, "yyyymmdd") ' Change the date format as per your subject, report is T-1 date. 

Set objOL = Outlook.Application 
Set objNamespace = objOL.GetNamespace("MAPI") 
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox) 

' Loop through each email in the Inbox folder with the specified subject 
For Each objMail In objFolder.Items.Restrict("[Subject] = 'IPO Instrument Report_" & strDate & "'") 
    Set objAttachments = objMail.Attachments 
    ' Loop through each attachment and save it to the specified folder 
    For Each objAttachment In objAttachments 
        ' Specify the new file name for the attachment 
        strNewFileName = "IPOInstrumentReport.xls" 
        ' Build the full file path 
        strFilePath = strFolderpath & strNewFileName 
        ' Check if the file already exists 
        If Dir(strFilePath) <> "" Then 
            ' Delete the existing file 
            Kill strFilePath 
        End If 
        ' Save the attachment to the specified folder path 
        objAttachment.SaveAsFile strFilePath 
        ' Rename the file to the new file name 
        Name strFilePath As strFolderpath & strNewFileName 
    Next objAttachment 
Next objMail 

Set objAttachment = Nothing 
Set objAttachments = Nothing 
Set objMail = Nothing 
Set objFolder = Nothing 
Set objNamespace = Nothing 
Set objOL = Nothing

End Sub

I tried to automate this macro to run it on a scheduled timing daily using the below, but it is throwing a runtime error 438: Object doesn’t support this property or method.

2

So you have a working macro that downloads attachments from emails in your Outlook inbox, and you want to run it automatically on a daily schedule. Here’s what you need to do:

Step 1: Enable Macros in Outlook

  1. Open Outlook.
  2. Click the File tab.
  3. Select Options.
  4. Click Trust Center and then Trust Center Settings.
  5. In the Trust Center, select Macro Settings.
  6. Choose one of three options to handle macros:
    • Disable all macros without notification: Macros are disabled, and no alerts appear.
    • Disable all macros with notification: Macros are disabled, but alerts will appear if any are present.
    • Disable all macros except digitally signed macros: Macros are disabled, but alerts will appear if there are any unsigned macros present.

Step 2: Set Up a Scheduled Task

  1. Open the Task Scheduler on your Windows computer.
  2. Click Create Basic Task in the right-hand pane.
  3. Enter a name and description for the task.
  4. Set the trigger as Daily at the desired time.
  5. Set the action as Start a program.
  6. Enter the path to Outlook.exe with the /autorun switch and the name of your macro, for example: C:\Program Files\Microsoft Office\root\Office16\OUTLOOK.EXE /autorun DownloadIPOInstruments.
  7. Save the task.

This will run your macro automatically at the specified time each day.