VBA Outlook Script

Question

I have a script I've pieced together over time. Recently a plugin we need to use in Outlook has caused some issues. Basically we get prompted twice because when the plugin is used the e-mail ends up being destroyed and re-created with a specific attachment filename. At this point the user is prompted again. I've tried to work in a For loop to skip the script if it finds this attachment. However, when I added the For loop it just seems to skip the entire script. I have limited experience with VBA and so I'm sure it's an issue with my syntax or usage. See script below:

Private Sub Application_ItemSend _
(ByVal Item As Object, Cancel As Boolean)
Dim strMsg As String
Dim Atmt As Variant

'strMsg = Item.Class
If Item.Class = "43" Then
    For Each Atmt In Item.Attachments
        If VBA.Right(Atmt.FileName, 3) = ".sf" Then
           GoTo NonEmailError
        End If
    Next Atmt
    If Item.CC = "" Then
        strMsg = "To recipients: " & Item.To & vbCrLf & _
        "Are you sure you want to send this message?"
    Else
        strMsg = "To recipients: " & Item.To & vbCrLf & _
        "Cc recipients: " & Item.CC & vbCrLf & _
        "Bcc recipients: " & Item.BCC & vbCrLf & _
        "Are you sure you want to send this message?"
    End If
Else
    GoTo NonEmailError
End If
' Exit Sub

' Ignore errors for now.
On Error GoTo NonEmailError

' Prompt user to fill in subject
If Item.Subject = "" Then
    MsgBox "You must enter a subject.", 48, "Empty Subject"
    Cancel = True
    GoTo NonEmailError
End If
' Exit Sub

' Prompt user to verify E-Mails
If MsgBox(strMsg, vbYesNo + vbQuestion _
    , "Send Confirmation") = vbNo Then
    Cancel = True
End If
Exit Sub

NonEmailError:
' The item being sent was not an e-mail and so don't prompt the user anything
Exit Sub

End Sub

A couple of comments: 1) Do the two prompts show up regardless of the email having attachments or not? 2) Which is the line associated with the first prompt, and which to the second? 3) It is likely a good idea to work on an Outlook.MailItem instead of an Object, with Dim olMI As Outlook.MailItem --- If TypeName(Item) = "MailItem" Then --- Set olMI = Item --- 'Do your stuff --- End If. — sancho.s ReinstateMonicaCellio

horatio horatio · Accepted Answer · 2011-09-27T19:25:28

I am not that familiar with outlook and VBA, but my first guess in this case is that item.class should be compared to the numeric value 43 rather than the string-literal value "43".

The one thing that gives me pause is that I would expect the statement to throw an error because "43" is the wrong type for Item.Class, but this may just reflect my unfamiliarity with the material.

According to msdn reference, item.class (in this case a mailItem) is an OlObjectClass constant, and 43 is olMail. It would probably be advisable to change:

If Item.Class = "43" Then

to

If Item.Class = olMail Then

or at least to:

If Item.Class = 43 Then

( see: http://msdn.microsoft.com/en-us/library/bb208118%28v=office.12%29.aspx and http://msdn.microsoft.com/en-us/library/bb207137%28v=office.12%29.aspx )

VBA Outlook Script

1 Answers