Loop Problem... Need loop inside loop maybe...?

M

mikebaldam

Registered User.
Local time
Today, 11:34
Joined
Oct 29, 2002
Messages
114
Hi,

I have an autoemail form, that emails fine for the email list, i.e. sending individual emails, but I want to add a REF link as a hyperlink in each of the message fields of the emails.

Currently I only get 1 message field Ref.


Not too hot on VB... so go easy...

Cheers in advance..

Mike


Code: 
Private Sub cmd55EmailAllDetails_Click()

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim strRef As String
Dim strMessage As String

Set rsEmail = CurrentDb.OpenRecordset("qrySearchDistinctCustAllProps")
strRef = rsEmail.Fields("Ref").Value
strEmail = rsEmail.Fields("Email").Value
strMessage = "http://www.website.com/details.php?Ref=" & rsEmail.Fields("Ref").Value

rsEmail.MoveNext


Do While Not rsEmail.EOF
strEmail = strEmail & " ; " & rsEmail.Fields("Email").Value
strRef = "http://www.it4life.co.uk/other/TT/fdetails.php?Ref=" & rsEmail.Fields("Ref").Value

rsEmail.MoveNext

Loop

DoCmd.SendObject , , , , , strEmail, "Terry Thomas Estate Agents - Proprerty Updates", strRef, False, False

Set rsEmail = Nothing
MsgBox "The emails are in your emails Outbox and will be sent automatically during the next send recieve"

End Sub
 
Please explain in a bit more detail, getting lost on where its going wrong for you.

also, could you explain what this line is doing?

strMessage = "http://www.website.com/details.php?Ref=" & rsEmail.Fields("Ref").Value

from what i can see, its set to the string nd never used again :S

and at a guess. This line is being over written all the way through the loop

strRef = "http://www.it4life.co.uk/other/TT/fdetails.php?Ref=" & rsEmail.Fields("Ref").Value

try

strRef = strRef & "http://www.it4life.co.uk/other/TT/fdetails.php?Ref=" & rsEmail.Fields("Ref").Value
 
The loop command only loops the email addresses and not the REF hyperlinks.

OK...

Tried that but its still not looping through the hyperlinks. It only displays the first one.

The problem in a bit more detail....

I have a Command Button on a form, that when pressed emails all selected reciepients a list of hyperlinks (looked up from the current DB).

At the moment the emails are working fine.... Multiple recipients in the BCC field.
I just can't get the emails to loop through all of the Ref hyperlinks which are to go in the message field. They just show the first one only.

The loop command only loops the email addresses and not the REF hyperlinks.

Cheers

Mike
 
if im reading that correctly, you want all the ref values from the table in every email..if thats the case then heres what i would do

Code: 
Private Sub cmd55EmailAllDetails_Click()

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim strRef As String
Dim strMessage As String

Set rsEmail = CurrentDb.OpenRecordset("qrySearchDistinctCustAllProps")
strRef = rsEmail.Fields("Ref").Value
strEmail = rsEmail.Fields("Email").Value
strMessage = "http://www.website.com/details.php?Ref=" & rsEmail.Fields("Ref").Value

rsEmail.MoveNext


Do While Not rsEmail.EOF
strRef = strRef & "http://www.it4life.co.uk/other/TT/fdetails.php?Ref=" & rsEmail.Fields("Ref").Value
rsEmail.MoveNext
Loop

rsEmail.MoveFirst
rsEmail.MoveNext

Do While Not rsEmail.EOF
strEmail = strEmail & " ; " & rsEmail.Fields("Email").Value
rsEmail.MoveNext
Loop

DoCmd.SendObject , , , , , strEmail, "Terry Thomas Estate Agents - Proprerty Updates", strRef, False, False

Set rsEmail = Nothing
MsgBox "The emails are in your emails Outbox and will be sent automatically during the next send recieve"

End Sub

Hope this works
 
Amazing - Thanks

Cheers Workmad3,


That got it going... I needed to play a little with the formatting.

In case anyone else wants to see the end code.



Code: 
Private Sub cmd55EmailAllDetails_Click()

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim strRef As String
Dim strMessage As String

Set rsEmail = CurrentDb.OpenRecordset("qrySearchDistinctCustAllProps")
strRef = rsEmail.Fields("Ref").Value
strEmail = rsEmail.Fields("Email").Value
strMessage = " http://www.website.com/details.php?Ref=" & rsEmail.Fields("Ref").Value

rsEmail.MoveNext

Do While Not rsEmail.EOF
strRef = " http://www.it4life.co.uk/other/TT/fdetails.php?Ref=" & rsEmail.Fields("Ref").Value & strRef
rsEmail.MoveNext
Loop

rsEmail.MoveFirst
rsEmail.MoveNext

Do While Not rsEmail.EOF
strEmail = strEmail & " ; " & rsEmail.Fields("Email").Value
rsEmail.MoveNext
Loop

DoCmd.SendObject , , , , , strEmail, "Terry Thomas Estate Agents - Property Updates", strRef, False, False

Set rsEmail = Nothing
MsgBox "The emails are in your emails Outbox and will be sent automatically during the next send recieve"

End Sub



Cheers Again....

Mike
 
Code: 
Private Sub cmd55EmailAllDetails_Click()

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim strRef As String
Dim strMessage As String

Set rsEmail = CurrentDb.OpenRecordset("qrySearchDistinctCustAllProps")
strRef = rsEmail.Fields("Ref").Value
strEmail = rsEmail.Fields("Email").Value
strMessage = " http://www.website.com/details.php?Ref=" & rsEmail.Fields("Ref").Value

rsEmail.MoveNext

Do While Not rsEmail.EOF
strEmail = strEmail & " ; " & rsEmail.Fields("Email").Value
strRef = " http://www.it4life.co.uk/other/TT/fdetails.php?Ref=" & rsEmail.Fields("Ref").Value & strRef
rsEmail.MoveNext
Loop

DoCmd.SendObject , , , , , strEmail, "Terry Thomas Estate Agents - Property Updates", strRef, False, False

Set rsEmail = Nothing
MsgBox "The emails are in your emails Outbox and will be sent automatically during the next send recieve"

End Sub

looked at it again, and i think the above should work, and avoid looping through the recordset twice. I'd missed the formatting problem

Hope this helps in code efficiency :)
 
You must log in or register to reply here.

Similar threads

J
Help with Email VBA
Replies
4
Views
1,025
Gasman
Gasman
J
Email with textbox value in message
Replies
1
Views
699
Gasman
Gasman
C
.MoveNext not working
2 3
Replies
44
Views
6,022
SHANEMAC51
SHANEMAC51
M
Access VBA Conditional Loop
Replies
13
Views
1,930
namliam
N
corentin
Problem to insert last autonumber record ID into an inster into statement in a loop ...
Replies
7
Views
1,612
moke123
moke123

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom