Loop same record

[atticus1802]

Good Evening,

I have been trying to get this work for days. I have tried various pieces of code but this is the closest I can get to it working. I have a piece of code which populates an email with values from a datasheet. The datasheet contains multiple records however when the code is run I am shown only the record which has focus. This is populated for how many rows there are in a data sheet. I.e. 3 rows show 3 of the same emails.

Any help you can give would be greatly appreciated.

Private Sub cmd_sendmail_Click()
'Create mail merge
Set objOL = CreateObject("Outlook.Application")
Dim olApp As Object
Dim Address As String
Dim CCAddress As String
Dim Body As String
Dim rs As dao.Recordset
Forms!frmClearwell_Access_Entry!frmDatasheet.SetFocus
With Forms!frmClearwell_Access_Entry
Set rs = frmDatasheet.Form.RecordsetClone
Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open
If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If
     
Address = frmDatasheet!txt_Email_Address
CCAddress = frmDatasheet!txt_primemail + "; " + frmDatasheet!txt_secondemail
Body = "<p style='font-family:Calibri (Body);font-size:16'>" & "Dear " & frmDatasheet!txt_shortname & ", <br><br> Please find below your login details for the document review platform. <br><br > You can access it at the following link: <a href= 'https://l" & frmDatasheet!txt_CW_Box & "..com/esa'> [URL="https://clearwell"]https://[/URL]" & frmDatasheet!txt_CW_Box & "..com/esa</a>" & _
"<br><br> Your login details are as follows:- <br><br> Username: " & frmDatasheet!txt_User_ID & "<br> Password: " & frmDatasheet!txt_Password & _
"<br><br> I would recommend copying and pasting your password the first time you attempt to log in. <br><br> Please change this password as soon as possible, by clicking on the shown link:" & "<br><br> <img src='cid:CWpassword.jpg' height=100 width=210>" & "<br><br> If you have any queries, please email myself or " & frmDatasheet!com_Secondarycontact & " at <a href='mailto:" & frmDatasheet!txt_secondemail & "'>" & frmDatasheet!txt_secondemail & "</a>" & "<br><br> Kind Regards, <br><br>" & frmDatasheet!txt_primshortname & _
"<span style='Arial;font-size:13pt;color:rgb(128,128,128)'>" & "<br><b>___________________________________________________________</b><br>"</p>"
With rs
rs.MoveFirst
Do While Not rs.EOF
Set objMail = objOL.CreateItem(olMailItem)
objMail.Attachments.Add "P:\Project Database\Backend Database\Mail_Merge\CWpassword.jpg"
objMail.To = Address
objMail.CC = CCAddress
objMail.BodyFormat = olFormatHTML
objMail.Subject = "Login Details"
objMail.HTMLBody = Body
objMail.Display
rs.MoveNext
Loop
End With
rs.Close
Set olApp = Nothing
Set objMail = Nothing
End With
End Sub

 

[pbaldy]

Your not getting values from the recordset, you're getting them from the form. Try referencing it like:

objMail.To = rs!Address

 

[atticus1802]

Your not getting values from the recordset, you're getting them from the form. Try referencing it like:

objMail.To = rs!Address

Thank you for the quick reply. I assumed it was something like this. However if I user rs!address I get Run Time error 3265, "Item not found in this collection". My VB is not amazing so assume it is because rs! is not a string.

I have tried referencing the field names as rs! however this does not appear to work either.

 

[pbaldy]

It sounds like "address" is not a field in the form's source?

 

[atticus1802]

Address is a string as shownbelow:-


Dim Address As String
Address = frmDatasheet!txt_Email_Address

 

[pbaldy]

Then set the variable using the recordset method instead of the form.

 

[atticus1802]

I have tried this as

Address = rs!frmDatasheet!txt_Email_Address AND
Address = rs!txt_Email_Address

aswell as

Address = rs.frmDatasheet!txt_Email_Address AND
Address = rs.txt_Email_Address

No luck I get the same error I get Run Time error 3265, "Item not found in this collection". Maybe I am referencing this wrong.

 

[pbaldy]

I would expect the second one to work. Can you post the db here?

 

[atticus1802]

Sorry about the wait in a reply. I really aprreciate your help.

I have attached a sample of my database and removed information. Some buttons may not work. Basically I have populated some test data into the form "frm_Access_Entry" the button "Send Mail Merge" should populate the information from the datasheet subform.

Any help you can give would be greatly appreciated.View attachment Test Mail Merge.zip

 

[pbaldy]

You're referring to the textbox, not the field:

Address = !Email_Address

Also, this line and any others referring to the recordset should be within the loop.

 

[atticus1802]

Thank you so much. That worked you are a genius

 

[pbaldy]

Happy to help!