Outlook VB Macro Problem

[hansgruber]

Good Morning,

I have created a VB macro in Outlook 2010 to warn end users before sending to a bad e-mail address (see below). It worked fine when I first created it and modified it a couple of times for other addresses. Now it suddenly will not work for some reason.

I set this up under Project1 in "ThisOutlookSession" as Application "ItemSend". I have tried the following to get it to work:

1) Recompiled the macro.
2) Exited and restarted Outlook.
3) Created a selfcert digital certificate and changed the security settings in the Trust Center for macros to "Notifications for digitally signed macros, all other macros disabled".
4) I even tried "Enabled all macros" ... still no luck.
5) Deleted everything and started again by creating the same macro from scratch ... still not working.

I am fairly new to VB macros and this is frustrating (since I had it working at first), so could really use some help.

Thanks in advance!

=======================================================


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

On Error Resume Next
' use lower case for the address
' LCase converts all addresses in the To field to lower case
If InStr(LCase(Item.To), "bad e-mail address here") Then
Prompt$ = "You are sending this to " & Item.To & ". 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 Sub

[westconn1]

first remove on error resume next, so any error will show

[SamT]

You are missing a parentheses, and I like to be specific
If InStr(LCase(Item.To), "bad e-mail address here")) > 0 Then[/CODE]

I think that you will find that code a bit unwieldy when you get too many bad addresses. Check out the code below. Its advantages are speed, removing the list from the main sub, and with BadAdds declared as a static and the two redundant If_initialized checks, the Sub "Init_BadAds" only runs once per session. The Sub "Init_BadAdds" and the variable "Bad_Adds" can also be moved to their own module if desired. The Init Sub can then be called prior to running Application_Sendtem.


Static BadAdds As Variant

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

If BadAdds = "" Then Init_BadAdds

For i = LBound(BadAdds) To UBound(BadAdds)
If LCase(Item.to) = BadAdds(i) Then
Prompt$ = "You are sending this to " & Item.to & ". Are you sure you want to send it?"
If MsgBox(Prompt$, vbYesNo + vbQuestion + vbMsgBoxSetForeground, "Check Address") = vbNo Then
Cancel = True
Exit Sub '
End If
End If
Next i

End Sub

Sub Init_BadAdds()
'double-double check if BadAdds is intialized
If BadAdds = "" Then _
BadAdds = Array( _
"adam@email.com", _
"bill@email.com", _
"Charlie@email.com", _
"Dave@email.com", _
"etc@etc.com")
End Sub

[hansgruber]

I have it working now. Thanks for your help!