Results 1 to 9 of 9
  1. #1
    jrobertson is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    2

    Question Automaticall Generated Letters


    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.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Triggered by what? Simplest is probably SendObject in VBA. You can send a report or just text in the body of an email.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jrobertson is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    2
    Thanks....it would be triggered when a student is absent a set number of days (i.e. after 5 unexcused absences, etc.)

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    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.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    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:
    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
    And to call it have a look at the attached pic.

    Cheers,
    Vlad
    Attached Thumbnails Attached Thumbnails ReportPseudoMerge.jpg  

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by pbaldy View Post
    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.
    Oops, I know that. Didn't notice the missing L.

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    To get a trigger each time there is a 5th absence you could get a total count of absences and use the Mod()

    Code:
    CountOfAbsences Mod 5
    each time it equals 0 it would be the fifth instance.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 12-08-2016, 03:17 AM
  2. Table ID to be generated from names...
    By arrudac30 in forum Access
    Replies: 5
    Last Post: 08-09-2015, 11:02 AM
  3. Updating a Database Automaticall
    By ccordner in forum Import/Export Data
    Replies: 1
    Last Post: 01-20-2012, 01:53 PM
  4. Replies: 8
    Last Post: 10-13-2011, 04:34 AM
  5. Replies: 1
    Last Post: 04-26-2011, 04:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums