Nested loop

[scoe]

Hi

I've been trying to automate emails from access and cannot get them to send, now, through trying to resolve the issue I'm having problem with the loop any help would be appreciated with this as it is getting urgent and I'm running out of ideas

The code I have so far is:


[Public Function ULNEmail()
Dim MyDB As Database
Dim MyRS As Recordset
Dim TheAddress As String
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("ULNNotificationEmailQry")
MyRS.MoveFirst
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF


' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAdd]
With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo
.To = TheAddress
.Subject = "IMPORTANT INFORMATION REGARDING YOUR ULN"
.Body = "Hello" & _
vbCrLf & "Everybody who has enrolled at Eastleigh College or who has undertaken training with a partner of Eastleigh College receives a Unique Learner Number (ULN), issued by Eastleigh College. You may not have studied with us directly as we deliver a number of courses through Partner Agencies. Your ULN is:" & _
vbCrLf & [MyRS]![uln] & _
vbCrLf & _
vbCrLf & "Please follow the link http://www.eastleigh.ac.uk/student-s...ir-processing/ for information regarding what a ULN is and assurance that your personal data has been held securely."
.Importance = olImportanceHigh 'High importance

'SendUsingAccount is new in Office 2007
'Change Item(1)to the account number that you want to use
'.SendUsingAccount = objOutlookMsg.Session.Accounts.Item(1)
'.SentOnBehalfOfName = "ulns@eastleigh.ac.uk"
'Add the attachment to the e-mail message.
'Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.display
Else
objOutlookMsg.Send

End If
End With
Next

MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Function]

Thank you

[pbaldy]

What's the problem you're having? Offhand, your With block overlaps the For loop, which may cause a compile error.

[ItsMe]

EDIT: I was typing too slow and did not notice ..

Offhand, your With block overlaps the For loop, which may cause a compile error.

It is a good idea to test your code before adding a loop. Perhaps you have done this already. You did mention that you were sending emails OK and then had an issue with your loop. However, the code you posted here seems to be missing some declarations. Perhaps that is your problem. There isn't anything jumping out at me regarding your loop.

Reference Microsoft Outlook XX.X Library and add the following

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient

Test your code by not iterating and using only the first record within the recordset. Comment out

Do Until MyRS.EOF
objOutlookMsg.Send
MyRS.MoveNext
Loop

and add a msgbox directly after TheAddress = MyRS![EmailAdd]

msgbox TheAddress

[scoe]

Thank you for your help, I have retested using the code below (enclosed by []) and it sends to the first record set without error, when I try to loop using the code in bold I get the error "Compile error: Loop without Do".

[ Dim MyRS As Recordset
Dim TheAddress As String

Dim outApp
Dim outMsg


Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("ULNNotificationEmailQry")
MyRS.MoveFirst


Set outApp = CreateObject("Outlook.Application")
Set outMsg = outApp.CreateItem(0)
TheAddress = MyRS![EmailAddress]

'MsgBox outApp.Session.Accounts.Item(1)

'Un-comment the following four lines and run this so you can see which account number
'in outlook is the ulns. Then comment them again and set the number in the item bellow.

'For i = 1 To outApp.Session.Accounts.Count
' MsgBox outApp.Session.Accounts.Item(i) & " is account number " & i
'Next i
'End

With outMsg
Set objOutlookRecip = .Recipients.Add(TheAddress)
.To = TheAddress
.Subject = "IMPORTANT INFORMATION REGARDING YOUR ULN"
.Body = "Hello"
'Replace the 2 bellow with the ulns account number
Set .SendUsingAccount = outApp.Session.Accounts.Item(1)
.Send
End With]

[MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing]
End Sub


Thank you again

[scoe]

Thank you

I have managed to solve this by adding a do until command.

[pbaldy]

Glad you got it sorted. In my experience the MoveFirst is unnecessary, and it will throw an error if the recordset doesn't return any records.