VBA Code In Access To Send Email 1

[Caryisms]

Please see below. How do augment the code? I need to send emails without reports. Thank you.


Private Sub cmdSend_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim txtTO, txtCC, txtBCC, txtSubject, txtMessage As String
txtTO = ""
txtCC = ""
txtBCC = ""
txtSubject = ""
txtMessage = "" _
& vbCrLf & vbCrLf & "" _
& vbCrLf & vbCrLf & "" _
& vbCrLf & vbCrLf & ""
Set db = CurrentDb
Set rs = db.OpenRecordset("Test", dbOpenDynaset)
rs.MoveFirst
Do
gblvID = rs("ID&quot
txtTO = rs("EmailAddress&quot
DoCmd.SendObject acSendReport, "", acFormatRTF, txtTO, txtCC, txtBCC, txtSubject, txtMessage, False
rs.MoveNext
Loop Until rs.EOF
rs.Close
db.Close

End Sub

 

[SgtJarrow]

What are you asking for? The code should work fine. The only suggestion I have right now is that this line:

Dim txtTO, txtCC, txtBCC, txtSubject, txtMessage As String

should be changed to

Dim txtTO As String
Dim txtCC As String
Dim txtBCC As String
Dim txtSubject As String
Dim txtMessage As String

as you were defining everything except message as Variant...

Fill in the appropriate lines and run the code and you should get mails to everyone in the recordset supplied.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Caryisms]

Robert,

This code used to work when I had a report to send along with the email body. Now, all I need is to send the email body, and it is not working.

Thank you.

 

[GummowN]

It still thinks you are sending an object, but finds no object to attach!!!!

If you want to send an email the best thing to do is reference the the outlook reference library and build it from there.

Run a search in this forum for "email from access" there are 100s of threads with the same topic.

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[Molby]

Here's some code that I use on occassion:

Sub Outlook()
Dim myApp As New Outlook.Application
Dim myItem As Outlook.MailItem
Set myItem = myApp.CreateItem(olMailItem)
With myItem
.Importance = olImportanceHigh
.To = "johnsmith@hotmail.com"
.Subject = "Sample item"
.ReadReceiptRequested = False
.Body = "Your text here"
End With
myItem.Send

 

[SgtJarrow]

The Outlook automation is the best bet....but try this:

Change

DoCmd.SendObject acSendReport, "", acFormatRTF, txtTO, txtCC, txtBCC, txtSubject, txtMessage, False

to

DoCmd.SendObject acSendNoObject, , , txtTO, txtCC, txtBCC, txtSubject, txtMessage, False

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[nat1967]

Hi Caryisms,

your code is close. take a look at the below modified code. this should work for you.

'**************begin*********
Private Sub Command0_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim txtTO, txtCC, txtBCC, txtSubject, txtMessage As String
txtTO = ""
txtCC = ""
txtBCC = ""
txtSubject = ""
txtMessage = "" _
& vbCrLf & vbCrLf & "" _
& vbCrLf & vbCrLf & "" _
& vbCrLf & vbCrLf & ""
Set db = CurrentDb
Set rs = db.OpenRecordset("Test", dbOpenDynaset)
rs.MoveFirst
Do
gblvID = rs("ID&quot
txtTO = rs("EmailAddress&quot

DoCmd.SendObject , , , txtTO, txtCC, txtBCC, txtSubject, txtMessage, False
rs.MoveNext
Loop Until rs.EOF
rs.Close
db.Close
End Sub

'**********end ********************

let me know if this helps.

Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com

 

[nat1967]

OOPSS!!! i forgot one thing. i agree with Robert. I would break out the variables
'*****begin****
Private Sub Command0_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim txtTO as String
Dim txtCC as String
Dim txtBCC as String
Dim txtSubject as String
Dim txtMessage As String

txtTO = ""
txtCC = ""
txtBCC = ""
txtSubject = ""
txtMessage = "" _
& vbCrLf & vbCrLf & "" _
& vbCrLf & vbCrLf & "" _
& vbCrLf & vbCrLf & ""
Set db = CurrentDb
Set rs = db.OpenRecordset("Test", dbOpenDynaset)
rs.MoveFirst
Do
gblvID = rs("ID&quot
txtTO = rs("EmailAddress&quot

DoCmd.SendObject , , , txtTO, txtCC, txtBCC, txtSubject, txtMessage, False
rs.MoveNext
Loop Until rs.EOF
rs.Close
db.Close
End Sub

'****end****

Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com