Change a Command Button to send emails "report" I Need Programming Help ASAP" Thanks

[BNW2]

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

[ItsMe]

Leaving out the rs should be OK. it gets a little confusing with the second "With rs" but at a glance that looks good. What catches my eye and I did not spen a lot of time onit, is your comment about a specific state not having email and maybe that is starting the problem. I am a little confused if this code worked before and now it does not what the problem could be.

I understand there is a request by the user to do something different now. Putting that aside, maybe a Do while with an exit do if there is a problem with the state.

If it is a mass email, I would consider removing the dynamic of stEmail to a constant email address. An address that you send to yourself and you can check what your computer is sending. Then put the stEmaillcc as the variable and put it in the BCC part of the SEND. I mention this because it might give you less to loop through and a place to insert an ExitDo if there was a problem.

I might not be real clear on what is going wrong too. Just me glossing over the code and offering places to look.

[BNW2]

Hello ItsMe,

Thanks for responding to my request. All the States have emails within the Email Field for [EmailAddress] and [Email Address1]. Do you think sending 50 emails "at one time" automatically using the code is causing the problems?

[ItsMe]

Hello ItsMe,

Thanks for responding to my request. All the States have emails within the Email Field for [EmailAddress] and [Email Address1]. Do you think sending 50 emails "at one time" automatically using the code is causing the problems?

In short and specifically to this question, no. Something to consider is whether or not you are asking your program to do something while it is still busy. Asking Access to:

DoCmd.SendObject , , , "sharvey@disneyworld.com", , , Subj & SubCycle, msgtext, False

May be an issue if it was within another loop.

[ItsMe]

The IIf statement should be catching any Null issues. Like you say, all of the states have an email so sending that to a loop should not crash.

When did this stop working? What changed? One reason I asked to set aside the user's request to change the code is to focus on what is different now and hoe to THEN work towards solving the user's request.

At a glance, I don't quite understand the:
If strEmail = "" Then
strEmail = strEmailcc
strEmailcc = ""
End If

So I was wondering if Do While was a better option than While to Wend. You could do some data verification in a suspect area and have it Exit Do.

If "There is a problem with the loop" Then
Exit Do
end if

[BNW2]

Hello ItsMe,

Thanks for responding to my request. All the States have emails within the Email Field for [EmailAddress] and [Email Address1]. Do you think sending 50 emails "at one time" automatically using the code is causing the problems?

[ItsMe]

I believe I addressed this in post #4. I could be wrong. I am not testing the code and the action so I can't jump up and down and say, "Heck no." Also, I do not use the DoCmd to execute sending Email.

[ItsMe]

I found the following info on the interWeb

While the SendObject command is useful for sending an object or short
message, it has several significant limitations:

• Messages must be 255 characters or less
• Messages are plain text and cannot be HTML format
• Cannot attach multiple files (limited to one attachment)
• Cannot attach a file on disk
• Cannot filter the data source or report to just the data you need to send
• Cannot specify the FROM address
• Cannot specify settings such as priority, sensitivity, and read receipt
• MAPI security dialog box prompts the user for each email to verify it is
  okay to send
• Doesn't always work with email programs if it's not Outlook, Outlook
  Express, or Exchange

[ItsMe]

BNW2 have you had any luck? I am interested to know your progress. It is helpful if others can see what obstacles or successes you may encounter.

[BNW2]

Hello ItsMe

Everthing is working ok. However, I can only select one State from the listbox and I need to be able to select any State from the listbox for example from NY to WY "having all the States from NY to WY selected" and press the restart button which have the above codes behind it to be able to have those States with the required information from the codes to go into the "TestRestartEmailCollectionTable". As I mentioned before, I can only select one state and that state information is entered into the "TestRestartEmailCollectionTable" after I press the Restart Email Collection button. Also, i have the multipleselect property of the listbox set to "Extended". How can I select for example "2 States" so the States and the required will be entered in the table after I pressed the Restart Email Collection" button? I've searching online for vba codes examples and couldn't find one. Can you help me?

[ItsMe]

For multiselection of the listbox I believe the best way is a For Each statement. So you will need to declare a variant for the multiselection.

dim varEmail as variant
dim strAnswer as string

if me.lstSelection.count = 0 then
exit sub
else

for each varEmail in me.lstSelection.itemselection

strAnswer = strAnswer & me.lstSelection.column (0, varEmail)

next varEmail

end if

I believe this should return every ID for every selected item within the list box. You will probably want to throw in an operator and a literal string at the end of strAnswer

(0, varEmail) & "Special Character to act as a separator for my Email"

something like that. Also you need to get the colomn count correct. 0 is probably the ID in the first row. 1 may be.....

My only example I have uses a second loop and steps through each list item. So I don't have a concatenated string example to paste.

[BNW2]

Hello IsMe

Thanks for responding back to my post. I only have one column for the ListBox called States. I don't have an ID column. Is that causing the problem and do I really need an ID column along with the States Column? But when I change the multiselect to "Simple or Extened", I will not allow me to paste those states I have selected in the "TestRestartEmailColectionTable". But when I changed the MultiSelect to "None", I can only paste one State in the "TestRestartEmailCollectionTable. The code I am using is below:

Private Sub StateNames_Click()
Dim Frm As Form
Dim ctlList As Control
Dim VarItem As Variant
'Return control object varible pointing to list box
Set Frm = Forms![Email Collection]
Set ctlList = Frm!StateNames
'Enumerate through selected items.
For Each VarItem In ctlList.ItemsSelected
'Print value of bound column.
Debug.Print ctlList.ItemData(VarItem)
Next VarItem

End Sub

For multiselection of the listbox I believe the best way is a For Each statement. So you will need to declare a variant for the multiselection.

dim varEmail as variant
dim strAnswer as string

if me.lstSelection.count = 0 then
exit sub
else

for each varEmail in me.lstSelection.itemselection

strAnswer = strAnswer & me.lstSelection.column (0, varEmail)

next varEmail

end if

I believe this should return every ID for every selected item within the list box. You will probably want to throw in an operator and a literal string at the end of strAnswer

(0, varEmail) & "Special Character to act as a separator for my Email"

something like that. Also you need to get the colomn count correct. 0 is probably the ID in the first row. 1 may be.....

My only example I have uses a second loop and steps through each list item. So I don't have a concatenated string example to paste.

[ItsMe]

place this among your other declarations and then compile


dim strEmail as string

then........

[ItsMe]

place the following just above your "next varItem line"


strEmail = strEmail & ctlList.ItemData(0, VarItem)

I have not tested this but looking at what you posted here in this thread and comparing it to the example I posted in post #11, this is what I have come up with. I think you still need the column designator even though you only have one column, not sure though.

[ItsMe]

forgot the separator

strEmail = strEmail & ctlList.ItemData(0, VarItem) & "; "

I belive email addresses use the semicolon