Need help sending email from Access using Outlook

[sklambert]

Hello,

I have coded my issue two different ways. I am trying to send an email for each record in a query. In the first set of code, I am using SendObject. When I use this method, the first email goes out fine, then Access shuts down, saying the program has performed an illegal operation. Access appears to bomb while trying to move to the next record.

In the second set of code, I get an error "the item has been moved or deleted", and the debugger points to the line containing ".To = strEmail". I have a reference to Microsoft Outlook 9.0 Object Library checked. Can someone please assist me with what I am doing wrong? Both sets of code follow:

-1-
Private Sub SEND_OVERDUE_EMAILS_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ORIGIN, DESTINATION, SENDTO, SUBJECT, MESSAGE As String

Set db = CurrentDb
Set rs = db.OpenRecordset("NOT COMPLETED", dbOpenDynaset)
rs.MoveFirst
Do Until rs.EOF

'***set references from your form
ORIGIN = "sharron.lambert@ipacc.com"
DESTINATION = rs![programmer email]
SENDTO = rs![programmer email]
SUBJECT = "FOLLOW UP ON ASSIGNMENT NUMBER " & rs![task number] & " - " & rs![TASK NAME]
MESSAGE = rs![PROGRAMMER] & "," & Chr(13) & Chr(13) & _
"Please provide a status update on this assignment." & Chr(13) & Chr(13) & _
"****************************************************************" & Chr(13) & Chr(13) & _
"ASSOCIATED TASKS: " & rs![ASSOCIATED TASKS] & Chr(13) & Chr(13) & _
"ASSIGN DATE: " & rs![assign date] & Chr(13) & Chr(13) & _
"DUE DATE: " & rs![DUE DATE] & Chr(13) & Chr(13) & _
"DESCRIPTION: " & rs![TASK OBJECTIVE] & Chr(13) & Chr(13) & _
"Thanks," & Chr(13) & _
"Sharron Lambert"

DoCmd.SendObject , , , SENDTO, , , SUBJECT, MESSAGE, False
rs.MoveNext
Loop

MsgBox ("PLEASE OPEN OUTLOOK TO SEND EMAILS"), , "OPEN OUTLOOK"
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
MsgBox ("OVERDUE EMAILS SENT")
End Sub


-2-
Private Sub SENDMAILVBA_Click()
'******begin code*****
Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim db As DAO.Database
Dim rs As DAO.Recordset
'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
'**************************************************************
Set db = CurrentDb
Set rs = db.OpenRecordset("NOT COMPLETED", dbOpenDynaset)

rs.MoveFirst
Do Until rs.EOF

strEmail = rs![programmer email]
strBody = rs![PROGRAMMER] & "," & Chr(13) & Chr(13) & _
"Please provide a status update on this assignment." & Chr(13) & Chr(13) & _
"****************************************************************" & Chr(13) & Chr(13) & _
"ASSOCIATED TASKS: " & rs![ASSOCIATED TASKS] & Chr(13) & Chr(13) & _
"ASSIGN DATE: " & rs![assign date] & Chr(13) & Chr(13) & _
"DUE DATE: " & rs![DUE DATE] & Chr(13) & Chr(13) & _
"DESCRIPTION: " & rs![TASK OBJECTIVE] & Chr(13) & Chr(13) & _
"Thanks," & Chr(13) & _
"Sharron Lambert"

'***creates and sends email
With objEmail
.To = strEmail
.SUBJECT = "FOLLOW UP ON ASSIGNMENT NUMBER " & rs![task number] & " - " & rs![TASK NAME]
.body = strBody
.Send
End With
rs.MoveNext
Loop
Set objEmail = Nothing
'****closes Outlook. remove if you do not want to close Outlook
'objOutlook.Quit
'MsgBox ("PLEASE OPEN OUTLOOK TO SEND EMAILS"), , "OPEN OUTLOOK"
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
MsgBox ("OVERDUE EMAILS SENT")

End Sub
'****end code*****

 

[PHV]

1) First time I see calling DoCmd.SendObject without any object ...
2) Try to create a new objEmail object inside the loop.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244