CODEWITHSUNDEEP
excelvbaoutlook
Chum Ba
Chum Ba

Reputation: 47

Trigger a macro after a new email with specific subject

Run excel macro based on email Subject

I already found a way to trigger a macro when a new email is found. However, I would like to only trigger it when there is a specific word in the email subject line. Here's the line of code I found posted by JimmyPena.

Private WithEvents Items As Outlook.Items
Private Sub Application_Startup() 
  Dim olApp As Outlook.Application 
  Dim objNS As Outlook.NameSpace 
  Set olApp = Outlook.Application 
  Set objNS = olApp.GetNamespace("MAPI") 

  ' default local Inbox
  Set Items = objNS.GetDefaultFolder(olFolderInbox).Items 
End Sub

Private Sub Items_ItemAdd(ByVal item As Object) 

  On Error Goto ErrorHandler 
  Dim Msg As Outlook.MailItem 

  If TypeName(item) = "MailItem" Then
    Set Msg = item 
    ' ******************
    ' I call my macro here
    ' ******************
  End If
ProgramExit: 
  Exit Sub
ErrorHandler: 
  MsgBox Err.Number & " - " & Err.Description 
  Resume ProgramExit 
End Sub

I thought that if I change this part If TypeName(item) = "MailItem" Then to If TypeName(item) = "MailItem" And Msg.Subject = "specific_subject_here" Then that it should now only trigger the macro when the new email contains the specific subject on the subject line but I get this error: 91-Object variable or with block variable not set. Does this mean that I also have to declare Msg as an object and is it possible to combine it with the TypeName function?

Upvotes: 0

Views: 1915

Answers (1)

BigBen
BigBen

Reputation: 49998

The error message is pretty much self-explanatory: you're trying to use an object you haven't Set yet.

Instead, add an additional If Msg.Subject after you Set msg...:

If TypeName(item) = "MailItem" Then
    Set Msg = item 

    If Msg.Subject = "specific subject" Then
    ' ******************
    ' I call my macro here
    ' ******************
    End If
End If

Upvotes: 1

Related Questions