Automatic Email Function

Question

I have a code for private sub function for sending automatic emails. I sourced the code from Pete Leaning

ACCESS 2007 - Automatically Send and Email Using Outlook Upon a Specific Event

I attempted to turn it into a function myself using the code below. However it doesn't work. I have a feeling I have set it up completely wrong. Also I'd like the body of the email to contain the the entire record information if possible.

Option Explicit
Public Started As Boolean
Public oApp As Outlook.Application
Public oItem As Outlook.MailItem
Function AutoEmail()



'Automatic Email to send notifications to selected user
If Combo99.Text = "SM" Or "TW" Or "LM" Or "LV" Or "SV" Then


    On Error Resume Next
    'Get Outlook if it's running
    Set oApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then
         'Outlook wasn't running, start it from code
         Set oApp = CreateObject("Outlook.Application")
        Started = True
    End If

                     Set oItem = oApp.CreateItem(olMailItem)
                     With oItem
                        .To = "[email protected]"
                        .Subject = "AutoEmail Test"
                        .Body = "Please enjoy this complimentary email. If this worked please email back."
                        'Send the email
                        .Send
                    End With

                                    Set oItem = Nothing
                                    If Started Then
                                        oApp.Quit
                                    End If

'Display message to the user
MsgBox "A model that is on the watch list has been selected. An Automatic Email has been sent", vbOKOnly, AutoEmail

Else
        'Do nothing
End If


End Function

Answer 1

There are two issues with this code line ...

If Combo99.Text = "SM" Or "TW" Or "LM" Or "LV" Or "SV" Then

1. The combo's Text property is available only when it has focus. When focus shifts to a different control, such as when the user clicks a command button, use Combo99.Value instead.

2. When you use Or between conditions, you must repeat the item from the left side of the = sign again for each Or.

Consider these two If statements ...

If strLetter = "a" Or "b" Then
If strLetter = "a" Or strLetter = "b" Then

The first throws an error, but the second does not.

If you prefer to compare something to a list of values, you can use Select Case.

Select Case Me.Combo99.Value
Case "SM", "TW", "LM", "LV", "SV"
    ' insert code to send email
Case Else
    ' no email
End Select