Please do the following ...
1. Copy all of the code found at the bottom of this posting into a public
Module. The module should *not* be named SendEmail1 . If you do not have
any public modules, create one and save it using a name like basEmail.
2. Now, on your form, create a Command Button. In the properties sheet
for that command button, find the OnClick event and click anywhere on that
line and you will see a downward pointing arrow at the right edge of the
line. Click it and select Event Procedure. Then, look for the small button
to the right of the downward pointing arrow which contains an ellipsis
(three dots ...). Click this button to open the code window. In the code
window, type the following:
SendEMail1
The above line is the only thing you need to have in the code behind the
command button. It will execute the procedure that you have copied to a
public Module. We are not using a macro here. If you use the Wizard to
create your command button, it may put some code in the OnClick event for
you. Delete that code and replace it with the line above: SendEMail1
Do not use "SendEmail1()" or "(SendEMail1())" , as the code is not a
function but a procedure. (Functions return values - procedures do not.
Since the code to send an email does not return a value, I have made it a
procedure.)
3. Re - Naming Command Buttons: I notice that in your most recent post
that you named your command button SendEmail. By default, Access names
command buttons "Command1", "Command2", etc. While I think it is a good
idea to provide a descriptive name to the command button, it is not a good
idea to re-use object names in Access. If you want to rename the command
button for clarity, I'd suggest you rename it so that its object type is
identifiable: cmdSendEmail
COPY THIS CODE INTO YOUR PUBLIC MODULE:
Public Sub SendEMail1()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim MyBodyText As String
'Change this:
'Dim fso As FileSystemObject
'To this:
Dim fso As Object
'Change this:
'Dim MyBody As TextStream
'To this:
Dim MyBody As Object
'Add this:
Dim f1 As Object
'Replace this:
'Set fso = New FileSystemObject
'With this:
Set fso = CreateObject("Scripting.FileSystemObject")
' In the code you posted, you were using BodyFile$ = ...
' Since you dimmed a variable as BodyFile, why not use it?
BodyFile = InputBox$("Please enter the filename of the body of the
message.", _
"We Need A Body!")
' Now, you have to actually create the text file
Set f1 = fso.CreateTextFile(BodyFile, True)
If BodyFile = "" Then
MsgBox "No body, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain't Got No-Body!"
Exit Sub
End If
If fso.FileExists(BodyFile) = False Then
MsgBox "The body file isn't where you say it is. " & vbCRLF & _
vbCRLF & "Quitting...", vbCritical, "I Ain't Got No-Body!"
Exit Sub
End If
Subjectline = InputBox$("Please enter the subject line for this mailing.", _
"We Need A Subject Line!")
If Subjectline = "" Then
MsgBox "No subject line, no message." & vbCRLF & vbCRLF & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Sub
End If
' You need to write something to the text file - right?
' Write a line with a newline character.
f1.WriteLine ("Testing 1, 2, 3.")
' Write three newline characters to the file.
f1.WriteBlankLines (3)
' Write a line.
f1.Write ("This is a test.")
f1.Close
Set MyBody = fs
penTextFile(BodyFile, , False)
MyBodyText = MyBody.ReadAll
MyBody.Close
' Now, send the email
Set MyOutlook = New Outlook.Application
Set MyMail = MyOutlook.CreateItem(olMailItem)
Set db = CurrentDb
Set MailList = db.OpenRecordset("MyEmailAddresses")
Do Until MailList.EOF
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = MailList("email")
MyMail.Subject = Subjectline
MyMail.Body = MyBodyText
MyMail.Send
MailList.MoveNext
Loop
Set MyMail = Nothing
MyOutlook.Quit
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Sub
END OF CODE TO BE COPIED
--
Cheryl Fischer
Law/Sys Associates
Houston, TX
nata said:
hi,i'm not sure whether this is what u mean but i juz place a runmacro
button in the form n the onclick properties is [Event Procedure]. In
the code, i put something (SendEmail()) as follow, n the error is
Syntax Error
Private Sub sendemail_Click() 'yellow highlighted in this line
SendEmail()
On Error GoTo Err_sendemail_Click
Dim stDocName As String
stDocName = "project"
DoCmd.RunMacro stDocName
Exit_sendemail_Click:
Exit Sub
Err_sendemail_Click:
MsgBox Err.Description
Resume Exit_sendemail_Click
End Sub
'code end
Thanks for the help and sorry for the trouble
