VB for sending emails on click

[eholtman]

I am having issues how my code is working. What I am looking to do is on click of Received Request to auto the date into Received Date (Works Fine) after that email a text message with my updates (layout works fine). Sends first one with no issue. The email box closes and I am again back to my front screen. If I want to check another box in the Received Request it doesn’t seem to drive Docmd again. The email box does not pop up. Also this occurs if I cancel the email before sending, So basically I want the email box to pop every time the Received request is true. If false it does not need to create a email. Also if I cancel the email that should be fine the email box needs to come back up after I make the Received Request true.

Code***
Private Sub Received_Request_Click()
On Error GoTo Err_Received_Request_Click

If [Received_Request] = True Then
[Received_Date] = Date

DoCmd.SendObject acSendNoObject, , , _
To:=Me.Technican, subject:="Update from Provisioning Department for Circuit " & Me.Primary_ID & "; at " & Me.Cust_Acro & " " & Me.Cust_Address, MessageText:="Updated of status of circuit " & vbCrLf & vbCrLf & "Technician: " & Me.Technican & vbCrLf & "Received Request: " & Me.Received_Date & vbCrLf & "Circuit Ordered: " & Me.Order_Date & vbCrLf & "Guaranteed Date: " & Me.Order_Date + 56 & vbCrLf & "Circuit Installed: " & Me.Installed_Date & vbCrLf & "Data Communication equipment installed: " & Me.Completed_Date & vbCrLf & vbCrLf & Me.Cust_Acro & vbCrLf & Me.Cust_Name & vbCrLf & Me.Cust_Address & vbCrLf & Me.Cust_City & " " & Me.Cust_State & " " & Me.Cust_Zip & vbCrLf & vbCrLf & "Terminal Number: " & Me.Terminal_ID

Exit_Received_Request_Click:
Exit Sub

Err_Received_Request_Click:
MsgBox Err.Description
Resume Exit_Received_Request_Click

End If

If [Received_Request] = False Then
[Received_Date] = Null

End If

End Sub

 

[eholtman]

Update

Just to update if I am not making sense on my issue.

Attach is a snapshot of the form. Basically everytime you check mark a milestone it will automate the date and than send a email. If approved or cancelled it goes back to the form and waits for the next button.

 

[jgc31]

Not sure that you have got your if .. then statements in the right order here. Should be for example

if [request]=true then

do stuff

else

do stuff

End if


Exit statement:

do stuff

Exit sub

Error statement:

do stuff

End sub


Assumming that Received_Request can only be true or false there is no need to test it twice.

 

[eholtman]

Well I only want to email updates if a Request is True so that is why I put the email part in the middle of the if statement.

But I changed my IF statement to: This now doesn't give me a email now. So I assume I was on the right track with putting the email code in the middle of the if statement. But now I am really confused

Private Sub Order_Request_Click()
On Error GoTo Err_Received_Request_Click

If [Order_Request] = True Then
[Order_Date] = Date

Else

[Order_Date] = Null

End If

Exit statement:
DoCmd.SendObject acSendNoObject, , , _
To:=Me.Technican, subject:="Update from Provisioning Department for Circuit " & Me.Primary_ID & "; at " & Me.Cust_Acro & " " & Me.Cust_Address, MessageText:="Updated of status of circuit " & vbCrLf & vbCrLf & "Technician: " & Me.Technican & vbCrLf & "Received Request: " & Me.Received_Date & vbCrLf & "Circuit Ordered: " & Me.Order_Date & vbCrLf & "Guaranteed Date: " & Me.Order_Date + 56 & vbCrLf & "Circuit Installed: " & Me.Installed_Date & vbCrLf & "Data Communication equipment installed: " & Me.Completed_Date & vbCrLf & vbCrLf & Me.Cust_Acro & vbCrLf & Me.Cust_Name & vbCrLf & Me.Cust_Address & vbCrLf & Me.Cust_City & " " & Me.Cust_State & " " & Me.Cust_Zip & vbCrLf & vbCrLf & "Terminal Number: " & Me.Terminal_ID

Exit_Received_Request_Click:
Exit Sub

Err_Received_Request_Click:
MsgBox Err.Description
Resume Exit_Received_Request_Click

End Sub

 

[jgc31]

Yes your email statement needs to go in the if statement so

If [Order_Request] = True Then

[Order_Date] = Date

put docmd.sendobject line here

else

[Order_Date] = Null

end if

Just a thought is the [Order_Date] field empty before this proceedure? if so the default for the field is null so you wouldn't need to set it only to change it from null.

 

[jgc31]

Further thought just as I am shuting my machine down ( the pub is calling very loudly) unless the data type is set to variant you cant assign a null value to it. Normally you would only test for null eg if isnull(order_date) then .... etc not assign it as value because it isnt one.

 

[eholtman]

Ok I cleaned up the if statment but still leaves me with the same problem. With the below code it will only let me email one item corretly. It seems like I want to create a loop but it still not working

Private Sub Received_Request_Click()
On Error GoTo Err_Received_Request_Click

If [Received_Request] = True Then
[Received_Date] = Date

DoCmd.SendObject acSendNoObject, , , _
To:=Me.Technican, subject:="Update from Provisioning Department for Circuit " & Me.Primary_ID & "; at " & Me.Cust_Acro & " " & Me.Cust_Address, MessageText:="Updated of status of circuit " & vbCrLf & vbCrLf & "Technician: " & Me.Technican & vbCrLf & "Received Request: " & Me.Received_Date & vbCrLf & "Circuit Ordered: " & Me.Order_Date & vbCrLf & "Guaranteed Date: " & Me.Order_Date + 56 & vbCrLf & "Circuit Installed: " & Me.Installed_Date & vbCrLf & "Data Communication equipment installed: " & Me.Completed_Date & vbCrLf & vbCrLf & Me.Cust_Acro & vbCrLf & Me.Cust_Name & vbCrLf & Me.Cust_Address & vbCrLf & Me.Cust_City & " " & Me.Cust_State & " " & Me.Cust_Zip & vbCrLf & vbCrLf & "Terminal Number: " & Me.Terminal_ID

Exit_Received_Request_Click:
Exit Sub

Err_Received_Request_Click:
MsgBox Err.Description
Resume Exit_Received_Request_Click
Else

[Received_Date] = Null

End If

End Sub

 

[jgc31]

the code should be

Private Sub Received_Request_Click()

On Error GoTo Err_Received_Request_Click

If [Received_Request] = True Then

[Received_Date] = Date

DoCmd.SendObject acSendNoObject, , , _
To:=Me.Technican, subject:="Update from Provisioning Department for Circuit " & Me.Primary_ID & "; at " & Me.Cust_Acro & " " & Me.Cust_Address, MessageText:="Updated of status of circuit " & vbCrLf & vbCrLf & "Technician: " & Me.Technican & vbCrLf & "Received Request: " & Me.Received_Date & vbCrLf & "Circuit Ordered: " & Me.Order_Date & vbCrLf & "Guaranteed Date: " & Me.Order_Date + 56 & vbCrLf & "Circuit Installed: " & Me.Installed_Date & vbCrLf & "Data Communication equipment installed: " & Me.Completed_Date & vbCrLf & vbCrLf & Me.Cust_Acro & vbCrLf & Me.Cust_Name & vbCrLf & Me.Cust_Address & vbCrLf & Me.Cust_City & " " & Me.Cust_State & " " & Me.Cust_Zip & vbCrLf & vbCrLf & "Terminal Number: " & Me.Terminal_ID

Else

[Received_Date] = Null

End If

Exit_Received_Request_Click:
Exit Sub

Err_Received_Request_Click:
MsgBox Err.Description
Resume Exit_Received_Request_Click

End Sub

This should run every time you click on the check box and if the check box is true run the sendobject command. If it is not doing this suggest putting in a breakpoint at the beginning of the if statement and stepping through the code to check what happens.

 

[eholtman]

Still not working keeps emails 1 or 2 than doesnt do it any more.

I will check the breakpoint.... thing....

Thanks