SendObject

I'm using SendObject method to send a data where i fetch from a table. The problem i'm facing rite now is I just can send the mail only once. In my form I have 2 textboxes, 1 for E-mail subject the other one is for the message box, 2 command buttons 1 for fetching data from table to put into the msg box and the other one for sending. Below is the code

Fetching data
Private Sub Command2_Click()
Dim dbs As Database
Dim rst As DAO.Recordset



Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("MyTable")

'// MSG1 is Text Box for Message body

Do While Not rst.EOF
Me.MSG1.Value = Me.MSG1.Value & rst!Filed1 & " " & rst!Field2 & " " & "verified by " & rst!Field3 & " (" & rst!Field4 & ")" & Chr(13) + Chr(10)
Me.MSG1.ScrollBars = 2
rst.MoveNext
Loop

rst.close

End Sub

Sending E-mail


Private Sub Command3_Click()
Dim MSG, subj2 As String

MSG = Me.MSG1.Value
subj2 = Me.Subject1.Value
DoCmd.SendObject acSendNoObject, , , "receiver", , , subj2, MSG, False


pls help me

Can't you just put it in a loop? If two different email recipients, skip the loop and send twice.

VB Code:

Private Sub Command3_Click()
Dim MSG, subj2 As String
dim i as integer
 
MSG = Me.MSG1.Value
subj2 = Me.Subject1.Value
 
For i = 1 to 2
DoCmd.SendObject acSendNoObject, , , "receiver", , , subj2, MSG, False
Next
'or if skipping loop
DoCmd.SendObject acSendNoObject, , , "other_receiver", , , subj2, MSG, False

Hi salvelinus,

I need to send to same group but different msg. Now If wanna send again with a different msg i have to quit access and run back the application. If not the second I want to use it the button is useless. Need ur help man... btw thanks a lot with ur last suggestion.

Instead of getting the recordset in a Click event, put it in a separate function. Add a parameter to the new function to check which recordset should be retrieved, then call the new function twice from the Click event with different arguments.

VB Code:

Private Sub command1_click()
  GetData(1)
  Getdata(2)
End Sub
 
Private Sub GetData(i as integer)
  Dim dbs As Database
  Dim rst As DAO.Recordset
 
  if i = 1 then
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("MyTable")
 
    '// MSG1 is Text Box for Message body
 
    Do While Not rst.EOF
      Me.MSG1.Value = Me.MSG1.Value & rst!Filed1 & " " & rst!Field2   & " " & "verified by " & rst!Field3 & " (" & rst!Field4 & ")" & Chr(13) + Chr(10)
      Me.MSG1.ScrollBars = 2
      rst.MoveNext
    Loop
 
    rst.close
  Else
    'Do what you need to get the other data
  End If
 
  DoCmd.SendObject acSendNoObject, , , "receiver", , , subj2, MSG, False
End Sub

Filling your textbox may be able to be moved out of the If statement if you're filling the same box with the same indexed fields of either recordset. In that case just put the code to create the 2nd recordset in the else statement.