Sending E-mails via Access

[RSteph]

I have an access database that generates a report, then sends the report out
as an attachment via e-mail (through Outlook) to a list of users. The problem
I'm having is that when I start sending the e-mails it ends up locking up my
computer, and then crashing Outlook; so I'll get through part of the list,
then I'll have to close the program, then go back in, and step through the
code to get back to the last person that an e-mail was sent to, and start up
again on the next person.

What I would like to do is first, add a some "Do Events" type of code, so
that the computer doesn't lock up while it's running through the loop sending
all the e-mails. Next, I'd like to put it some kind of timer, or delay, so
that after it sends an e-mail it'll wait (say maybe 4-5 seconds) before
continuing the loop and starting the next one. I'm hoping that adding these
two items will help prevent my computer from locking up, and prevent Outlook
from crashing.

That being said, it has been a long time since I have done much coding in
Access (or any of the office products for that matter). Can anyone help me
with the best way to do this, or perhaps point me to a web resource that
would help me get something like this put together (get the timer and do
events items added in to the code I've already put together)?

Any help would be greatly appreciated.

Thank you.

 

[schasteen]

Are you sending the same report to each person? Why don't you generate a To
list and send 1 email instead of sending the same email multiple times?
Also, please post your code to make it easier for people to make suggestions.

 

[RSteph]

I'm sorry, I should have mentioned (and I completely forgot to point this
out), the e-mails have information specific to each individual. It's a
report, that pulls info from a query and fills in the information for a given
person, sends the e-mail, then the next person, sends their e-mail, etc. so I
can't use a distribution list, or a large "To:" list... I appologize for not
pointing that out earlier.

Here's the code I have that actually sends the e-mail, this is being run
through Access 2007, with an add-on that lets me send PDF format:

While Not RS.EOF
If Not IsNull(RS!EmailAddress) Then
Me.Contact = RS!ContactID
If Not IsNull(Me.Contact) Then
DoCmd.OutputTo acOutputReport, docname,
acFormatPDF, "C:\database\report.pdf"
Me.Contact = Null
End If

If 1 = 1 Then
' Create the message.
Set objOutlookMsg =
objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the
message.

Set objOutlookRecip =
..recipients.Add(RS!EmailAddress)
objOutlookRecip.Type = olTo

If rsOptions!HTML = True Then
.HTMLBody = rsOptions!Body
Else
.Body = rsOptions!Body
End If

' Set the Subject, Body, and
Importance of the message.
.Subject = rsOptions!Subject
.Importance = olImportanceNormal

' Add attachments to the message.
Set objOutlookAttach =
..Attachments.Add("c:\Assoc11\Invoice.pdf")

' Resolve each Recipient's name.
For Each objOutlookRecip In
..recipients
objOutlookRecip.Resolve
Next

.Save
.Send
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing
End With
Set objOutlookMsg = Nothing
End If
End If
RS.MoveNext
Wend

 

[Stefan Hoffmann]

hi,

The problem
I'm having is that when I start sending the e-mails it ends up locking up my
computer, and then crashing Outlook;

Sounds like a problem in your e-mail sending code...


mfG
--> stefan <--