X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Send Emails With Specific Information For Each Receiver    
post Dec 9 2015, 06:33 PM

Posts: 51
Joined: 3-December 10
From: San Diego, CA

UtterAccess Universe -

****PLEASE NOTE - I have been able to figure out this issue but cannot figure out how to delete this post. No need to read further or reply. I have a new challenge on the way!*****

It has been a struggle with this issue; thought that maybe a new viewpoint would be helpful.

The database will create a table which lists all the employees with a past due evaluation along with the supervisor name.

Here is my coding objective:
1. Loop through the contents of the table
2. Identify a supervisor name along with the employees that report to that specific supervisor
3. Those records for this supervisor drop into a report, which becomes the email body
4. An email is sent to this supervisor with ONLY their employees listed
5. Program loops through the records for the next supervisor and their employees
6. Continues to loop until it reaches the end of the list

In the past I have had success with sending one record to one email; which works great when sending information to a specific employee, sort of like a standard Word mail merge. It is not working very well on grouping several records together based on the supervisor name.

Maybe I am taking the wrong approach? Maybe it's not possible? (Which I find hard to believe, if I can dream it, Access can do it, right?)

Attached is a zipped copy of the data to give you an idea of what I am doing. Here is my code if that is helpful.


Option Compare Database

'Objective of this function is to send a listing to each supervisor of just their employees who appear on the list.
'The code should loop through the list, sending just those employees that have the supervisor's email.
'Once the code has sent all the records in the file, the code should stop and a message box appearing, stating the process is completjed.

Public Function send_PerfEvalPastDue()

Dim sSql As String

Set OutApp = CreateObject("Outlook.Application")

Dim rs As DAO.Recordset

'creating my record set - only pulling the records that have a common supervisor

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblPastDue WHERE SupvName;")

With rs

'If there are no records, then a message should appear

    If .EOF And .BOF Then
        MsgBox "No records on your table"
'asking the program to loop through all of the records
        Do Until .EOF

'Outputs the data for the specific supervisor called out in the recordset

'Clears out the temporary table
    sSql = "DELETE FROM tblPastDueTemp;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL sSql
    DoCmd.SetWarnings True
'Pulls the data from the table and places it into the temporary table

    sSql = "INSERT INTO tblPastDueTemp ( CC, DeptName, SupvName, [EE ID], [EE Name], [Lawson Due Date], [Grace Period Due Date], Comment, SupvEmail ) " _
           & "SELECT tblPastDue.CC, tblPastDue.DeptName, tblPastDue.SupvName, tblPastDue.[EE ID], tblPastDue.[EE Name], tblPastDue.[Lawson Due Date], tblPastDue.[Grace Period Due Date], tblPastDue.Comment, tblPastDue.SupvEmail " _
           & "FROM tblPastDue " _
           & "WHERE tblPastDue.SupvName " & rs!SupvName

    DoCmd.SetWarnings False
    DoCmd.RunSQL sSql
    DoCmd.SetWarnings True
'Outputs the report that will be used for the email message/body
    DoCmd.OutputTo acOutputReport, "tblPastDuePerfEvalsTemp", "HTML(*.html)", "C:\temp\EmailPastDueEvals" & ".html"

'Creates a text stream
    Set oFilesys = CreateObject("Scripting.FileSystemObject")
    Set oTxtStream = oFilesys.OpenTextFile("C:\temp\EmailPastDueEvals" & ".html", 1)

    txtHTML = oTxtStream.ReadAll
'Going to open Outlook, create then send the email.

   Dim olApp As Object
   Dim objMail As Object

'Checking to see if Outlook is open and need to create an instance to actually send e-mail

   On Error Resume Next 'Keep going if there is an error
   Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

    If Err Then 'Outlook is not open
       Set olApp = CreateObject("Outlook.Application") 'Create a new instance
    End If
'Creating e-mail item
   Set objMail = olApp.CreateItem(olMailItem)

    Receiver = rs!email
   With objMail
'Set body format to HTML
     .BodyFormat = olFormatHTML
     .To = Receiver
     .Cc = ""
     .Subject = "TEST - You can delete!  Past Due Perf Eval Message"
     .HTMLBody = txtHTML
        End With

    End If


'Cleaning up the system when everything has been sent.

    Kill "c:\temp\EmailPastDueEvals" & ".html"

    Set olApp = Nothing
    Set objMail = Nothing

    Set oTxtStream = Nothing
    Set oFilesys = Nothing


'Users will know the email was sent.
  MsgBox "Operation completed successfully"

End With

End Function
Go to the top of the page
post Dec 9 2015, 07:21 PM

Posts: 51
Joined: 3-December 10
From: San Diego, CA

Hello UtterAccess Universe - I have figured this out but don't know how to delete/remove this post. My apologies if you have read through this entire thing.
Go to the top of the page
post Dec 9 2015, 07:33 PM

Posts: 2,428
Joined: 12-February 15
From: SW AZ

Have you considered using the SendObject method (see also the FMS topic)

You can send an attached access report in PDF format, as well as a cover message. You can use a saved query as the report's record source, using a technique to set the query's filter property (to identify the supervisor's personnel) found in Post #2 of Using Multi-select List Boxes In Queries
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 09:58 AM