How to send email from Access without opening Outlook

[Jack]

Hi,
I am testing the following code to send email based on criteria from a
query. If there are three records then three emails will be sent by looping
three times in the recordset of the query. However I do not want Access to
open up and I need to click send to send the email. I want the emails to be
send automatically on the click of a form button. I would appreciate any help
to resolve this. Thanks
CODE:
Dim db As DAO.Database, rs As DAO.Recordset
Dim sSQL As String

'Set environment
Set db = CurrentDb
DoCmd.SetWarnings False

'Open the controlling recordset
Set rs = db.OpenRecordset("qrySendEmailAdvice")
While Not rs.EOF

'For each record (CaseID) send an email
DoCmd.SendObject acSendNoObject, , , rs![EmailAddress], , , "Case
Closing Reminder", _
"Dear " & rs![Employee] & vbCrLf & vbCrLf & "Your case
corresponding to " & rs![CaseID] & _
vbCrLf & vbCrLf & "is five days overdue. You need to work on this
to close it"

'Update the Booking table so that emails are not duplicated
sSQL = "UPDATE tblEmployeeCase SET EmailSent=-1 where CaseID=" &
rs![CaseID]
db.Execute sSQL

'Cycle on to the next record
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
'Control recordset now closed

'Reset environment
DoCmd.SetWarnings True

 

[Graham Mandeno]

Hi Jack

The 8th (last) argument of the SendObject method is "EditMessage", a boolean
which controls whether or not the email client displays the message for
editing before it is sent. The default value for this argument is True.
You need to pass False instead:

DoCmd.SendObject acSendNoObject, , , rs![EmailAddress], , ,

....
to close it", False

 

[Jack]

Hi Graham,
Thanks for pointing out the issue that I was having. However when I clciked
the button with the changed code, the Outlook form did not open up for edit
Rather emails were sent automatically. However here I am getting duplicate
emails. There are two records and there should be two emails and I am getting
four. Any idea why so. Thanks

Hi Jack

The 8th (last) argument of the SendObject method is "EditMessage", a boolean
which controls whether or not the email client displays the message for
editing before it is sent. The default value for this argument is True.
You need to pass False instead:

DoCmd.SendObject acSendNoObject, , , rs![EmailAddress], , ,

....
to close it", False

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,
I am testing the following code to send email based on criteria from a
query. If there are three records then three emails will be sent by
looping
three times in the recordset of the query. However I do not want Access to
open up and I need to click send to send the email. I want the emails to
be
send automatically on the click of a form button. I would appreciate any
help
to resolve this. Thanks
CODE:
Dim db As DAO.Database, rs As DAO.Recordset
Dim sSQL As String

'Set environment
Set db = CurrentDb
DoCmd.SetWarnings False

'Open the controlling recordset
Set rs = db.OpenRecordset("qrySendEmailAdvice")
While Not rs.EOF

'For each record (CaseID) send an email
DoCmd.SendObject acSendNoObject, , , rs![EmailAddress], , , "Case
Closing Reminder", _
"Dear " & rs![Employee] & vbCrLf & vbCrLf & "Your case
corresponding to " & rs![CaseID] & _
vbCrLf & vbCrLf & "is five days overdue. You need to work on
this
to close it"

'Update the Booking table so that emails are not duplicated
sSQL = "UPDATE tblEmployeeCase SET EmailSent=-1 where CaseID=" &
rs![CaseID]
db.Execute sSQL

'Cycle on to the next record
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
'Control recordset now closed

'Reset environment
DoCmd.SetWarnings True

 

[Graham Mandeno]

Hi Jack

I have never heard of a problem where a single SendObject sends multiple
emails.

I suggest you set a breakpoint in your code on the DoCmd.SendObject line and
see how many times it gets visited.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Thanks for pointing out the issue that I was having. However when I
clciked
the button with the changed code, the Outlook form did not open up for
edit
Rather emails were sent automatically. However here I am getting duplicate
emails. There are two records and there should be two emails and I am
getting
four. Any idea why so. Thanks

Hi Jack

The 8th (last) argument of the SendObject method is "EditMessage", a
boolean
which controls whether or not the email client displays the message for
editing before it is sent. The default value for this argument is True.
You need to pass False instead:

DoCmd.SendObject acSendNoObject, , , rs![EmailAddress], , ,

....
to close it", False

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,
I am testing the following code to send email based on criteria from a
query. If there are three records then three emails will be sent by
looping
three times in the recordset of the query. However I do not want Access
to
open up and I need to click send to send the email. I want the emails
to
be
send automatically on the click of a form button. I would appreciate
any
help
to resolve this. Thanks
CODE:
Dim db As DAO.Database, rs As DAO.Recordset
Dim sSQL As String

'Set environment
Set db = CurrentDb
DoCmd.SetWarnings False

'Open the controlling recordset
Set rs = db.OpenRecordset("qrySendEmailAdvice")
While Not rs.EOF

'For each record (CaseID) send an email
DoCmd.SendObject acSendNoObject, , , rs![EmailAddress], , , "Case
Closing Reminder", _
"Dear " & rs![Employee] & vbCrLf & vbCrLf & "Your case
corresponding to " & rs![CaseID] & _
vbCrLf & vbCrLf & "is five days overdue. You need to work on
this
to close it"

'Update the Booking table so that emails are not duplicated
sSQL = "UPDATE tblEmployeeCase SET EmailSent=-1 where CaseID=" &
rs![CaseID]
db.Execute sSQL

'Cycle on to the next record
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
'Control recordset now closed

'Reset environment
DoCmd.SetWarnings True

 

[Jack]

Hi Graham,
I am not sure why I got two sets of email. I ran the code again and
everything works great. Thanks for all your help. i appreciate it.

Hi Jack

I have never heard of a problem where a single SendObject sends multiple
emails.

I suggest you set a breakpoint in your code on the DoCmd.SendObject line and
see how many times it gets visited.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Thanks for pointing out the issue that I was having. However when I
clciked
the button with the changed code, the Outlook form did not open up for
edit
Rather emails were sent automatically. However here I am getting duplicate
emails. There are two records and there should be two emails and I am
getting
four. Any idea why so. Thanks

Hi Jack

The 8th (last) argument of the SendObject method is "EditMessage", a
boolean
which controls whether or not the email client displays the message for
editing before it is sent. The default value for this argument is True.
You need to pass False instead:

DoCmd.SendObject acSendNoObject, , , rs![EmailAddress], , ,

....
to close it", False

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,
I am testing the following code to send email based on criteria from a
query. If there are three records then three emails will be sent by
looping
three times in the recordset of the query. However I do not want Access
to
open up and I need to click send to send the email. I want the emails
to
be
send automatically on the click of a form button. I would appreciate
any
help
to resolve this. Thanks
CODE:
Dim db As DAO.Database, rs As DAO.Recordset
Dim sSQL As String

'Set environment
Set db = CurrentDb
DoCmd.SetWarnings False

'Open the controlling recordset
Set rs = db.OpenRecordset("qrySendEmailAdvice")
While Not rs.EOF

'For each record (CaseID) send an email
DoCmd.SendObject acSendNoObject, , , rs![EmailAddress], , , "Case
Closing Reminder", _
"Dear " & rs![Employee] & vbCrLf & vbCrLf & "Your case
corresponding to " & rs![CaseID] & _
vbCrLf & vbCrLf & "is five days overdue. You need to work on
this
to close it"

'Update the Booking table so that emails are not duplicated
sSQL = "UPDATE tblEmployeeCase SET EmailSent=-1 where CaseID=" &
rs![CaseID]
db.Execute sSQL

'Cycle on to the next record
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
'Control recordset now closed

'Reset environment
DoCmd.SetWarnings True