Loops (1 Viewer)

[gselliott]

I am wanting to loop through all records in a table where the field SEND is true. I have came up with the following after reading through some of the past posts but i am not really familiar with loops.

If Send = False Then
MsgBox "Please select users"
Exit Sub
End If


Dim NameSpace As Object
Dim EmailSend As MailItem
Dim EmailApp As Object

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)


Dim rsEmail As DAO.Recordset
Dim strEmail As String


Set rsEmail = CurrentDb.OpenRecordset("tbl")

Do While Send = True
rsEmail.MoveNext
EmailSend.Subject = "Information Systems - Hardware Audit"
EmailSend.HTMLBody = "TESTING"
EmailSend.Recipients.Add Email
EmailSend.Send
Loop
Set rsEmail = Nothing
MsgBox "Your Message has been sent successfully!"

End Sub


It seems to work fine for the first record in the table and an email message is sent, after that a runtime error is displayed saying "The Item has been moved or deleted"

If someone could point me in the right direction please i would be greatful!

Thanks

 

[CBragg]

This would really benefit me too, any suggestions would be appreciated.

 

[scottfarcus]

Try this...

'BEGIN CODE'

If Send = False Then
MsgBox "Please select users"
Exit Sub
End If

Dim NameSpace As Object
Dim EmailSend As MailItem
Dim EmailApp As Object

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim intEmail as Integer
Dim i as Integer

Set rsEmail = CurrentDb.OpenRecordset("SELECT * FROM tbl WHERE SEND = True")

'Refresh recordset
'There may be a more efficient way of doing this, but I usually use
rsEmail.MoveLast
rsEmail.MoveFirst

'Count the records in the recordset
intEmail = rsEmail.RecordCount

'Set the email subject, body, and add the first recipient email
EmailSend.Subject = "Information Systems - Hardware Audit"
EmailSend.HTMLBody = "TESTING"
EmailSend.Recipients.Add rsEmail!Email

'Loop through all records and add the email addresses, one at a time, to the email item
For i = 1 to intEmail
rsEmail.MoveNext
EmailSend.Recipients.Add Email
Next

'Send the email
EmailSend.Send

Set rsEmail = Nothing
MsgBox "Your Message has been sent successfully!"

End Sub

'END CODE'

I think that will work. Good luck!

 

[CBragg]

Ive tried that and it will only send it to the recipient with the last focus. Im doing this from a continuous form view. If that makes a difference.

Thanks for your help though, i would be gratefull for any other ideas, im totally stuck on this one.

Cheers.

 

[CBragg]

So far this is what we've come up with. However it will only send the email to two records, usually the first two depending on what recordset you input:

DoCmd.GoToRecord , , acFirst

Dim NameSpace As Object
Dim EmailSend As MailItem
Dim EmailApp As Object

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim intEmail As Integer
Dim i As Integer

Set rsEmail = CurrentDb.OpenRecordset("SELECT * FROM tblUsers WHERE SEND = True")

'Refresh recordset
'There may be a more efficient way of doing this, but I usually use
rsEmail.MoveFirst
rsEmail.MoveNext


'Count the records in the recordset
intEmail = rsEmail.RecordCount

'Set the email subject, body, and add the first recipient email
EmailSend.Subject = "Information Systems - Hardware Audit"
EmailSend.HTMLBody = "TESTING"
EmailSend.Recipients.Add rsEmail!Email

'Loop through all records and add the email addresses, one at a time, to the email item
For i = 1 To intEmail
rsEmail.MoveFirst
rsEmail.MoveLast
EmailSend.Recipients.Add Email
Next

'Send the email
EmailSend.Send

Set rsEmail = Nothing
MsgBox "Your Message has been sent successfully!"


Any advice or help would be helpfull to us.

Cheers.

 

[Jeff Bailey]

Ignoring all the email stuff (which is not relevant to the loop problem) here is one approach ...

You have:

Set rsEmail = CurrentDb.OpenRecordset("tbl")

Do While Send = True
rsEmail.MoveNext
EmailSend.Subject = "Information Systems - Hardware Audit"
EmailSend.HTMLBody = "TESTING"
EmailSend.Recipients.Add Email
EmailSend.Send
Loop

This will not work properly because the loop exits as soon as it gets to a row where SEND is false. So ...

if rsEmail.RecordCount = 0 then
'no records in rsEmail. Tell the user and exit gracefully
end if

rsEmail.movefirst '(probably not strictly necessary, but I like to be sure ...)

Do until rsEmail.eof
If rsEmail!SEND = True then
do your email stuff here
End if
rsEmail.movenext
loop

will loop through the recordset and only do the email stuff where SEND is true.

HTH

Jeff

 

[CBragg]

This is what i have now, think its a problem with where to put the email stuff now.

Dim NameSpace As Object
Dim EmailSend As MailItem
Dim EmailApp As Object

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim intEmail As Integer
Dim i As Integer

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

Set rsEmail = CurrentDb.OpenRecordset("tblUsers")

If rsEmail.RecordCount = 0 Then
msgbox "idiot"
End If

rsEmail.MoveFirst

Do Until rsEmail.EOF
If rsEmail!Send = True Then
EmailSend.Subject = "Information Systems - Hardware Audit"
EmailSend.HTMLBody = "TESTING"
EmailSend.Recipients.Add rsEmail!Email
EmailSend.Send

End If
rsEmail.MoveNext
Loop

It is now bringing an object error on the subject and only sends it to the first record. (Ive added all the necessary references) Any more help would be appreciated.

Cheers

 

[CBragg]

I was thinking would it be easier to open a form which has queried whether send was true and to just loop through every record in the form that way?

If so what code would i write?

 

[Jeff Bailey]

It would be very similar ... use the form's recordsetclone as your recordset:

Set rsEmail = Me.RecordsetClone or
Set rsEmail = Forms!FormName.RecordsetClone

rsEmail.movefirst

do until rsEmail.eof
'email stuff here
loop

you wouldn't need to check for SEND = true if you've already filtered the form for SEND = True

Jeff

 

[CBragg]

Ive tried that thanks, but im getting an error mesage after its sent the first message. 'The item has been moved or deleted'

Here's the code:

Dim NameSpace As Object
Dim EmailSend As MailItem
Dim EmailApp As Object

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim intEmail As Integer
Dim i As Integer

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

'Set rsEmail = CurrentDb.OpenRecordset("tblUsers")

Set rsEmail = Me.RecordsetClone
'Set rsEmail = Forms!Form1.RecordsetClone

rsEmail.MoveFirst

Do Until rsEmail.EOF
EmailSend.Subject = "Information Systems - Hardware Audit"
EmailSend.HTMLBody = "TESTING"
EmailSend.Recipients.Add Email
EmailSend.Send 'email stuff here
Loop


Any other ideas??

 

[NOL]

Hi ,
I was trying this out by creating a form in MS Access and
using this code in the onlick event of a button .
It throws up an error which says " Compile Error - Invalid Outside Procedure"
at line Set EmailApp = CreateObject("Outlook.Application")


What could be wrong ?
Would appreciate any help,
Thanks,
Gina.

 

[CBragg]

Make sure that you:

Dim EmailApp As Object

And also in tools, references make sure you have ticked:

Microsoft Outlook 9.0 Object Library.

If this doesnt work, paste your code on and ill have a look.

 

[NOL]

Hi ,
Thanks so much .
It worked with the references added !

Actually I'd hoped that using this method instead of sendobject would eliminate the need for user name and password entry.

But it doesn't.
Is there anyway to communicate directly with the SMTP to send an email ? So that when users click on a button the email goes through without the user having to type in anything?

Thanks,
Gina

 

[CBragg]

If the users are automatically logged into their email account beforehand then no authorisation will be reqiured. It really depends on how you are delivering the application.

In what instance are you delivering email and your application?

I usually get the users to have email open before trying to send something, but thats because we deliver it via citrix using a runtime versoin of Access.

 

[NOL]

Hi C Bragg!!

I have exactly the same setup , i think.
MS Access 2000 Database & MS Access 2000 front End on the Citrix Server.

The email piece in the Access application currently uses SendObject .

When users login through citrix and try to send email through the application , it asks to set up the profile, even though outlook is open on their machines.

Is this is because of Send Object ?
Can i expect that if i can that to Email.Send , it will pick up the local outlook profile and use that to send email ?

I truly hope so !!
Thanks,
Gina .

 

[CBragg]

I use the EmailSend Object, with the Microsoft DOA 3.6 Object Library and Outlook References. My code below works if the users are in email. Try just using this as a test and send it to yourself with you logged into email and see what happens. If you have a similar setup to us then it might be a permissions problem.

When you've done this get back to me either way. Good Luck!!


Dim NameSpace As Object
Dim EmailSend As MailItem
Dim EmailApp As Object

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0) 'Mail Item

EmailSend.Subject = "Test Subject"
EmailSend.Recipients.Add "Name Here"
EmailSend.Send

MsgBox "Thank you for you co-operation, your message has been sent successfully
DoCmd.Quit

Err_Handler:
MsgBox "Your message has not sent, please contact Information Systems"
Exit Sub
DoCmd.Quit

 

[NOL]

Hi ,

I get an message alerting me that "A program is trying to access my address book "
I've attached the screen print in this post.

I don't get this message if i use SendObject.

I'm still runnign this on my local machine and not through citrix.

Please have a look if you can ?
Thanks a lot !

Gina

 

[CBragg]

That will be your problem then, in my case when im using local access i log into the domain and open my email locally through that, dont use the sendobject, it creates complications.

Is that what you've done? or have you got it open via citrix? it should be local.

Have you tried it through citrix?? if not i think you should try that, you will get a better understanding of whats going on.

 

[NOL]

Hi Bragg,

I made the change from SendObject to EmailSend in the copy of the database on my machine and ran it from there.

That's when i get this error .
I'll try it through Citrix .

Thanks for your help !

Gina.

 

[NOL]

Hi Bragg ,

Sorry to bother you again,
But it seems that you have a setup very similar to mine and I'm kinda stuck ..

I tried sending email using EmailSend on Citrix, but I still get prompted to create a profile, it does not use my outlook pfrofile which is already open on my machine ..

Also , everytime i want to send an email, i have to enter user name, password and domain .
I'm lost !

Do you have any ideas?
Thanks,
Gina.