Sending Email to Supervisors

[jtm013]

Hello,

I have a pickle. I have all the pieces of what I want to accomplish. I just cannot determine how to put them together.
My database tracks what courses an individual has completed. An individual's supervisor needs periodic updates on their personnel's completed (or incomplete) training. I want to be able to push a button on my dashboard and have access run the requisite report for each person in the database, save those reports to a delineated folder, attach them to an email, and email those status reports to the applicable supervisor.

Currently, I can do most of that but not all together and not all at once. Right now my on click event runs the report, saves it to a delineated folder as a pdf, attaches that pdf to an email, and emails it. HOWEVER - I had to code the recipient email which defeats my goal and I do not know whether I can have multiple reports uploaded to the same email.

Supervisor name and emails are stored in a table and linked to the table which stores trainee information (ie. there is a field on each trainee record listing which supervisor is applicable to that trainee).

Any help is appreciated - thank you.

 

[theDBguy]

Hi. Welcome to AWF! Perhaps if you can post your code for creating the email, we can help you change it to avoid coding the email to a specific recipient.

 

[jtm013]

Sorry about that! Here it is.

Private Sub btnEmail_Click()

Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String, todayDate As String

'Export report in same folder as db with date stamp
todayDate = Format(Date, "MMDDYYYY")
fileName = Application.CurrentProject.Path & "\rptPlaceholder" & todayDate & ".pdf"
DoCmd.OutputTo acReport, "rptPlaceholder", acFormatPDF, fileName, False

'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
    .Recipients.Add "Email@Email.com"
    .Subject = "Training Incomplete"
    .Body = "Please see the attachement to view those with incomplete courses."
    .Attachments.Add fileName
    .Send
End With

MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"
End Sub

 

[theDBguy]

Sorry about that! Here it is.

Private Sub btnEmail_Click()

Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String, todayDate As String

'Export report in same folder as db with date stamp
todayDate = Format(Date, "MMDDYYYY")
fileName = Application.CurrentProject.Path & "\rptPlaceholder" & todayDate & ".pdf"
DoCmd.OutputTo acReport, "rptPlaceholder", acFormatPDF, fileName, False

'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
    .Recipients.Add "Email@Email.com"
    .Subject = "Training Incomplete"
    .Body = "Please see the attachement to view those with incomplete courses."
    .Attachments.Add fileName
    .Send
End With

MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"
End Sub

Thanks! So, assuming the issue is with this line:

.Recipients.Add "Email@Email.com"

Then, perhaps you can change that to something like this:

Dim strEmail As String

strEmail = DLookup("EmailAddress","tblSupervisors","SomeCriteriaToPinpointTheCorrectPerson")

.Recipients.Add strEmail

Hope that helps...

 

[Cronk]

@DBguy, jtm013
The OP want to have multiple attachments for multiple emails, but different attachments for each email. I'll let you address this.

An alternative approach is to generate a multi page report containing all students for each supervise and use docmd.sendObject.

 

[Pat Hartman]

If you want to send multiple attachments to multiple people, you need two loops.
The outer loop reads the supervisor table and formats the email,
The inner loop reads a query with critera that selects only the people for this supervisor. It then saves the pdf and attaches it to the email.
After all the attachements are added, the outer loop sends the email and then reads the next supervisor record.

I'm not sure if there is a limit to the number of attachments you can send. If there is and you have a problem, you can put a counter in the inner loop and send after 10 attachments. So you might end up sending 10,10,10, 2

 

[jtm013]

@Cronk, DBguy, Pat Hartman Thank you all for the assistance I appreciate it.

Cronk, as far as whether I have multiple individual reports or 1 multipage report I can work with either. I do not believe it would be unreasonable for a supervisor to have to sift a multi-page report.

However - Pat Hartman/DBguy - you have both struck on my primary issue. I am very new to access. How do I do the outer/inner loops?

I added the dlookup function DBguy suggested, but I admit to having no idea how to make the criteria work to facilitate the looping you are talking about Pat. Thank you for your the continued assistance!

 

[Cronk]

If you have n supervisors, and each supervisor had xn subordinates, you want to generate n emails, with each having xn attachments (1 for each of that supervisor's subordinates) or one report attached containing xn pages (assuming one page report per subordinate). If the number of subordinates for each supervisor is small (relatively) then there could be an attachment for each subordinate) but if large, I would want to have them in a multipage report attached.

The outer loop would move through the supervisor group one at a time. It would have an inner loop through the sub-group of subordinates for that supervisor, generating a separate attachment file for each subordinate. If a multipage report was being compiled, the inner loop would not be necessary.

Look up looping through a recordset. Then come back with more questions if you need. Without knowing the structure of your tables, I would not wish to suggest any code.

 

[jtm013]

@Cronk, thanks for your continued assistance. I'm afraid I'm hopelessly lost. I looked at the loop information you recommended. However, I'm not sure how to incorporate that into the code. Rather than try to explain my difficulties I've uploaded a simplified database which shows current state. Thank you for your continued assistance.

 

[Cronk]

I find it hard to get my head around your database. When you name the key field for tblStudents, stdClock and the Foreign Key to the related supervisor a different name from your key field for tblSupers, it makes it more difficult. Also, tblTrainingRecords should be related to tblCourseReqs on the reqID (key field) not the name of the reqTask.

What you are asking would for me involve several hours work and given I do this work for a living, I can't spare that much time now. Maybe someone else can help you.

Going forward, I suggest you get your table design right. Come back here with that for comments/suggestions. Then look to create appropriate forms for inputting/maintaining data. Then look to reports. To me, it's a bit like you are building a house from the roof down.

 

[jtm013]

@Cronk I inherited the database from a predecessor and am now tasked with trying to cure its ills and expand its capabilities. Despite not being an access aficionado myself. I both understand and appreciate your position completely. Thank you for the assistance you have provided.

I threw the example db together to show the relationships. The actual db I'm working with has many thousands of existing records and, as I am certain you can imagine, they are quite a mess. So I am loathe to start over. However, if I am interpreting your comment correctly in order to reach my desired result of emailed notifications it may be best to just begin again - is that correct?

Again, I greatly appreciate your assistance and do not expect anyone on here to build the system on my behalf.

 

[theDBguy]

@Cronk, thanks for your continued assistance. I'm afraid I'm hopelessly lost. I looked at the loop information you recommended. However, I'm not sure how to incorporate that into the code. Rather than try to explain my difficulties I've uploaded a simplified database which shows current state. Thank you for your continued assistance.

Hi. Hope this helps. I added a button to show you how you can do a loop with an inner loop. Cheers!