vba - Run macro after new Outlook email, with code in Excel only - Stack Overflow

admin管理员组

文章数量:1122832

Is it possible to enable automatic Excel macro execution (run macro) every time a new email arrives?

Can something like Private Sub Application_Startup / Triggered() be added to the code?
Or a command for the macro to click the run macro button every time a new email arrives or to automatically click it every 3 seconds?

I thought the whole code should remain in Excel so that it doesn't have to be entered into Outlook.

Here is the code that works when the blue button is clicked.

Raw Github Link

How to import E-Mails from OUTLOOK to EXCEL 2016 file using VBA

'Clear the range contents
Sub Clear_Range()

    Dim lastRow As Integer
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    If lastRow > 4 Then
        ActiveSheet.Range("A5:D" & lastRow).ClearContents
    End If

End Sub

'Import E-Mails from Outlook subroutine
Sub Import_Emails()

    'Empty the range
    Clear_Range
    
    'Create an Outlook Application object
    Dim OutlookApp As Outlook.Application
    
    'Create an Namespace object
    Dim OutlookNamespace As Namespace
    
    'Create a Outlook folder object
    Dim Folder As MAPIFolder
    
    'Object to store the retrieved E-Mails
    Dim OutlookItems As Outlook.items
    
    'Temporary object, used for iteration
    Dim OutlookMail As Variant
    
    'Get the folder name from excel sheet
    Dim FolderName As String
    FolderName = ActiveSheet.Range("D1").Value
    
    'Create an instance of Outlook
    Set OutlookApp = New Outlook.Application
    'Set the namespace
    Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
    'Error handling
    On Error GoTo ExitSub
    
    
    'If the checkbox is not checked, then the folder is at the same level as inbox
    If ActiveSheet.OLEObjects("check").Object.Value = False Then
        Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Forex").Folders("Majors").Folders("USDJPY")
    End If
    
    'If the checkbox is active, then it is a sub-folder of inbox
    If ActiveSheet.OLEObjects("check").Object.Value = True Then
        Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Forex").Folders("Majors").Folders("USDJPY")
    End If
    
        
    'Get the folder items and sort according to the recieved time
    Set OutlookItems = Folder.items
    OutlookItems.Sort "ReceivedTime", True
    
    'Results counter starting from Row 5
    Dim i As Integer
    i = 5
    
    'Print the output
    For Each OutlookMail In OutlookItems
        
        If OutlookMail.ReceivedTime >= ActiveSheet.Range("B1").Value Then
            ActiveSheet.Cells(i, 1).Value = OutlookMail.ReceivedTime
            ActiveSheet.Cells(i, 2).Value = OutlookMail.SenderName
            ActiveSheet.Cells(i, 3).Value = OutlookMail.Subject
            ActiveSheet.Cells(i, 4).Value = OutlookMail.Body
            i = i + 1
        End If
        
    Next OutlookMail
    
    'Display the total number of e-mails retrieved
    ActiveSheet.Range("B2").Value = i - 5
    ActiveSheet.Range("B2").Font.Color = vbBlack
    
    'Reset the obejcts
    Set OutlookItems = Nothing
    Set Folder = Nothing
    Set OutlookNamespace = Nothing
    Set OutlookApp = Nothing
    
    Exit Sub

'Error handling function
ExitSub:
ActiveSheet.Range("B2").Value = "Folder name not found"
ActiveSheet.Range("B2").Font.Color = vbRed

    Set OutlookItems = Nothing
    Set Folder = Nothing
    Set OutlookNamespace = Nothing
    Set OutlookApp = Nothing

End Sub

Is it possible to enable automatic Excel macro execution (run macro) every time a new email arrives?

Can something like Private Sub Application_Startup / Triggered() be added to the code?
Or a command for the macro to click the run macro button every time a new email arrives or to automatically click it every 3 seconds?

I thought the whole code should remain in Excel so that it doesn't have to be entered into Outlook.

Here is the code that works when the blue button is clicked.

Raw Github Link

How to import E-Mails from OUTLOOK to EXCEL 2016 file using VBA

'Clear the range contents
Sub Clear_Range()

    Dim lastRow As Integer
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    If lastRow > 4 Then
        ActiveSheet.Range("A5:D" & lastRow).ClearContents
    End If

End Sub

'Import E-Mails from Outlook subroutine
Sub Import_Emails()

    'Empty the range
    Clear_Range
    
    'Create an Outlook Application object
    Dim OutlookApp As Outlook.Application
    
    'Create an Namespace object
    Dim OutlookNamespace As Namespace
    
    'Create a Outlook folder object
    Dim Folder As MAPIFolder
    
    'Object to store the retrieved E-Mails
    Dim OutlookItems As Outlook.items
    
    'Temporary object, used for iteration
    Dim OutlookMail As Variant
    
    'Get the folder name from excel sheet
    Dim FolderName As String
    FolderName = ActiveSheet.Range("D1").Value
    
    'Create an instance of Outlook
    Set OutlookApp = New Outlook.Application
    'Set the namespace
    Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
    'Error handling
    On Error GoTo ExitSub
    
    
    'If the checkbox is not checked, then the folder is at the same level as inbox
    If ActiveSheet.OLEObjects("check").Object.Value = False Then
        Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Forex").Folders("Majors").Folders("USDJPY")
    End If
    
    'If the checkbox is active, then it is a sub-folder of inbox
    If ActiveSheet.OLEObjects("check").Object.Value = True Then
        Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Forex").Folders("Majors").Folders("USDJPY")
    End If
    
        
    'Get the folder items and sort according to the recieved time
    Set OutlookItems = Folder.items
    OutlookItems.Sort "ReceivedTime", True
    
    'Results counter starting from Row 5
    Dim i As Integer
    i = 5
    
    'Print the output
    For Each OutlookMail In OutlookItems
        
        If OutlookMail.ReceivedTime >= ActiveSheet.Range("B1").Value Then
            ActiveSheet.Cells(i, 1).Value = OutlookMail.ReceivedTime
            ActiveSheet.Cells(i, 2).Value = OutlookMail.SenderName
            ActiveSheet.Cells(i, 3).Value = OutlookMail.Subject
            ActiveSheet.Cells(i, 4).Value = OutlookMail.Body
            i = i + 1
        End If
        
    Next OutlookMail
    
    'Display the total number of e-mails retrieved
    ActiveSheet.Range("B2").Value = i - 5
    ActiveSheet.Range("B2").Font.Color = vbBlack
    
    'Reset the obejcts
    Set OutlookItems = Nothing
    Set Folder = Nothing
    Set OutlookNamespace = Nothing
    Set OutlookApp = Nothing
    
    Exit Sub

'Error handling function
ExitSub:
ActiveSheet.Range("B2").Value = "Folder name not found"
ActiveSheet.Range("B2").Font.Color = vbRed

    Set OutlookItems = Nothing
    Set Folder = Nothing
    Set OutlookNamespace = Nothing
    Set OutlookApp = Nothing

End Sub

• excel
• vba
• outlook

Share Improve this question Follow edited yesterday CPlus 4,5644141 gold badges3030 silver badges7070 bronze badges asked Dec 27, 2024 at 6:07 Goran ZoricGoran Zoric 111 bronze badge 1

• Utilizing Outlook Events From Excel – niton Commented Dec 28, 2024 at 1:40

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Here's the documentation for the NewMail Event. With that you should be able to make it so your code executes every time a new Mail is recieved. https://learn.microsoft.com/en-us/office/vba/api/outlook.application.newmail

本文标签： vbaRun macro after new Outlook emailwith code in Excel onlyStack Overflow