Macro not working after outlook restart

Question

I have a code which checks subject and email for a specific string and notifies users with YES/NO option.

When I restart outlook, I need to manually run MACRO to make it work.

I tried below code but I still need to run it manually.

Public WithEvents PasswordCheck As Outlook.Application
Public Sub Initialize_handler()
   Set PasswordCheck = Outlook.Application
End Sub

Private Sub PasswordCheck_ItemSend(ByVal Item As Object, Cancel As Boolean)
  Dim strBody As String
  Dim strSubject As String
  strSubject = Item.Subject
  strBody = Item.Body
UCasePasswd = UCase("Test_123")
prompt = "Are you sure you want to send this email? It contains  Password: "
If InStr(1, UCase(strSubject), UCasePasswd) > 0 Or _
InStr(1, UCase(strBody), UCasePasswd) > 0 Then
If MsgBox(prompt, vbYesNo + vbQuestion, "Check for Subject") = vbNo Then
Cancel = True
End If
End If
End Sub

Private Sub PasswordCheck_Startup()
Initialize_handler
End Sub

I already checked that. Problem is code is not working. When I restart outlook, macro is not executed unless I go an re-run it. I need help with how do we call macro in Startup — Nikhil Mehta, Oct 03 '17 at 11:43
Possible duplicate of [How can I automatically run a macro when an email is sent in Outlook?](https://stackoverflow.com/questions/35806335/how-can-i-automatically-run-a-macro-when-an-email-is-sent-in-outlook) — niton, Oct 03 '17 at 13:14
Nope. Link do not explain how to resolve the problem. I am looking for a solution using which once I place my code in outlook it should work whenever I restart my outlook. — Nikhil Mehta, Oct 03 '17 at 15:19
Perhaps this https://stackoverflow.com/questions/46002026/outlook-events-not-firing which specifically states how to trigger events when using the convoluted way found in documentation. — niton, Oct 03 '17 at 16:30
I incorporated best answer from that link already. Once I restart outlook, it doesn't seem to work. — Nikhil Mehta, Oct 04 '17 at 07:52
The `Application_Startup()` code is in `ThisOutlookSession` isn't it? — Darren Bartrup-Cook, Oct 04 '17 at 15:10
Yes. Application_startup is replaced by PasswordCheck_Startup(). It doesnt work when I restart outlook. Entire code is present in ThisOutlookSession Private Sub PasswordCheck_Startup() Initialize_handler End Sub — Nikhil Mehta, Oct 04 '17 at 15:16

Darren Bartrup-Cook · Accepted Answer · 2017-10-04T15:32:19.190

Change your procedure name

Private Sub PasswordCheck_ItemSend(ByVal Item As Object, Cancel As Boolean)

should be

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

and be stored in the ThisOutlookSession module.

You should also place Option Explicit at the top of your module and declare UCasePasswd and prompt as string variables.

After testing this code worked without need to call it with the Application_Startup() routine as it's a built in event.

Also, I don't think you can re-reference Application_Startup as PasswordCheck_Startup the same way I've never seen Private Sub Workbook_Open() changed to anything else in Excel.

Edit: This code in ThisOutlookSession worked perfectly - it's the only code in the module.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Dim strBody As String
    Dim strSubject As String
    Dim UCasePasswd As String
    Dim prompt As String

    strSubject = Item.Subject
    strBody = Item.Body
    UCasePasswd = "TEST_123" 'Rather than use UCASE, just write it in upper case.
    prompt = "Are you sure you want to send this email? It contains Password: "
    If InStr(UCase(strSubject), UCasePasswd) > 0 Or InStr(UCase(strBody), UCasePasswd) > 0 Then
        If MsgBox(prompt, vbYesNo + vbQuestion, "Check for Subject") = vbNo Then
            Cancel = True
        End If
    End If
End Sub

Thanks Darren. It works as expected. Appreciate your time and explanation. — Nikhil Mehta, Oct 05 '17 at 09:33
The OP's code, promoted in the documentation, works. https://msdn.microsoft.com/VBA/Outlook-VBA/articles/application-itemsend-event-outlook To run at startup put Initialize Handler in Application_Startup. — niton, Oct 05 '17 at 12:02
@niton I'll have a look and add as an edit if applicable when I'm next on my PC. — Darren Bartrup-Cook, Oct 06 '17 at 21:08
@niton I see what you mean. `PasswordCheck_ItemSend` will work. The OP just needs to change `PasswordCheck_Startup()` to `Application_Startup()` and the original code will work. — Darren Bartrup-Cook, Oct 06 '17 at 21:16

Macro not working after outlook restart

1 Answers1