?
Solved

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

Posted on 2012-08-17
22
Medium Priority
?
288 Views
Last Modified: 2014-07-21
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?
0
Comment
Question by:cyardley
  • 10
  • 8
  • 4
22 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38305908
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
 

Author Comment

by:cyardley
ID: 38306200
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
 
LVL 40

Expert Comment

by:als315
ID: 38306210
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:cyardley
ID: 38306329
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
 
LVL 40

Expert Comment

by:als315
ID: 38307285
May be you can upload your DB with some sample data and related objects? In this case you will have real code.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38312706
Give this a try...
Examine the Database fully
Note the code to send the email.
Access-EEQ27833034-EmailManagerI.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38312736
<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
 

Author Comment

by:cyardley
ID: 38312749
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38313350
<Is it ok to leave this question open until then? >
Fine by me...
;-)
0
 

Author Comment

by:cyardley
ID: 38355774
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
 

Author Comment

by:cyardley
ID: 38355778
attaching the file again
Access-EEQ27833034-EmailManagerI.mdb
0
 
LVL 40

Expert Comment

by:als315
ID: 38356956
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38357887
<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
 

Author Comment

by:cyardley
ID: 38390756
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
 
LVL 40

Expert Comment

by:als315
ID: 38390891
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
 

Author Comment

by:cyardley
ID: 38391461
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
 
LVL 40

Expert Comment

by:als315
ID: 38391889
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
 

Author Comment

by:cyardley
ID: 38400373
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
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 38402008
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
 

Author Comment

by:cyardley
ID: 38406349
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
 
LVL 40

Expert Comment

by:als315
ID: 38408293
But your report in example is based on tblManagers. If it is not correct, you should rebuild report.
0
 

Author Closing Comment

by:cyardley
ID: 40209428
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question