Seached forum for automation of emails, but still stuck-cos im a newb

[hillsee2]

Hiya,

Sorry to add to the email threads, already posted on this forum. I have already searched on the forum for the relevant help but im still struggling. As am a newb when it comes to this sort of thing, and everythink is an on going learning curve.

Background, i work for a architectural firm, my boss would like the database to send out automated emails, to the local planning authority.

I have a query called "ApplicationEmailCouncilQuery" in this i have the following fields. ApplicationProjectID, ApplicationSubmitted, ApplicationRef, ApplicationDecisiondate, ApplicationContact, PlannersEmail, ApplicationEmployee, Email.

How do i get access 2k to automatically send out emails to each of the results individually as produced fromt he query, every 2 weeks.

I want the email to use the fields from the query.
To: "PlannersEmail"
Send: Your Application ref is "ApplicationRef" submitted on "ApplicationSubmitted"
Message: To ApplicationContact,
Please could you give me an update on the application "ApplicationRef" which we submitted on "ApplicationSubmitted".

Please reply to this following address "Email"

Yours Truly

"ApplicationEmployee"

-----------------------------------------------

Sorry for the long post, any help on the above matter would be greatly appreciated.

Thanx

Keith

 

[Sergeant]

SMTP mail would be a good way to do this. I figured it out, using this forum for reference, when I was about 6 months into learning Access/VBA. I tested it on my ISP's SMTP server, and it shot out 100+ emails from a loop in about 6 seconds.
Here's a couple of posts that might help:
http://www.access-programmers.co.uk/forums/showthread.php?t=62394
http://www.access-programmers.co.uk/forums/showthread.php?t=97854

I would suggest that you read all you can about sending mail using SMTP, then try to make a sub that sends one hard-coded email (to yourself). That way, you can fidget with the SMTP stuff without worrying about loops and building your email body and such. Once you get that perfected, you can add to it until you get where you need to be, which is loosely outlined below:

1 Open query as a recordset in code
2 Build SMTP email using current record
3 Send SMTP email
4 MoveNext in the recordset and Loop to '2'

Of course, you then need to make this happen automatically on a set schedule...perhaps you could put all this functionality in a separate db and use windows scheduler to open it regularly?

 

[hillsee2]

Thanx,

Thanx for your help,

I will try and look in to this, ive read the 2 posts u sent me and i will try look for other stuff on SMTP. But as i said im a newb when it comes to coding and VBA. So most of it is well above my head. hehehe. Guess i will struggle on.

Im sure that smtp will do it, but without knowing the fundamentals of coding im gonna struggle.

Thanx for all your help.

Cheers

 

[Sergeant]

Sorry, you are starting from scratch...
This ain't easy, but it ain't rocket science, either.
This will get you started...
Make a VBA Procedure: (Make an unbound form with a button...in the CLick event of the button, write some code between the lines "Private Sub Button1_Click()" and "End Sub")
Like this:

Private Sub Button1_Click() 
  MsgBox "Hello World" 
End Sub

Now run the form and click the button...you should get a message box that says "Hello World".

Now you can start expanding, like this basic Loop:

Private Sub Button1_Click() 
For i = 1 to 5 
  MsgBox "This is loop number " & i 
Next i 
End Sub

It really is addictive once you get rolling.

Tip: Do not use "macros", write the code yourself.