Conditionally sending an email 1

[krets]

I'm trying to set up a button on a form to conditionally send an email based on a field in a table. The form is unbound and saves data to a table through a VB function called by a button.

The field comes from a table called "tblAIT", is a Yes/No field and is called Rework. Basically the user will select an Error Code on the form from a list. Each Error Code either requires rework or does not. What I would like to happen is when a user selects an error code, if the value for rework is set to Yes, the Submit button automatically sends an email then saves the error information to the table. If it is No, then it just saves the error with no email.

Would do you think would be the best way to go about this?

 

[krets]

For reference, right now I have two buttons on the form, one to submit the error and one to send an email.

Submit button:

Private Sub SaveAIT_Click()

If IsNull(Me!TechRxEInit) Then
    MsgBox "Please make sure to assign the tech initials before saving", vbOKOnly
    Me!TechRxEInit.SetFocus
    Exit Sub
End If
If IsNull(Me!ErrorCode) Then
    MsgBox "Please make sure to select an error code before saving", vbOKOnly
    Me!ErrorCode.SetFocus
    Exit Sub
End If
If IsNull(Me!CellNum) Then
    MsgBox "Your cell number has been cleared due to an unknown error. Please close this form and log back in to the database.", vbOKOnly
    Exit Sub
End If
If IsNull(Me!RphInitials) Then
    MsgBox "Your intials have been cleared due to an unknown error. Please close this form and log back in to the database.", vbOKOnly
    Exit Sub
End If
    Dim db As Database
    Dim rst As Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblAITInfo")
    With rst
    .AddNew
    !AITDate = Me.AITDate
    !RphInit = Me.RphInitials
    !RxNum = Me.RxNum
    !TechRxEInit = Me.TechRxEInit
    !CellNum = Me.CellNum
    !Severity = Me.Severity
    !Category = Me.Category
    !ErrorCode = Me.ErrorCode
    !Comments = Me.Comments
    !Emailed = Me.ckEmailed
    .Update
    End With
    rst.Close
    
    Me!AITDate = ""
    Me!RxNum = Null
    Me!TechRxEInit = Null
    Me!ErrorCode = Null
    Me!Comments = Null
    Me!EmailAdd = Null
    Me.ckEmailed = False
    
    Me!TechRxEInit.RowSource = "SELECT [TechRxEInit], [Email] " & _
                                "FROM tblTechs " & _
                                "WHERE [CellNum] = '" & sCellNum & "';"
    
    Me!RxNum.SetFocus
    Me!AITDate.Value = Date
    
    stDocName = "frmTodaysAITs"
    If CurrentProject.AllForms(stDocName).IsLoaded Then
     [Forms]![frmTodaysAITs]![frmRPhDate].Form.Requery
     [Forms]![frmTodaysAITs]![frmCellDate].Form.Requery
    End If

End Sub

Email button:

Private Sub SendEmail_Click()
If IsNull(Me!TechRxEInit) Then
    MsgBox "The technician's initials must be selected " & _
    "before an email can be sent", vbOKOnly
    Me!TechRxEInit.SetFocus
    Exit Sub
End If
If IsNull(Me!ErrorCode) Then
    MsgBox "An error code must be selected " & _
    "before an email can be sent", vbOKOnly
    Me!ErrorCode.SetFocus
    Exit Sub
End If
Dim mlemail As String
Dim mlrxnum As String
Dim mlerrorcode As String
Dim mlrphinit As String
Dim mlcomments As String

'**create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**gathers information from your form.  this sets the string variable to your fields
mlemail = Me!EmailAdd
mlrxnum = Nz(Me!RxNum, "No Rx Specified")
mlerrorcode = Me!ErrorCode
mlrphinit = Me!RphInitials
mlcomments = Nz(Me!Comments, "")

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
    .To = mlemail
    .Subject = "AIT: " & mlerrorcode & " Rx#: " & mlrxnum
    .Body = mlcomments
    .Display 'sends the email in Outlook.  Change to DISPLAY if you want to be able to
      'modify or see what you have created before sending the email
End With

'**closes outlook
Set objEmail = Nothing

Me!ckEmailed = True

Exit Sub
End Sub

 

[Crowley16]

sounds like a sensible way of doing thing, however you might want to give the users a yes/no box to send the e-mail or not. Generally, users feel better if they can control things a bit more. However this will depend on who your users are and how your application needs to work...

--------------------
Procrastinate Now!

 

[krets]

That's how I have it set up now, the user actually clicks a separate button to send the email. However, the VP wants to standardize the emails sent to our agents because there is too much variation right now.

 

[Crowley16]

instead of having another button, just embed the email functionality into the save button's code (Put it after saving is done)

and maybe have a yes/no box to confirm the sending of an e-mail, you don't really need this but it gives users a sense of control...

--------------------
Procrastinate Now!

 

[krets]

Your first suggestion is what I want to do, I just don't know how to conditionally send the email based on the Rework field. I really have no choice on that one, the VP wants it that way and it has been approved by the other managers and supervisors. I'm not going to fight that fight.

 

[Crowley16]

well, you've got the rework code in a control somewhere on the form right?

then can you not just do

if reWorkControl = "YES" then
sendEmail...
endif

--------------------
Procrastinate Now!

 

[krets]

There is no control on the form for Rework. Rework is set in tblAIT by a supervisor or manager for each Error Code. It is not set by the user. Each Error Code either requires rework or it doesn't.

 

[Crowley16]

in that case, you can use a DLookup to find out the value of Rework, and base your if statement on that.

F1 for details about DLookup

--------------------
Procrastinate Now!

 

[krets]

Ok, that sounds like what I want. When I set up the conditional statement like:

If DLookup("[Rework]", "tblAIT", "[ErrorCode]= " & Chr$(34) & Me!ErrorCode & Chr$(34)) = True Then
Do email stuff
End If

Even if I select an Error Code where I know Rework is true, it's not running the email routine.

 

[Crowley16]

try running dlookup in the immediate window with hard coded error codes to see if it brings back the expected results.

--------------------
Procrastinate Now!

 

[krets]

Nope, it's bringing back Nulls for some reason no matter what error code I use. Is my Dlookup malformed or something?

 

[krets]

When I run a query that should return the same result it returns what I want:

SELECT Rework FROM tblAIT WHERE ErrorCode = "Whatever"

That returns the Yes/No check box with the appropriate value.

 

[Crowley16]

if your select statement works, then dlookup should work as well, try using the same inputs as the select statement, i.e. without the [] and stuff...

if you really can't get dlookup to work, then you can open a recordset, and find out the value of rework through that.

--------------------
Procrastinate Now!

 

[krets]

I think my Dlookup was formed badly so I changed it to add single quotes:

DLookup("[Rework]", "tblAIT", "[ErrorCode] = '" & Me!ErrorCode & "'")

If I use the immediate window to change to form field to a hard coded error it returns a -1 or 0 corresponding to the Rework field.

So to use that in the if statement should I use:

If DLookup("[Rework]", "tblAIT", "[ErrorCode] = '" & Me!ErrorCode & "'") = True Then

Or

If DLookup("[Rework]", "tblAIT", "[ErrorCode] = '" & Me!ErrorCode & "'") = -1 Then

I've tried both unsuccessfully.

 

[Crowley16]

your rework field is a true/false value, therefore you do not need to test it against anything at all, e.g.

if dLookup(..) then
emailStuff
endif

you should put a break point in there somewhere to check as you're running what the values are and which bit of code gets executed...

--------------------
Procrastinate Now!

 

[krets]

Ah, it wasn't my Dlookup, it was something in how I had the routines structured. I changed the order around a bit and everything works.

Thanks for the help.

 

[Crowley16]

yes, it often happens that the problem is not where you're looking, which is why I always step through code and check it's operation at run time...

--------------------
Procrastinate Now!

 

[krets]

I'm pretty new to VB so I don't know how to do that.

 

[Crowley16]

you can set a break point in your code by clicking on the left hand side of the line of code you want to set the break point at. You'll see a red dot and the line will be highlighted.

then when you activate the code, it will stop at that point. From here, you can press F8 to "step" through your code line by line and see how your code flows along with what values your variables actually contain and stuff.

Extremely useful...

--------------------
Procrastinate Now!