Consulting

Results 1 to 4 of 4

Thread: Outlook VB Macro Problem

  1. #1

    Outlook VB Macro Problem

    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
    Last edited by SamT; 08-31-2014 at 05:08 AM. Reason: Added Code Tags with the # Icon

  2. #2
    first remove on error resume next, so any error will show

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    I have it working now. Thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •