Hello All,
Getting stumped by some vba and query work that I've nearly finished.
I've got a form that shows records from a table. This form behaves like a checklist and only shows "open orders." When those orders are complete the worker chooses a yes or a no option from a combo box, and that choice determines where an email is sent and what QUERY to run.
TRUE = Email is sent to in house users
FALSE = Email is sent to users outside the 'house, and the list of users will vary based on where they are. I have a table of contacts responsible for different areas and the form will autofill that area based on the material number I'm working with; i only want to send emails to the relevant users and not ALL of the out of house users.
my problem is this: Each record has a different set of out of house users associated with it and when i run the "PlantSLQuery" un filtered it populates the recipient column with the emails related to EVERY record in the table and not the one listed in the form. Also, when I add a filter to the query to only show things that are relevant to the form, the query shows the records correctly, but doesn't allow the VBA to function properly. I get a 3061 error whenever I run this VBA with a form filter on the PlantSLQuery, even though I can open the query itself and see the information.
Any help would be AWESOME. Bolded text is the line that throughs the error out.
Also worth noting, I have a second IF with identical code, but a different query running in bold. That query has a filter based on text criteria on it, and works perfectly fine.
Code snippet below:
If Me.TrueMispickCombo = "False" Then
Dim rs As Object
Dim I As Integer
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim strBody As String
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("PlantSLQuery")
'Email body message
Do While Not rs.EOF
Emailto = rs!
rs.MoveNext
.Recipients.Add Emailto
Loop
.Display
strBody = "Inventory investigation results for mispick involving " & Me.MatRecBox & " and " & Me.MatNeedBox & " are as follows:" & "<br>" & _
"<br><b>" & "Received: " & "</b>" & Me.MatRecBox & " - System: " & Me.NSystem & " Physical: " & Me.NActual & " Difference: " & Me.NDiff & _
"<br><b>" & "Needed: " & "</b>" & Me.MatNeedBox & " - System: " & Me.RSYS & " Physical: " & Me.RActual & " Difference: " & Me.RDIFF & _
"<br><b>" & "Counted By: " & "</b>" & Me.CountedBy & _
"<br><b>" & "Comments: " & "</b>" & Me.CommentsBox & "<b>" & _
"<br>" & strTableBody & "<br>" & .HTMLBody
Set rs = Nothing
Set rs = CurrentDb.OpenRecordset("MispickQueryEmail")
.Subject = "Mispick Investigation Results " & Me.MatRecBox & " and " & Me.MatNeedBox
.HTMLBody = strBody
End With
Getting stumped by some vba and query work that I've nearly finished.
I've got a form that shows records from a table. This form behaves like a checklist and only shows "open orders." When those orders are complete the worker chooses a yes or a no option from a combo box, and that choice determines where an email is sent and what QUERY to run.
TRUE = Email is sent to in house users
FALSE = Email is sent to users outside the 'house, and the list of users will vary based on where they are. I have a table of contacts responsible for different areas and the form will autofill that area based on the material number I'm working with; i only want to send emails to the relevant users and not ALL of the out of house users.
my problem is this: Each record has a different set of out of house users associated with it and when i run the "PlantSLQuery" un filtered it populates the recipient column with the emails related to EVERY record in the table and not the one listed in the form. Also, when I add a filter to the query to only show things that are relevant to the form, the query shows the records correctly, but doesn't allow the VBA to function properly. I get a 3061 error whenever I run this VBA with a form filter on the PlantSLQuery, even though I can open the query itself and see the information.
Any help would be AWESOME. Bolded text is the line that throughs the error out.
Also worth noting, I have a second IF with identical code, but a different query running in bold. That query has a filter based on text criteria on it, and works perfectly fine.
Code snippet below:
If Me.TrueMispickCombo = "False" Then
Dim rs As Object
Dim I As Integer
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim strBody As String
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("PlantSLQuery")
'Email body message
Do While Not rs.EOF
Emailto = rs!
rs.MoveNext
.Recipients.Add Emailto
Loop
.Display
strBody = "Inventory investigation results for mispick involving " & Me.MatRecBox & " and " & Me.MatNeedBox & " are as follows:" & "<br>" & _
"<br><b>" & "Received: " & "</b>" & Me.MatRecBox & " - System: " & Me.NSystem & " Physical: " & Me.NActual & " Difference: " & Me.NDiff & _
"<br><b>" & "Needed: " & "</b>" & Me.MatNeedBox & " - System: " & Me.RSYS & " Physical: " & Me.RActual & " Difference: " & Me.RDIFF & _
"<br><b>" & "Counted By: " & "</b>" & Me.CountedBy & _
"<br><b>" & "Comments: " & "</b>" & Me.CommentsBox & "<b>" & _
"<br>" & strTableBody & "<br>" & .HTMLBody
Set rs = Nothing
Set rs = CurrentDb.OpenRecordset("MispickQueryEmail")
.Subject = "Mispick Investigation Results " & Me.MatRecBox & " and " & Me.MatNeedBox
.HTMLBody = strBody
End With