Verify a recipient is there if a specific word appears in the body

Question

I want to check for a specific word in the body of the email.
Then if the To or CC fields do not contain a specific email address present a popup asking whether to send without the address.

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

    Dim Recipients As Outlook.Recipients
    Dim recip As Outlook.Recipient
    Dim i
    Dim prompt As String
    Dim iIndex As Long
    
    On Error Resume Next
    
    Checklist = "address@example.com"
    iIndex = InStr(Item.Body, "word")

    Set Recipients = Item.Recipients
    For i = Recipients.Count To 1 Step -1
        Set recip = Recipients.Item(i)
        
        If iIndex > 0 Then
            If Not InStr(LCase(recip), LCase(Checklist)) Then
                prompt$ = "You may have forgotten to send this to address. Are you sure you want to send it?"
                If MsgBox(prompt$, vbYesNo + vbQuestion + vbMsgBoxSetForeground, "Check Address") = vbNo Then
                    Cancel = True
                End If
            End If
        End If
        
    Next i

End Sub

Here's my code after a few changes, but still not fully functional:

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

    Dim Recipients As Outlook.Recipients
    Dim recip As Outlook.Recipient
    Dim i
    Dim prompt As String
    Dim iIndex As Long
    Dim addFound As Boolean
    Dim address As String
    addFound = False


    On Error Resume Next

    CheckList = "address@example.com"
    iIndex = InStr(Item.Body, "word")

    Set Recipients = Item.Recipients
    For i = Recipients.Count To 1 Step -1
        Set recip = Recipients.Item(i)

        If InStr(1, LCase(CheckList), LCase(recip)) >= 1 Then
            addFound = True
            address = address & recip & & vbCrLf
            Exit For
        End If

        If iIndex > 0 Then
            If addFound = False Then
                prompt$ = "You may have forgotten to send this to address. Are you sure you want to send it?"
                If MsgBox(prompt$, vbYesNo + vbQuestion + vbMsgBoxSetForeground, "Check Address") = vbNo Then
                    Cancel = True
                End If
            End If
        End If

    Next i


End Sub

I'm getting an error from address = address & recip & & vbCrLf of "Compile error: Syntax error" Not sure how to proceed.

`On Error Resume Next` is used correctly approximately once in a billion times. Even if it didn't hide an error here it was not appropriate. https://stackoverflow.com/a/31753321/1571407 — niton, May 19 '23 at 12:15
As suggested by Dmitry Streblechenko loop through the recipients. `Exit For` when address found. It is the opposite of this https://stackoverflow.com/a/31247785/1571407. Turn the logic around and use one address only in Checklist. — niton, May 19 '23 at 12:22
@niton I'm not entirely familiar with vba coding, so I'm doing a lot of reading, copying, and pasting. Should I be removing `On Error Resume Next`? [link](https://stackoverflow.com/a/31247785/1571407) was one of the sources I had originally went to, but obviously I didn't fully understand what I was looking at. — John, May 19 '23 at 14:15
`On Error Resume Next` https://stackoverflow.com/a/31753321/1571407 "...you should almost NEVER use it. You should figure out why the error occurs and code to handle it." — niton, May 19 '23 at 15:19

score 0 · Answer 1 · answered May 18 '23 at 17:31

0

Cancel parameter must be declared with ByRef modifier:

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

Also, Recipient is an object, you must treat it as such an check a specific property:

 If Not InStr(LCase(recip.Address), LCase(Checklist)) Then

answered May 18 '23 at 17:31

Dmitry Streblechenko

62,942
4
53
78

Made changes you specified and when testing it properly finds the keyword. Only problem is that when the keyword is found it always brings up the "Are you sure?" message whether the specified address is in the To field or CC field or not. Even if one of those fields has the correct address, it will still ask are you sure you want to send, and then if you click no it actually brings up the message a second time. – John May 18 '23 at 18:25
Because your code performs the check for each recipient. Loop through all recipients first and set a boolean flag when a matching recipient is found (and break from the loop). Once out of the loop, check that boolean variable and prompt only if it is false. – Dmitry Streblechenko May 19 '23 at 03:47

score 0 · Accepted Answer · answered May 19 '23 at 15:35

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)    
    
    ' Model for this code
    ' https://stackoverflow.com/a/31247785/1571407
    
    Dim itmRecipients As Recipients
    Dim recip As Recipient
    
    Dim i As Long
    Dim iIndex As Long
    
    Dim checkOneAddress As String
    Dim addFound As Boolean
    
    Dim prompt As String
    
    iIndex = InStr(Item.body, "word")
    
    If iIndex > 0 Then
    
        Debug.Print "addFound: " & addFound
        'addFound = False
        
        checkOneAddress = "address@example.com"
    
        Set itmRecipients = Item.Recipients
        For i = itmRecipients.count To 1 Step -1
            Set recip = itmRecipients.Item(i)
            
            'If InStr(1, LCase(checkOneAddress), LCase(recip)) Then
            If LCase(checkOneAddress) = LCase(recip.address) Then
                addFound = True
                Exit For
            End If
        Next i
        
        If addFound = False Then
            prompt = "You may have forgotten to send this to " & checkOneAddress & ". Are you sure you want to send it?"
            If MsgBox(prompt, vbYesNo + vbQuestion + vbMsgBoxSetForeground, "Check One Address") = vbNo Then
                Cancel = True
            End If
        End If
        
    End If

End Sub

For VBA "Unless otherwise specified, arguments are passed by reference."
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/byref-argument-type-mismatch
https://learn.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#by-reference

Thanks so much! That seems to work perfect. Of course as soon as it's working great I realized that this isn't really designed to be shared with other users. Reading online says I'm better off doing this as a custom add-in. So I may be back to square one technically. Thanks though! If you have any insight into converting this code to an add-in for Outlook please let me know! Otherwise back to the grind lol — John, May 19 '23 at 19:41

Verify a recipient is there if a specific word appears in the body

2 Answers2