Hello folks.

I have several buttons on a form.
Each button runs some code that attaches a specific report to an email and seends to a list of recipients.
The recipients may be different for each report.
There are 7 reports (and so 7 buttons in total)

The user has now asked if the emailing can all be done from one button.

There are two problem I need to overcome.

At the moment, at the end of each module I have put a message box to inform the user the routine has run. This is not a major problem as I can remove the message box and use a checkbox as a visual indicator of the progress.

The main problem is that in each module, I have a piece of code that looks for the supplier id's where records meeting certain criteria will be reported out. If there are no supplier id's, then the routine exits.

If I am going to link all of the code together so that all reports are emailed out automatically, I need to either change the code so that rather than exiting the routine, it moves onto the next report category. Or, I need to be able to automatically click 'OK' to any message boxes that appear.

Are either of these possible?

I would move the code to stand alone proceedures and call them seperately for the individual buttons and in a series for the group. Hope that makes sense.

That's what I have at the moment.
Each button calls a piece of code that does the emailing for that particular set of reports.

Problem with this is there are a lot of reports and a lot of recipients and it requires user attendance to click on each button to complete the process.

We are hoping to load the database onto a separate PC so that the user can continue working on their own PC while all this is going on because it takes a while to complete. Ideally we just want to do a 'one-click send all'.

I would just get rid of the buttons and use a multiselect listbox that would let your user select any, more than one, or all of the reports they want and then a single button to kick off the code. Then you can have one message box at the end that says it has been completed.

The other benefit to this is that you can add reports and not have to add new buttons and code all of the time, if you do something like this one:
http://downloads.btabdevelopment.com/Samples/listbox/SampleSelectReportFromForm.zip

That's what I have at the moment.
Each button calls a piece of code that does the emailing for that particular set of reports.

Problem with this is there are a lot of reports and a lot of recipients and it requires user attendance to click on each button to complete the process.

We are hoping to load the database onto a separate PC so that the user can continue working on their own PC while all this is going on because it takes a while to complete. Ideally we just want to do a 'one-click send all'.

Thats why I suggested running a group of emails from one button that run one email procedure at a time, back to back to back. ?

Not that easy Bob - the reports are Excel spreadsheets so not held in the database.
I have an excel template made up for each report. The data is fed out to the templates and then saved with a unique report name based on a concatenation of supplier id, current date and a file description. Each set of reports is then saved in a separate folder.

Thats why I suggested running a group of emails from one button that run one email procedure at a time, back to back to back. ?

Thanks Ken, this is what I am trying to do.
But like I say, in each routine there is code that says 'if nosuppliers data meet this criteria, exit the routine'.
If I am goung to run them back to back to back, then I need to take this into account, but I don't want to exit the routine.
How do I do this?

Exit the secondary routine, not the main one - ?

And there lies the knub.

Each button on the form calls the code below (the only differences being the file extensions, fila paths, templates, mail message bits etc).

Ideally one button needs to run all 7 bits of code one after the other.

Am I right in thinking that I need to make one large routine by copying all 7 pieces of code into one module?

The bit between the astrisks is the problem area.
Message boxes can be commented out, but if no data is reurned from this query, how do I exit the rest of the routine and move on to the next routine.

The code I have is (I have left out the declarables;


DoCmd.Hourglass True

strReportDate = (Format(Now(), "ddmmyyyy")) 'Format the report date to today's date

strFileExtension = "_PBSE_Stats_Summary.xls" 'set the file extension of the file to be sent

'SQL Statement to extract a list of Cupids
strSql = "SELECT DISTINCT [tblAllCupidPBSEStats].[CUPID]" & _
"FROM [tblAllCupidPBSEStats]"

'Set recordset Cupidrst to consist of the DISTINCT Cupids (in query sql1)

Set Cupidrst = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot, dbReadOnly)

'*****************************************

If Cupidrst.EOF Or Cupidrst.BOF Then
MsgBox "There are no PBSE Stats Summary Reports to send!"
[Forms]![frmPBListings]![chkEmailStatsSummaryPBSE] = True
DoCmd.Hourglass False
Exit Sub

Else

'*****************************************

'Set recordset Cupidrst to consist of the DISTINCT Cupids (in query sql1)
Set Cupidrst = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot, dbReadOnly)

Cupidrst.MoveFirst 'Go to the first Cupid in the list

Do While Not Cupidrst.EOF

StrCupid = [Cupidrst]![Cupid]


'Get the email addresses for the Cupid
strSql2 = "SELECT [tblEmailOrgUser].[Email_Address]" & _
"FROM [tblEmailOrgUser], [tblEmailOrg]" & _
"WHERE [tblEmailOrgUser].[Company_Name] = [tblEmailOrg].[Company]" & _
"AND [tblEmailOrg].[Cupid] = " & [Cupidrst]![Cupid]


'Sets the Reciprst to be the required email addresses
Set Reciprst = CurrentDb.OpenRecordset(strSql2, dbOpenSnapshot, dbReadOnly)


Do While Not Reciprst.EOF
strRecipId = [Reciprst]![Email_Address]


'sets the filename to be the current Cupid's file (with the correct file extension
strFileName = StrCupid & "_" & strReportDate & strFileExtension


'Create the Outlook session
Set objOutlook = CreateObject("Outlook.Application")


'Create the message
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg

'Add the To recipient(s) to the message
Set objOutlookRecip = .recipients.Add(strRecipId)
objOutlookRecip.Type = olTo


'Set the Subject, Body and Importance of the message
.subject = StrCupid & " " & "PBSE Stats Summary Report"
.Body = StrCupid & " " & "PBSE Stats Summary Report attached"
.Importance = olImportanceNormal


'Add attachments to the message.
If Not IsMissing("D:\Databases\PBSE Data\Output\PBSE Stats Summary\" & strFileName) Then
Set objOutlookAttach = .Attachments.Add("D:\Databases\PBSE Data\Output\PBSE Stats Summary\" & strFileName)

End If


.Send


End With


Reciprst.MoveNext


Loop


Set objOutlookMsg = Nothing
Set objOutlook = Nothing


Cupidrst.MoveNext


Loop


End If


'MsgBox "PBSE Stats Summary Reports sent!"

[Forms]![frmPBListings]![chkEmailStatsSummaryPBSE] = True

DoCmd.Hourglass False

End Sub

I think we're talking in circles here. Here's how I'm saying you may try it:

In the form you have three procedures not tied to any objects. Say prcEmail1, prcEmail2 and prcEmail3. So each procedure sends one email. So you have button one that simply calls prcEmail1. Button two calls prcEmail2 and button three calls prcEmail3. They all send one email at a time.

So then you have a fourth button that sends all of the emails. So it calls prcEmail1, prcEmail2 and then prcEmail3 in order, one right after the other.

???

Ken - yes I tried that.
I think to get this to work how it needs to work I am going to have to replicate the modules (and rename them), and comment out (or better still remove) the message boxes. The 'big button' will have to call these renamed modules and a final message box confirming the job has run appears after processing.

That looks like it is doing the business!
Cheers guys.

Glad you got it working - :)