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 = "google@google.com"
                        .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

If i remove the IF Statement it works, however this is needed for when the form is submitted. Combo99 (The name will be changed soon) is where i need to check for a model that is no longer supported. — ASM2701, Dec 09 '13 at 14:15

score 1 · Accepted Answer · answered Dec 09 '13 at 15:10

1

There are two issues with this code line ...

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

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.
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

answered Dec 09 '13 at 15:10

HansUp

95,961
11
77
135

Thank you for your quick response Hans... When i tried to run the code it comes up with a compile error where the use of ME is invalid. – ASM2701 Dec 09 '13 at 15:38
So then what happens if you discard "Me."? – HansUp Dec 09 '13 at 15:46
Get the following error. Compile Error: Variable not Defined – ASM2701 Dec 09 '13 at 15:47
And is `Combo99.Value` what the compiler is complaining about? Then I don't know why didn't it complain with your original code. – HansUp Dec 09 '13 at 15:50
Aaah ... when you said *"it doesn't work"*, you had the same problem then, too? – HansUp Dec 09 '13 at 15:51
Yeah sorry i should of been more clear on that. I apologize about that. The compiler just doesn't like the ME or when its removed. – ASM2701 Dec 09 '13 at 15:52
Yes, *"doesn't work"* is *almost* useless for anyone trying to help you. You must give us details to understand *your* problem. – HansUp Dec 09 '13 at 15:55
`Combo99` is located on a form, correct? And where is the VBA code located? Is it contained in the form's code module or a different code module? – HansUp Dec 09 '13 at 15:58
Combo99 is on a form called FRM_CallDetails. The code is stored in a module named AutoEmail. – ASM2701 Dec 09 '13 at 16:05
Does this work without error when you add it to your AutoEmail code? `MsgBox Forms!FRM_CallDetails!Combo99.Value` – HansUp Dec 09 '13 at 16:08
I commented everything just to try that code. It worked without issue. – ASM2701 Dec 09 '13 at 16:12
Attempted to add it into the code but when i did i got a run time error 2450. I attempted to do it with the following ones: Select Case Forms!FRM_CallDetails!ModelType.Value Select Case FRM_CallDetails!ModelType.Value – ASM2701 Dec 09 '13 at 16:17
What happened to `Combo99`? – HansUp Dec 09 '13 at 16:19
Sorry i changed the name quickly to see if i had a combobox with a similar name that was causing issue. – ASM2701 Dec 09 '13 at 16:21
Quick Question.... How can i get the email to display the information from the record in the body of the email? – ASM2701 Dec 10 '13 at 11:22
Append it to `olMailItem.Body` – HansUp Dec 10 '13 at 18:24

Automatic Email Function

1 Answers1