HI,
I am looking for information on how to automatically generate a letter in access to be sent to someone. I work in a school and would like to be able to automate our attendance letters. Any information would be helpful.
HI,
I am looking for information on how to automatically generate a letter in access to be sent to someone. I work in a school and would like to be able to automate our attendance letters. Any information would be helpful.
Triggered by what? Simplest is probably SendObject in VBA. You can send a report or just text in the body of an email.
Thanks....it would be triggered when a student is absent a set number of days (i.e. after 5 unexcused absences, etc.)
I assume you have some way of identifying that, so I'd create a query that returned any students who meet the criteria. Then I'd create a VBA function that opened a recordset on that query and looped through it, sending an email to each student. I'd put that all in a stand-alone db that ran this process when it opened and the quit, and run that db from Windows Scheduled Tasks.
If it's snail mail you want to send I would suggest Albert Kalla's Super Easy Word Merge.
It should come up in a google search.
Ah, I assumed email. Might have gotten the usual result of assuming.
If that assumption was wrong, you could base a report on the query I mentioned and print it. I haven't used Albert's tool, but he is top notch so I assume it is too. By the way, it's Kallal.
Hi guys, sorry to jump in so late. I think it gets a bit more complicated than that. Usually kids noways don't quit their habits after the first warning (talking from experience here...). So I would say you will need to create a table holding the StudentID (FK), email/letter sent date, infraction type (absence/late/....) then use that in the query to reset the counter after each letter. And I also did find that the easiest is to use an Access report that looks like a letter with a rich text field (populated from a static table of "letter templates") and use a "pseudo" "mail-merge" approach where you use keywords delimited by pipe| or any other uncommon charater and a custom VBA function that replaces that placeholder with its corresponding value for each record.
This is an example of the function I use:
And to call it have a look at the attached pic.Code:Public Function vcProcessText(ByVal OAString As String, strTable As String) As String Dim a() As String Dim v As Variant, vFieldValue As Variant Dim sFieldName As String On Error Resume Next a() = Split(OAString, "|") vcProcessText = "" For Each v In a vFieldValue = "" sFieldName = Mid$(v, InStr(v, "|") + 1) If sFieldName = "" Then vFieldValue = "" Else vFieldValue = DLookup(sFieldName, strTable) End If vcProcessText = vcProcessText & IIf(vFieldValue = "", sFieldName, vFieldValue) Next v End Function
Cheers,
Vlad
Oops, I know that. Didn't notice the missing L.
To get a trigger each time there is a 5th absence you could get a total count of absences and use the Mod()
each time it equals 0 it would be the fifth instance.Code:CountOfAbsences Mod 5