E-mails

B

BoroLee

Registered User.
Local time
Today, 11:58
Joined
Aug 30, 2000
Messages
90
I currently have a table which includes the fields e-mial, name, address, course, amount owed, reason.

I would like to send all these people e-mails, rather than have the Finance Department raise invoices.

Anyone got the code to help, rather than me re-invent the wheel???

Thanks,
Lee.
 
Create a command button on your form and on click of the cmdbutton put the following code

Private Sub CmdEmail_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim strBody As String
Dim strSubject As String
Dim strContactName As String
Set rsEmail = CurrentDb.OpenRecordset("QrySendEmails")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
strBody = rsEmail.Fields("Body").Value
strContactName = rsEmail.Fields("ContactName").Value
strSubject = "What Ever"


DoCmd.SendObject , , , strEmail, , , _
strSubject, _
"Hello " & strContactName & vbCrLf & vbCrLf & strBody, True

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

MsgBox "Emails have been sent"
End Sub

This will loop through all the contacts in your table/Query sending them an email in turn . The code is currently set to True so that the email opens in outlook first, that was mainly for testing purposes. Set this to false and it will just send all the emails without opening in outlook.
 
Thanks for that.

My only problem is that i am running a query to select specific items first
 
Can't you use the form to select the items you need then run the query based on the selection(s)
 
Thanks again.

have now got a from to select the results.

My problem now is the e-mails don't send?????
 
Also, if they do send, they only send to the first person. it is not looping through all records.
 
Are you getting any error messages when you try to send the emails or is it just a case of nothing happening upon trying to send them? Feel free to post up your db if you like and I'll have a look.
 
Nothing happens.

Just get the message saying e-mails have been sent, when they haven't.

it did work the first time, but now nothing.

Can't post db as it has linked tables.
 
Ok there shouldn't be any errors with the code because I copied it straight from a working db I have. The only thing I can think of is that you may have forgotten to change some of the field /query names to match your own.
 
Hayley,
Here is the code i'm using.

It worked the first time i tried, but hasn't since.

Lee.

Private Sub Email_these_students_Click()

Dim strEmail As String
Dim strBody As String
Dim strBody1 As String
Dim strBody2 As String
Dim strBody3 As String
Dim strBody4 As String
Dim strSubject As String





Set rsEmail = CurrentDb.OpenRecordset("Q_Students on DB > 21 days and not <19yrs")

Do While Not rsEmail.EOF

strEmail = rsEmail.Fields("E-Mail").Value
strBody = "According to our records, you are enrolled on the " & Me.CourseTitle & " course at the college."
strBody1 = "When you enrolled, you informed us that you where in receipt of a Mean Tested Benefit. You have not however, shown the college proof that you are in fact in receipt of this benefit."
strBody2 = "Can you therefore please take proof of your benefit with you to the Information & Guidance desk as soon as possible, along with this e-mail."
strBody3 = "Failure to provide this proof, will result in you being invoiced with the next 14 days for the course fee, which is £" & Me.CourseFee
strBody4 = "Should you have any queries regarding this e-mail, please contact the Finance Office on (01642) 865428."
strSubject = "Proof of benefit required"


DoCmd.SendObject , , , strEmail, , , strSubject, "Dear " & Me.Forename_s_ & vbCrLf & vbCrLf & strBody
'& vbCrLf & vbCrLf & strBody1
'& vbCrLf & vbCrLf & strBody2 & vbCrLf & vbCrLf & strBody3 & vbCrLf & vbCrLf & strBody4

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

MsgBox "Emails have been sent"
End Sub
 
BoroLee said:
DoCmd.SendObject , , , strEmail, , , strSubject, "Dear " & Me.Forename_s_ & vbCrLf & vbCrLf & strBody
'& vbCrLf & vbCrLf & strBody1
'& vbCrLf & vbCrLf & strBody2 & vbCrLf & vbCrLf & strBody3 & vbCrLf & vbCrLf & strBody4
Click to expand...

Just to butt in for a moment - not to offer a solution just to say something.

That's a lot of vbCrLf constants in there.

In your declarations for this routine make a private constant:

i.e.

Code: 
Const NL = vbCrLf & vbCrLf

Then, later in the code:

Code: 
DoCmd.SendObject , , , strEmail, , , strSubject, "Dear " & Me.Forename_s_ & NL & strBody
'& NL & strBody1
'& NL & strBody2 & NL & strBody3 & NL & strBody4

Hold on....

Me.Forename_s_ !!!!! Underscore.....
 
Thanks for that.

The Forename_s_ is becase that field contains ().

does this cause an error?????
 
Yes.

It's best practice not to include spaces and/or special characters in field and object names..

So, no !@<>?*()-#$£%& etc. in a field or object name.

Q_Students on DB > 21 days and not <19yrs, likewise, is a horrible name for a query.

The most widely used prefix for queries is qry and to not have spaces, and for each word in the query's name to begin with a capital letter; the remainder in lowercase.

i.e.

qryStudentsInRange
 
Mile-O-Phile said:
Q_Students on DB > 21 days and not <19yrs, likewise, is a horrible name for a query.
Click to expand...

Don't forget, after all, that you don't need to put all the information about what the query is for into the query's name - if you right click on the query object then you can enter a description about its purpose into the description property.
 
Mile-O-Phile said:


Just to butt in for a moment
Click to expand...

Just doing what you do best you mean;)

I'm working on your code. Can I change the query name and maybe give you a little sample...that's providing I can get it going of course:p
 
Well, I'll "butt" in again and say that there is no recordset object dimensioned in the code above and - unless you don't have Option Explicit on - could be the root of your problem.
 
Code: 
Private Sub Email_these_students_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strEmail As String
    Dim strMessage As String
    
    Const NL = vbCrLf & vbCrLf
    Const Subject = "Proof of benefit required"
    Const Part1 = "According to our records, you are enrolled on the "
    Const Part2 = " course at the college."
    Const Part3 = "When you enrolled, you informed us that you where in receipt of a Mean Tested Benefit. You have not however, shown the college proof that you are in fact in receipt of this benefit."
    Const Part4 = "Can you therefore please take proof of your benefit with you to the Information & Guidance desk as soon as possible, along with this e-mail."
    Const Part5 = "Failure to provide this proof, will result in you being invoiced with the next 14 days for the course fee, which is £"
    Const Part6 = "Should you have any queries regarding this e-mail, please contact the Finance Office on (01642) 865428."
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Q_Students on DB > 21 days and not <19yrs")
    
    Do While Not rs.EOF
        strEmail = rs.Fields("E-Mail")
        strMessage = "Dear " & Me.[Forename_s_] & NL & Part1 & Me.CourseTitle & Part2 & _
            NL & Part3 & NL & Part4 & NL & Part5 & Me.CourseFee & NL & Part6
    
        DoCmd.SendObject , , acFormatTXT, strEmail, , , Subject, strMessage, False
    
        rsEmail.MoveNext
    
    Loop
    
    MsgBox "Emails have been sent"
    
    Set rs = Nothing
    Set db = Nothing
    
End Sub


The only other problem I can foresee is you reference the form to produce the emails. The records in this form are never changed and so, as you navigate through the recordset, you will send out the exact same details about one person to every person returned within your query.
 
BoroLee said:
Should you have any queries regarding this e-mail, please contact the Finance Office on (01642) 865428
Click to expand...

You might want to consider excluding things like that next time as that is, in a way, personal information. Stockton & Billingham College of Further Education Finance Department? ;)
 
You must log in or register to reply here.

Similar threads

D
Picture sent in body (not attached) by automated E-Mail
2
Replies
20
Views
1,701
The_Doc_Man
The_Doc_Man
L
E-Mail from access to contacts selec
Replies
1
Views
590
Gasman
Gasman
P
Custom event whine
2
Replies
36
Views
1,597
jwcolby54
jwcolby54
P
Long-running delete of duplicates
2
Replies
25
Views
1,154
CJ_London
C
kstaelens
Search (if not found: add) in a table on a subform
Replies
6
Views
937
jamesave
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom