Automaticaly send emails. 1

[lars7]

Hi,
Advise needed.

I would like to automatically send emails based on a query from my database but each with different criteria. I was thinking that a temp table with a link to my main table and a check box field that could be ticked when an email is sent to the first player then second and so on with a timer. Is this a good approach or is there an easier way to do this.

 

[Crowley16]

why have a temp table?

just fill a recordset with the relevant details/criterias of each e-mail and then loop through the recordset, sending each e-mail...

you might have a sleep between each one but might not be necessary depending on how you are sending your e-mails.

--------------------
Procrastinate Now!

 

[lars7]

Hi Crowley16,

Thanks for replying.

I have never used "loop through" before could you help me get started. This is the SQL of the query that I would like to send to each player (in a Report [format Snapshot]) with playerId as the criteria.


qryAutoEmail:

SELECT tblPlayer.PPlayerId, tblPlayer.PPlayer, tblfixtures.FTeam1, [GScore1] & " v " & [GScore2] AS Fixture, tblfixtures.FTeam2, qryGameScore.QPoints
FROM (tblfixtures INNER JOIN qryGameScore ON tblfixtures.GameNo=qryGameScore.GGameNo) INNER JOIN tblPlayer ON qryGameScore.GPlayerId=tblPlayer.PPlayerId;

This is the player’s information only.

What I am trying to achieve here is:


The players have predicted the results of the football matches, so each day I would like to send them a report with there predictions and the correct scores and a list that shows which players are in the lead in the competition. The report will be called rptAutoEmail which will have 3 subreports to gather the information. I hope this is enough information for you if not let me know.

 

[Crowley16]

lol... you've never used a loop? of any kind?

ok, firstly, you'll need to create a report from which the desired snapshot can be generated.

once you have your report just the say you like, you can write the function to do the auto emails.

open all the addresses of people you want to send this report to in a recordset, and include some sort of id/filter in this recordset, e.g. player ID

once you've got the recordset, loop through it, Personally I use a while loop, F1 for the syntax, but there are other types of looping...

for each of the records found in your recordset, you need to first generate the snapshot file, look at the OutputTo method, and the where filter on the docmd.openreport method.

then you can send the e-mail, with the generated snapshot file attached.

and move onto the next record...

I suggest you write a global function for saving a report to a snapshot, and another for sending an e-mail with an attachment, then in the loop you'd only have to write 2 function calls...

--------------------
Procrastinate Now!

 

[Crowley16]

p.s. each step can be easily found on google, these forums, with F1...

--------------------
Procrastinate Now!

 

[lars7]

Hi Crawley16,

No I have never used a "loop of any kind" nor have I ever wrote a Fuction. Being fairly new to Code I think this, without some help, would be beyond me at this time. I had also looked through the forums keywords and FAQ's but could not find anything about "Automated Emails" in Access.
Is this the only way to do this or is there any alternative for the less gifted amounst us.

 

[Crowley16]

as far as I'm aware, there's not automatic tieins for access to do auto emails...

I suggest you have a look at some examples and just try different things, it shouldn't be that difficult to figure out how to do all the things I've said...

--------------------
Procrastinate Now!

 

[lars7]

Hi again,

Thanks for the advise, I will see if i can find some examples of this on the net.

Tom.

 

[lars7]

Hi,
I found what I was looking for just need to alter it to suit my database.

http://rogersaccesslibrary.com/download3.asp?SampleName=EmailingSpecificReports.mdb

thanks again Crowley16 for the advise.

 

[Aliffi]

hey lars7
how are you, i have the same probelm as you had , i am glad for you , you solved your problem but can you discuss it bit with me i used that ziped database which guides to automatic email ....

i altered the code as below but it gives me a runtime error
and says (object variable or with block variable not set)

her is the code
remember i am using SQL SERVER back and ADP front
'----------------------------------------------------
Sub SeparateEmails()
'*** error trapping - execution goes to bottom on error
On Error GoTo Err_SeparateEmails


Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rsGLTable As ADODB.Recordset
Dim rsCriteria As ADODB.Recordset

Set db = CurrentDb
Set rsCriteria = db.OpenRecordset("task_manager", dbOpenSnapshot)

'*** the first record in the Criteria table ***
rsCriteria.MoveFirst

'*** loop to move through the records in Criteria table
Do Until rsCriteria.EOF
'*** create the Select query based on
' the first record in the Criteria table
strSQL = "SELECT * FROM tasksmail WHERE "
strSQL = strSQL & "[taskm] = '" & rsCriteria![Nick_Name] & "'"

'MsgBox strSQL
'*** delete the previous query
db.QueryDefs.Delete "filtermail"
Set qdf = db.CreateQueryDef("filtermail", strSQL)

DoCmd.SendObject acReport, "rptmail", "RichTextFormat(*.rtf)", rsCriteria!, "", "", "This is a test", "I am testing a new idea for reports", False, ""
'rsCriteria!Emailed = True
'*** goto the next record in Criteria table
rsCriteria.MoveNext

Loop

rsCriteria.Close

Exit_SeparateEmails:
Exit Sub

Err_SeparateEmails: '*** if there is an error, execution goes here
'*** if the error is the table or query missing (3265)
' then skip the delete line and resume on the next line
' Error 2501 notifies you that the SendObject action
' has been cancelled. See the OnNoData Event of the report.
If Err.Number = 3265 Or Err.Number = 2501 Then
Resume Next
Else
'*** write out the error and exit the sub
MsgBox Err.Description
Resume Exit_SeparateEmails
End If

End Sub

'--------------------------------
any idea everyone

 

[lespaul]

First, you should start a new thread, not tack on to the end of an old one.

Secondly, when this error appears:

but it gives me a runtime error
and says (object variable or with block variable not set)

what line of code is highlighted?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi