Hello my coworker is having issues with a command button is when sending email to a State, it would stop processing at a particular State using the first command button. However, the second button "restart button" my coworker wants me to change the code to allow my coworker to select any State and so that other States with my coworker having to select the States will be emailed automatically attaching a report until the States have been been finished emailed. However, there is a parameter query which prompts my coworker to enter a state but my coworker doesn't want that any more. In addition, through the process of the code there is a Macro "which runs the parameter query" and a recordset that is opened and the code continue to run an email in sent to a State. My question is how can I change the code to help my coworker to stop entering a State so she can choose any State and the rest of the States will be processed without my coworker having to choose the other States that follows? Here is an example of the code below. Also, do you think that not having the "rs" in front of .movenext is preventing the program from processing "sending emails" to the other states when my coworker press the "first command" button to send emails to all of the states? The code below is for the send command button. "Its similar to the code for the first command button. Only difference is it have only Recordset and a different table name. I need help!!!!. Can I create a combo box listing the states and she can chose the sTates from a combobox and how can I program that using VBA codes so the combobox can be linked to the second command button? Or, how can I insert a code or a popup form with vba code for states from a combo box in the below codes to be able to send emails using the email fields below? I would post the query but it is too long and have many tables.
DoCmd.RunMacro ("Test Email Collection") - this is a parameter query
'Set rs = db.OpenRecordset("TestRestartEmailCollectionTable" )
With rs
While Not .EOF
SubCycle = !Cycle
Me.STATE = !St
Me.weeklyCount = !Count
Me.AMOUNT = ![AMOUNT] * 0.01
strEmail = IIf(IsNull(![EMail Address]), "", ![EMail Address])
strEmailcc = IIf(IsNull(![EMail Address 1]), "", ![EMail Address 1])
If strEmail = "" And strEmailcc = "" Then
MsgBox "No email address is available for the State of " & !STATE & "!", vbInformation
Else
If strEmail = "" Then
strEmail = strEmailcc
strEmailcc = ""
End If
StateName = !St
'SendMail "Email Collection", strEmail, strEmailcc, , Subj & !Cycle, Msgtext, False
'DoCmd.SendObject acSendReport, "Email Collection", acFormatRTF, strEmail, strEmailcc, , "Weekly Confirmation for Cycle " & !Cycle, vbCr & vbCr & "The attached word document is your notification of this week's Processable file." & vbCr & vbCr & vbCr & "Thanks" & vbCr & vbCr & vbCr & "TOP Operations" & vbCr & vbCr & vbCr & vbCr & "703-305-2314", False
DoCmd.SendObject acSendReport, "TestEmail Collection", acFormatRTF, strEmail, strEmailcc, , Subj & !Cycle, msgtext, False
StateName = ""
End If
.MoveNext
Wend
With rs
DoCmd.SendObject , , , "sharvey@disneyworld.com", , , Subj & SubCycle, msgtext, False
End With
MsgBox ("The Collection Confirmation has been sent successfully")
.Close
End With
Else ' User chose No.
MsgBox ("Good Bye >>>>>")
End If
End Sub