VBA program to to automatically loop through every distinct email section in a query to send out separate emails

Hello Everyone,

I have an access database that contains a query to identify sales for each Manager by their assigned employee.  Is there a way to loop through the query and send a report to each manager with only their employees on the report?
cyardleyAsked:
Who is Participating?
 
als315Connect With a Mentor Commented:
I'm confused a little with your logic. In question was: "send a report to each manager with only their employees". But in code I see Emp_table, where employee email is stored. May be we should open tblManagers? In this case code will be:
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim strReportName As String
strReportName = "rptManagerEmpSales"
Set rst = CurrentDb.OpenRecordset("tblManagers")
rst.MoveFirst
Do Until rst.EOF
    If rst!managerEmail & "" <> "" Then
     DoCmd.OpenReport strReportName, acViewPreview, , "[ManagerID] = " & rst!ManagerID
     DoCmd.OutputTo acOutputReport, , acFormatPDF, "c:\temp\report.pdf"
     DoCmd.Close acReport, strReportName
     SendMessage rst!managerEmail, "Report for " & rst!ManagerName, "Message text", "c:\temp\report.pdf"
     'DoCmd.SendObject acSendReport, strReportName, acFormatPDF, rst!ManagerEmail
    End If
    rst.MoveNext
Loop

MsgBox "Done"

rst.Close
Set rst = Nothing
End Sub

Open in new window

0
 
als315Commented:
You didn't mentioned Access version and format of report.
You can start here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22015397.html#a17677055
if access version is 2003 and before. You don't need PDF995 if you have 2007 or later versions.
0
 
cyardleyAuthor Commented:
I apologize.  I have Access 2007.  I copied the code from the link above but when I compile it, I receive Sub or Function not defined error for the convertReporttoPDF function.  Is there a library that I need to reference for that function?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
als315Commented:
This function was in one of comments above, but you don't need it. Access 2007 can save pdf without additional programs. Code will be like this:
DoCmd.OpenReport "ReportName", acViewPreview, , MyFilter
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, True
0
 
cyardleyAuthor Commented:
I apologize again as I am not an expert but where do I place that code above and do I replace "ReportName" with the name of my access report.

Thank you very much for your help!
0
 
als315Commented:
May be you can upload your DB with some sample data and related objects? In this case you will have real code.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Give this a try...
Examine the Database fully
Note the code to send the email.
Access-EEQ27833034-EmailManagerI.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
<convertReporttoPDF function>
I have no idea of what this, is or how it is being used in your app...
If you are in-fact using Access 2007 (as you state)...
   <I have Access 2007.>
...Then I don't see a need for a PDF conversion function.
If you load the PDF Addin utility, you can create PDFs from Access 2007 without any external utilities

In other words, your "convertReporttoPDF function" is not needed with my sample

JeffCoachman
0
 
cyardleyAuthor Commented:
Thank you for your reply everyone!  I very much appreciate the help.

Unfortunately, I had to travel to another client last minute so I will not be able to get back to this until late this week, early next.  

Is it ok to leave this question open until then?  I really do appreciate this site and all of this assistance that I've received from it, so I want to ensure that I'm doing things properly.
0
 
Jeffrey CoachmanMIS LiasonCommented:
<Is it ok to leave this question open until then? >
Fine by me...
;-)
0
 
cyardleyAuthor Commented:
Access-EEQ27833034-EmailManagerI.mdbHi boag2000,

Thank you for your example.  I reworked to show you my problem.  Basically, my user created one table for employees and uses that table to report on.  The code that you provided works well, but with the way my table is setup I'm not sure what unique ID I could use to run the report.

One other thing, in your code, is there a way to automate the email so that it's automatically sent without a message opening and me having to click the Send button?

Attached is your database, I created the Emp_table that represents my problem.

Thank You!
0
 
cyardleyAuthor Commented:
attaching the file again
Access-EEQ27833034-EmailManagerI.mdb
0
 
als315Commented:
Your selection for ID seems correct. If you don't like to ask Outlook's questions, you can use sendmessage from my link above. I've included sample, based on your DB (you should have reference to Microsoft Outlook library and temporary folder - c:\tmp)
Access-EEQ27833034-EmailManager.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
<One other thing, in your code, is there a way to automate the email so that it's automatically sent without a message opening and me having to click the Send button?>

...Sure, modify the "SendObject" line like so:
DoCmd.SendObject acSendReport, strReportName, acFormatPDF, rst!ManagerEmail, , , , , False

Now, if you also get an Outlook security message, you can buy a product like this to bypass it:
http://www.contextmagic.com/express-clickyes/

JeffCoachman
0
 
cyardleyAuthor Commented:
I apologize that I'm still struggling with this.  I tried to use your code with my database and I still receive 'Null' value errors.  

I updated the database that you attached as an example and set it exactly how I see my tables.  I cannot edit the tables because they come from a spreadsheet import.

Thank you for any help that you can offer!
Access-EEQ27833034-EmailManager-.mdb
0
 
als315Commented:
You need check for missing email addresses. Change button's code to:
Private Sub Command0_Click()

Dim rst As DAO.Recordset
Dim strReportName As String
strReportName = "rptManagerEmpSales"
Set rst = CurrentDb.OpenRecordset("Emp_table")
rst.MoveFirst
Do Until rst.EOF
    If rst!Emp_email & "" <> "" And rst!Emp_Mgr_Login & "" <> "" Then
     DoCmd.OpenReport strReportName, acViewPreview, , rst!Emp_Mgr_Login = "Emp_Mgr_Login"
     DoCmd.OutputTo acOutputReport, , acFormatPDF, "c:\temp\report.pdf"
     SendMessage rst!Emp_email, "Report for " & rst!Emp_Mgr_Name, "Message text", "c:\temp\report.pdf"
     'DoCmd.SendObject acSendReport, strReportName, acFormatPDF, rst!ManagerEmail
     DoCmd.Close acReport, strReportName
     rst.MoveNext
    End If
Loop

MsgBox "Done"

rst.Close
Set rst = Nothing
End Sub

Open in new window

0
 
cyardleyAuthor Commented:
als315,

Thank you for the quick response.  When I updated the code to yours above my system hangs.  Do you know what I may be missing?
0
 
als315Commented:
Sorry, no movenext. Change code to:
Private Sub Command0_Click()

Dim rst As DAO.Recordset
Dim strReportName As String
strReportName = "rptManagerEmpSales"
Set rst = CurrentDb.OpenRecordset("Emp_table")
rst.MoveFirst
Do Until rst.EOF
    If rst!Emp_email & "" <> "" And rst!Emp_Mgr_Login & "" <> "" Then
     DoCmd.OpenReport strReportName, acViewPreview, , rst!Emp_Mgr_Login = "Emp_Mgr_Login"
     DoCmd.OutputTo acOutputReport, , acFormatPDF, "c:\temp\report.pdf"
     SendMessage rst!Emp_email, "Report for " & rst!Emp_Mgr_Name, "Message text", "c:\temp\report.pdf"
     'DoCmd.SendObject acSendReport, strReportName, acFormatPDF, rst!ManagerEmail
     DoCmd.Close acReport, strReportName
    End If
    rst.MoveNext
Loop

MsgBox "Done"

rst.Close
Set rst = Nothing
End Sub

Open in new window

0
 
cyardleyAuthor Commented:
Ok.  I apologize because I'm still struggling with this.  

When I run the code above on my database it just sends empty reports.  I attached an example.

Basically, I need the report sorted by manager and emailed to the manager but the way the table is set up does not allow me to provide a unique id for the manager and unfortunately the table cannot be changed.

If you can take a look at the Emp_table in the attached example I think I've finally provided you with a good example of my database.  

I really appreciate all of the help and apologize for any frustration I may be causing.
example-091412.accdb
0
 
cyardleyAuthor Commented:
Sorry.  I didn't get a notice that you replied to this on Saturday.

The problem is that I can't use a table like tblManagers.  I can only use the emp_table because it comes from an ETL
0
 
als315Commented:
But your report in example is based on tblManagers. If it is not correct, you should rebuild report.
0
 
cyardleyAuthor Commented:
I realize that this has been open for a long time.  My client ended up using a different solution but als315 had me closest to resolution.  My apologies for the very long delay in closing
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.