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

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

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!

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.

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.

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

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

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?

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

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??

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.

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.

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

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.

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 .

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

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

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.

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.

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.

If you're running outlook through citrix and you have that open, then i dont see why it shouldnt just send it. The Username, Domain and Password pop up seems to me that its a local outlook looking for NT Password Authentecation.

If outlook and your database are published applications and your having to create a new profile, it may be that citrix or your profile has screwed. If this is the case try testing it with another user.

Hi ,

Thanks so much for your patience ...

I've tried the following steps with other users , and get the same results:

1. Login to Outlook on local Machine with user name password and domain
2. Login to Citrix with NT user and password
3. Application uses EmailSend code (instead of SendObject)
4. Click button to send email
5. Application looks for outlook client on citrix server, asks to create a profile if one does not exist.
If profile exists, it prompts to choose profile, setup on citrix server
and login with username , passowrd and domain
6. Click button again to send email, Outlook login screen pops up again

Do I need to check some specific permissions on citrix server ?

Thanks,
Gina.

As far as i know, you shouldnt need any other permissions other than access to the working directory.

You could try setting up your machine to log into the domain when your PC boots up. Change this in network nieghbourhood, put the relevant dns and gateway ect, then check the log into doman box and enter your domain. Restart your machine and log into the domain, then try to run it both locally and through citrix. This should determin whether its a citrix problem or not.

Let me know how you get on.

Thanks Bragg ..
I have tried everything.
It works perfectly fine on my machine, the moment i run the very same application through Citrix, I get prompted for user/pass

'Am kind of at a dead end.
would really be grateful for any help from anyone right now !

I was thinking of some way to write directly to the SMTP , but the code i found asks for windows socket programming .
I'm running against deadlines and don't have time for trial & error.

If anyone has worked on this kind of approach, It'd really help .

Thanks in Advance..
Gina.

The only way i could think to get it working would be to set up your users on exchange and give them outlook as a published application on citrix.

Otherwise im all out of ideas, sorry.

Hi Bragg,
Thanks a lot for you help and patience.
Dunno what I'd do without people like you and this forum :)

I did find another solution to my problem and it is very good, got rid of everything that was bothering me !
Though I'm not quite sure how it works ...

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=28669&perpage=15&pagenumber=1


( copy paste the link in IE, dunno how to point to a post in this forum)

Thanks,
Gina.