Automatic Email if combo box's 2nd value is selected as first is yes and 2nd is no

[jj1]

Can anybody help with the code to send automatic email if combo box's 2nd value is selected for example if first value is "yes" and 2nd is "no". And also I want to send email to three people if 2nd value in the combo box of a form is selected


Private Sub cmdSend_Click()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem




Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)


'Set rst = db.OpenRecordset("SELECT * FROM tUsers WHERE ID=1")


Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.To = rst!Email
.Subject = "Testing"
.Body = "Test number one"
.Send
rst.Close
Set rst = Nothing
Set db = Nothing
End With
End Sub


I have this code

[pbaldy]

You mean if they change the value from yes to no? You could use the before update event of the combo and compare the Value and OldValue properties.

[jj1]

Private Sub Status_AfterUpdate()




Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim SQL As String
Dim ManagerEmail As String


Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)



SQL = ("SELECT ManagerEmail FROM ManagerEmailAddress Where atnStatusID = 2")



Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook

.To = (ManagerEmail)
.Subject = "Testing"
.Body = "Test number one"


Set db = Nothing
End With
End Sub


This is what I am trying to do. Can anybody check. where I am wrong it's not sending the email

[pbaldy]

Well, it's not sending because you didn't include

.Send

or

.Display

You didn't do anything with the SQL, and didn't set ManagerEmail, and double set the Outlook objects.

[jj1]

Thanks for your reply. It's now sending email. What should I do for SQL and ManagerEmail

What I am trying to do is IF on the form's combo box have 2 values say Yes or No. and only if somebody selects No then sends an email to 3 specific managers

So I was trying to write the SQl as SELECT ManagerEmailaddress FROM TblManagerEmailAddress where value in combo box is selecected as No.

How should I write query

[pbaldy]

You need to do what you were doing in the first post; open a recordset on the SQL and use the recordset value. Alternatively you could use a DLookup().

[jj1]

Can you check what I am doing wrong with this one

Private Sub Status_AfterUpdate()


Dim db As Database
Dim rs As dao.Recordset
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim SQL As String
Dim ManagerEmail As String


' Dim st As String




Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT * FROM ManagerEmailAddress WHERE SentEmailorNot=True")


Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)


rs.MoveFirst
While Not rs.EOF


rs.MoveNext
Wend




' SQL = "SELECT ManagerEmailAddress.ManagerEmail FROM ManagerEmailAddress"




With MailOutLook
'MailOutLook.To = rs!ManagerEmail
.To = "rs!ManagerEmail"
.sUBJECT = "Testing"
.Body = "Test number one"
MailOutLook.Send
' MailOutLook.Quit


rs.Close
db.Close




Set rs = Nothing
Set db = Nothing
End With


End Sub

You need to do what you were doing in the first post; open a recordset on the SQL and use the recordset value. Alternatively you could use a DLookup().

[pbaldy]

What specific problem are you having? You're not doing anything in the loop, and the email address reference shouldn't be in quotes.

[jj1]

Its giving me error 424

.To = rs!ManagerEmail

[pbaldy]

With the loop above you've moved recordset past the last record. I would expect code inside the loop, not after.