Mail Merge w/ Access table/query

What does your table look like?

Person 1, Course 1
Person 1, Course 2
person 1, Course 3

or

Person 1, Course 1, Course 2, Course 3 (All in one record?)

It looks like the first example you posted.

Person 1, course 1
person 1, course 2
person 1, course 3
person 2, course 1
person3
and it continues on......

Person 2(or any person) can have anywhere from 1-4 courses listed. I need a mail merge that will only create one letter per person and list out their courses

I've been thinking about this.
Why don't we 'cheat' a little?
It's hard to get a query to convert rows to columns.
On the other hand, it isn't very hard to get Access to build an Excel worksheet...
And there's a LOT of granular control there.

Is that an approach you're confortable with?
How's your VBA coding skills?

Pretty good at vba. I need to do a mailmerge though. What are you thinking in excel

So lets build a worksheet.
1 row for each student with names, emails etc
Then for each row, find out how many courses, and add a column for each course.
Then make the excel sheet the recordset for the mail merge, rather than Access

I was able to produce the results of the query by creating a funciton within VBA.

I set up a table called "People"
it contains two columns... PERSON and COURSE both text

I set up a query called "Distinct_People_query"
SELECT DISTINCT people.[Person] FROM people;

Next set up a query that gets it's result from the above query... called wrapper query:
SELECT person, concatCourses(person) as Courselist
FROM Distinct_People_Query;

WrapperQuery is the query that would be called by your mailmerge function:

Within wrapper query a call is made to a VB function written for this called concatCourses:
Attached is the code for concatCourses.  This code would be inserted as a VBA Module.... not a Class Module.

Here are the results from wrapperquery:

person      Courselist
------------------------------------------------------------
Person1      Course1a,Course2a,Course3a
Person2      Course1b,Course2b
Person3      Course1c

Public Function ConcatCourses(thisPerson As String) As String


    Dim rst As DAO.Recordset
    Dim strResults As String
    Dim strSql As String
    
    
    strSql = "select people.course from people where person='" & thisPerson & "';"
 '   strSql = "select course from people"
    'open a recordset based on the query
    
    Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
 
    'loop though the query and build the results string
    'on the first time through the loop do not add the delimiter
    While Not rst.EOF
        If Len(strResults) > 0 Then
            strResults = strResults & "," & rst.Fields(0)
        Else
            strResults = rst.Fields(0)
        End If
        rst.MoveNext
    Wend
 
    'return the results string
    ConcatCourses = strResults

End Function

Select allOpen in new window

This is what my "People" table looked like to start with:
ID      Person      Course
----------------------------------------------------
3      Person1      Course1a
4      Person1      Course2a
5      Person1      Course3a
6      Person2      Course1b
7      Person2      Course2b
8      Person3      Course1c

Ok,

It's done
I built a test db
Three tables, two queries and a form.

It'll build a spreadsheet in the same directory as it is put in that will be suitable as a mail merge source

Let me know how it plays
courses.mdb

It's an elegant little thing to keep around anyway.
Building spreadsheets from Access data via code can be very handy.
Data that users can screw up to their hearts' content--that you can create, or they can--with a button click

What do you mean a spreadsheet in the samd directory

attached is access db... wrapperquery will return the results you were asking for.
Database4.accdb

I am trying to follow Nick67s example.

I just built 2 querys has follows:

Qry1: Learner ID- text field, first name, last name, date, course, required date, supervisor
Qry2: Learner ID- text field, first name, last name, count of learnerId.

I keep getting an error with the code

Please Help

So i am also having trouble because I need the dates listed next to the courses as shown below in a sample letter. Also, I need the letter in PDF format.

Dear [first name] [last name],

The following courses are overdue:

[Course Title] [Required Date]
[Course Title] [Required Date]

Thank you

CC: [Supervisor Name]

buttersk-

your way is easier however it won't produce the results I described above in my new post. Perhaps you can help me alter the query

I am not clear on what you want to see in the results of your query.

Are you wanting results from two separate queries?  

(from your post above)

Qry1: Learner ID- text field, first name, last name, date, course, required date, supervisor
Qry2: Learner ID- text field, first name, last name, count of learnerId.

In Nick67's way you need 2 querys. With yours I only need one which is what I prefer, but how do I get it in the formats I explained above?

My sample is designed such that the code behind the button creates an Excel spreadsheet named mailmerge.xls
It puts that file in the same folder as courses.mdb is located.

You then create a Word mail merge document and use mailmerge.xls as the data source.

Here are all three

Put them on the desktop to start.
The mail merge is VERY dependent on where it thinks the Excel sheet is.
You'll undoubtably need to re-point it because your username isn't mine :)
courses.mdb
mailmerge.xls
The-merge.doc

How are you making out with this part of your puzzle?

Nick67-

Let me explain a little bit of where I am with what I am trying to do. Attached is a sample database I created. I just took the query "qryCreateMemo-Single" and launched the mail merge wizard through access. I wrote the memo and placed the fields where I needed them and created all the memos. I had aboout 450 so I ran the code above to break them up and save them individually to a specific folder.

Now I need all these to be a pdf. I dont mind going through the steps again, but perhaps we can add on to my code. Also, I still need to figure out from what I have how to do the MULITPLE courses. I only did the learner who have SINGLE courses so far.

Last night after I wrote
<I suppose you now want it to email yet to boot?>
I had the palm-smack-forehead moment

You want to email out pdf's with specific data on them.
palm-smack-forehead
palm-smack-forehead

You want a report, you want to save it as a pdf, you want to email it.
That is TRIVIALLY easy compared to what we have been doing
No Excel, no mail merge, no monster query.
Just a report, a subreport, a loop to run one record at a time, and some code to create emails.

Oh well, it was a learning experience :)
palm-smack-forehead

P.S. You didn't attach a file
Do so, and I'll get it beat into shape

I think I do need a mail merge.

Did you read my previous post? So on the sample DB i sent you I have a memo template and am using the access fields to create it? I did all the ones with a single course, however I still need to figure out how to do multiples.

I can send you a sample of the memo template and email template if you need this.

There are no files attached to your posts

oh sorry

I thought I attached it.
Database1.mdb

<I can send you a sample of the memo template and email template if you need this. >

Sure, post them.
Oh BTW.  I did a compact-and-repair after I downloaded your db
It went from 34 MB to 236 KB :)

thank you. Its just a sample. my actual db is close to 2 gb

That's what I figured
I couldn't see what was making it so huge that it took four minutes to download
Just empty space :)

<I can send you a sample of the memo template and email template if you need this. >
Please?

Memo-Template1.doc
Email1.doc

Yup, definitely
<Just a report, a subreport, a loop to run one record at a time and save as pdf, and some code to create emails.>

Give me a bit

ok

Here is the interim.
Have a look at rptLearnerDelinquencyNotice.

Now,
Save each page as PDF and create emails.
Give me some feedback
kwarden.mdb

Nick67-

Look great! However, I need a seperate memo for each person and need to save each one individually as a PDF. Also, I would like to automate the email to be generated and attach the email. Also, a read receipt feature.

I know I am asking a lot.

Oh and each PDF needs to be saved as the learner ID in a folder on my drive.

Remember the palm-smack-forehead moment? :)

I take it you don't have a lot of experience with Access reports?

No I usually export everything to excel

<grin>
That works, and reports are sometimes the most difficult thing to grapple with.
Once you get the down pat, though, they rock.
</grin>

So can you help automate the things stated above?

<.... need a seperate memo for each person and need to save each one individually as a PDF. Also, I would like to automate the email to be generated and attach the email. Also, a read receipt feature.>
<each PDF needs to be saved as the learner ID in a folder on my drive>

I really need this. I wish I could give you like triple points or something

You've got a lot of questions open :)
And you can ask a related question.

...and yes, I am working on it :)

There's a bunch of stuff to tweak,
But voila
kwarden.mdb

Oh, read receipt I didn't do

Just add

.ReadReceiptRequested

in below the

.HTMLBody stuff

I guess I am missing something. I think I need to go back to the other way. I apologize but I dont think I can use what you have. It doesn't match the initial format and when I run it there is no pdf attachment w/ the email. I am more concerned with getting the pdf attachments right at the moment. I will worry about emailing after.

It plays on my side, forming up mail messages, pdf's and attaching them.
It depends on a folder c:\tempPDF existing, and being able to write/delete from

The body of the mail message is:

Jane Smith



The attached Notice to File is being placed in your Personnel File for failure to complete your required compliance training by the due date.
Please CLICK HERE to take your courses immediately. This training is mandatory and critical to ensure you are aware of important Company policies and procedures.
In the future, failure to complete your Compliance training by the due date may lead to disciplinary action, up to and including termination.

The PDF is attached
I can send you the sample email if you have an address


123456789.pdf

Where is the PDF stored?

My email is knw.fsu@gmail.com

Also, if you go to the design view of "rptLearnerDelinquencyNotice". How do I get the field under the learner ID header to show up

Also, how do I get the header on every page/memo. (the memorandum header and fields under the learner id header.

That is some of the possible tweaking that will be needed.
By default, they go to C:\tempPDF, and Outlook looks for them there to attach them
Tweak as needed

When they are created
From SaveAsOfficePDF
DoCmd.OutputTo acOutputReport, stdocname, FormatValue, "c:\tempPdf\" & LearnerID & ".pdf"

from modEmailReports
AttachmentPath = "c:\tempPDF\" & filename

ok so I changed the memo and that works fine. I just need to do some more tweaking to formatting and stuff. I'll play around and see. But do you how to get the headers and such as mentioned above

<Also, if you go to the design view of "rptLearnerDelinquencyNotice". How do I get the field under the learner ID header to show up >
if you want that stuff to show, you have to right-click the header, Properties, Visible = yes
I had that stuff hidden.

Reports are funny.
You can't use a field in code unless it exists in a control
What's more, if it is in a part of a control that doesn't get done over and over (ie report header) it doesn't change value
The learner id header exists solely to hise those controls and ensure they update as txtBlurb's value gets built in code

The mulit-page report is not the thing you are interested in printing/emailing
Anything in report header/footers is produced once
Everything in page header/footers winds up on each sheet

Tweaking :)

You wanted by Learner ID, so what you see on page one will be the format you see on each individual report, when things are filtered down to one user, and then looped.

In the email you must tweak the CLICK HERE <a href>  Google isn't where you want them to go!
   .HTMLBody = .HTMLBody & "Please <a href = 'http://www.google.com'>CLICK HERE</a> to take your courses immediately.   This training is mandatory and critical to ensure you are aware of important Company policies and procedures.<br>"

I don't know if you need the BCC

   ' Add the BCC recipient(s) to the message.
    Set objOutlookRecip = .Recipients.Add("Kwarden")
    objOutlookRecip.Type = olBCC

Comment or delete it, or put a proper email or Outlook user name in

More tweaks
Open report subDelinquencies
Take the stuff from the report header and put it in the footer
Take the stuff from the page header and put it in the report header
Make the report header visible, make the report fotter invisible

More tweaks in modEmailReports

Put Public EnoughPrompts as long

at the top of modEmailReports

Tweak this

if  EnoughPrompts = 0 then

MsgBox "The email is about to be created!" & vbCrLf & _
        "If nothing appears to be happening, the Outlook security box may be hiding behind an open window." & vbCrLf & _
        "Click the Outlook icon on the taskbar to bring it to the front, if necessary."

end if
EnoughPrompts = 1

Then put
EnoughPrompts = 0
as the last line in cmdCreateReports_Click()

Ah,

I have a logic error in the cmdCreateReports.
I wanted emails only for delinquents, but I didn't specify Distinct, so I have one for each delinquency, and not one for each delinquent

My bad.

This is hard for me to test because Access 2003 can't save to PDF :0

Attached is an example of the format of the PDF I need to generate for every memo. I am going to continue trying to tweak, but it would be great if you could help! :)
Example.doc

change the first set rs... in cmdCreateReports to

Set rs = db.OpenRecordset("select distinct [learner id] from qryDelinquencies", dbOpenDynaset, dbSeeChanges)

By memo, do you mean the PDF or the email body?

Will the From: be coming from a table field or just hard-code 'Training Dept.'?

Again I hate to do this, however the other way with the mail merge and splitter code I have below works great for the people who have only one course. I would rather see if I can create the same sort of query for the people with multiple courses.

Here is the PDF code
 

Sub SplitMergeLetter()
' splitter Macro modified to save individual letters with

' information from data source. The filename data must be added to

' the top of the merge letter - see web article.

Dim sName As String
Dim docName As String
Dim Letters As String
Dim Counter As Long
Dim oDoc As Document
Dim oNewDoc As Document
Set oDoc = ActiveDocument
'oDoc.Save

Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
     Application.ScreenUpdating = False
     With Selection

          .HomeKey Unit:=wdStory
          .EndKey Unit:=wdLine, Extend:=wdExtend
          .MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend

     End With
     sName = Selection
     docName = "J:\Memos\Single Course\" & sName & ".pdf"

     oDoc.Sections.First.Range.Cut
     Set oNewDoc = Documents.Add

    'Documents are based on the Normal template

    'To use an alternative template follow the link.
     With Selection
         .Paste
         .HomeKey Unit:=wdStory
         .MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
         .Delete
     End With
     oNewDoc.SaveAs FileName:=docName, _
        FileFormat:=wdFormatPDF, _
        AddToRecentFiles:=False
     oNewDoc.Saved = True
     ActiveWindow.Close
     Counter = Counter + 1
     Application.ScreenUpdating = True

   
Wend

'oDoc.Close wdDoNotSaveChanges
End Sub

Select allOpen in new window

I know you have been working hard on this and I will still use the email/Outlook functionality but I think for uniform purposes the mail merge to a PDF is so much cleaner. i just can't figure out how to do multiple courses.

A hard code for the From Field

but then again  if I can't do the multiple courses with the mail merge/query technique then I will need to use your code/process,

All I would need for the other process (mail merge/query) is the right/correct  query and to tweak the letter to accomodate the courses.

Your process is very in-depth. I have no doubt it works, however I do worry about maintaining it since I will be handing this off when ready to do so

That is what code documentation is for :)

And we are almost all the way there now

Note just the first page of the sample
225256255.pdf

It looks great, just take out the date sent field. Also, kinda pety but I am guessing I can change the fonts and color. I want it all black. Also, can we make the course title and required date closer together and only underlined under the words.

Sorry for all the pety stuff but I am sending this out to over 10,000 people.

             

Almost anything can be done

Tweaking :)

<only underlined under the words. > ?

I meant only underline under course title and required date; Not the whole way across. Also push the fields closer together

I am definitely going to take the sample and play around with reports after today. I didn't realize they could be so useful.

Fonts?
Sizes?
Italics?

sample
225256255.pdf

I like this a lot! I think I can figure out the fonts and stuff.

THANK YOU SO MUCH :)

Hang tough.

Here comes the spitting image of your memo.
Now, when you get Outlook to actually SEND messages, it forces a 5 second wait between messages.
I've never had a need to over-ride that
ClickYes can get you over that hump
http://www.contextmagic.com/express-clickyes/
These guys are good too
http://www.mapilab.com/outlook/security/

I have it set to display emails not send them.
That can be tweaked too


DisplayMsg = True
....
   ' Should we display the message before sending?
   If DisplayMsg Then
       .Display
   Else
       .Save
       .Send
   End If
kwarden.mdb

Yes the message definitely needs to be displayed before sending. Can we break out the creation of the PDFs and the actual sending of the memo into 2 buttons?

Sure.

Where it now does two calls in the loop, instead just duplicate the whole code block and deep-six one call
instead of cmdCreateReports-Click()

 

Private sub cmdCreatePdfs_Click()
'this is the half the biggie, sewing it all together
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select distinct [learner id] from qryDelinquencies", dbOpenDynaset, dbSeeChanges)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF = True
    Set rs1 = db.OpenRecordset("Select * from qryLearnerDetails where [Learner ID] = " & Chr(34) & rs![Learner ID] & Chr(34), dbOpenDynaset, dbSeeChanges)
    Call SaveAsOfficePDF(rs![Learner ID])
    rs1.Close
    Set rs1 = Nothing
    rs.MoveNext
Loop

end sub

Select allOpen in new window

and

Private Sub cmdCreateEmails_Click()
'this is the other half of the biggie, sewing it all together
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select distinct [learner id] from qryDelinquencies", dbOpenDynaset, dbSeeChanges)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF = True
    Set rs1 = db.OpenRecordset("Select * from qryLearnerDetails where [Learner ID] = " & Chr(34) & rs![Learner ID] & Chr(34), dbOpenDynaset, dbSeeChanges)
    Call CreateAnEmail(rs![Learner ID] & ".pdf", rs1!FullName, Nz(rs1!Email, ""), Nz(rs1![Supervisor Email], ""))
    rs1.Close
    Set rs1 = Nothing
    rs.MoveNext
Loop

EnoughPrompts = 0
End Sub

Select allOpen in new window

Also, where do I find the txtBlurb?

I have no idea what you talking about with the code above?!?
<Where it now does two calls in the loop, instead just duplicate the whole code block and deep-six one call
instead of cmdCreateReports-Click()>

Ok.

Throw the form into Design View
Right click the 'Create Reports' button.
Open the On Click event

I still dont see it. I am trying to view the actual memo text, not the fields but the paragraphs

On rptLearnerDelinquencyReport

txtBlurb is the unbound control between Subject: and the sub report

txtBlurbB is the unbound control just above the page footer

The paragraphs are all built in VBA code, in the Detail_Format event

I see the unbound control, but where is the actual text. I just need  to tweak a word

Reports are very different from MS Word.
Text has to come from fields in tables/queries or be built and assigned to me.somecontrol.value by VBA code

The email body text is built in code too
in modEmailReports

vbcrlf is a carraige return

so how do I go in and change a word

Just found it

in the code window
CTRL-F
find your phrase in the code, and tweak it

Let's say 'Compliance Training'

it's here in the report Detail_format event

Me.txtBlurbB.Value = Me.txtBlurbB.Value & "In the future, failure to complete your Compliance training by the due date may lead to disciplinary action, up to and including termination.  A copy of this memo will be placed in your Personnel File.  " & vbCrLf & vbCrLf


change it to whatever

Me.txtBlurbB.Value = Me.txtBlurbB.Value & "I am going to eat your babies  " & vbCrLf & vbCrLf

I gotta pick up my kid from school and eat lunch.
1 hr, 1:15 or so

Look through everything in the code window

I will thank you. I am going to try and break out the code into 2 steps:

1. Create PDFs
2. Create Emails

Thank you again! Ill post if I have any issues

I keep getting "expression has no value error" on the line of code below:

 

Me.txtBlurb.Value = [First] & " " & [Last] & ", you are receiving this Notice to File for failure to complete your required compliance training by the due date.  You were required to complete:" & vbCrLf & vbCrLf

Select allOpen in new window

Also, If I hover with my mouse over "Me.txtBlurb.Value"  it equals isNull. I can't figure out the issue. If I just open the report it is fine, its when I run the below code from the button on the form.

 

Private Sub cmdCreatePdfs_Click()
'this is the half the biggie, sewing it all together
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select distinct [learner id] from qryDelinquencies", dbOpenDynaset, dbSeeChanges)
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF = True
    Set rs1 = db.OpenRecordset("Select * from qryLearnerDetails where [Learner ID] = " & Chr(34) & rs![Learner ID] & Chr(34), dbOpenDynaset, dbSeeChanges)
    Call SaveAsOfficePDF(rs![Learner ID])
    rs1.Close
    Set rs1 = Nothing
    rs.MoveNext
Loop

End Sub

Select allOpen in new window

[First] & " " & [Last]  are field from the query.
Do you have a adata booboo where one of those values was left Null?
Null is hated by concatenation

try Nz(first,"Damn!")  & " " & nz([Last],"it!")
Nz catches null and substitutes in what you wrote after the comma.

In production, put nicer things in.
It's just good in debugging top have the app do all the swearing for you :)

No records have a null values in those fields. I tried it with 2 records

Its says is null on the txtblurb field

Null & "something" makes null
Another possibility is that rs or rs1 aren't returning any records.
I didn't build in checks for

if rs.recordcount = 0
if rs1.recordcount = 0

Playing with something that extends it, I get the same thing.
Give me a bit and I'll push up some revisions

Reports have a No_Data event.
You can catch and cancel and event that has no valid records sent to it

Private Sub Report_NoData(Cancel As Integer)
MsgBox "The query and/or filtered query returned no records.  The report won't open"
Cancel = True
End Sub

Thank you. Ill test it out

No Problem.

Have a good weekend

Nick67-

You have been so thorough and patient with me. Thank you so much. Your solution works great!

<sheepish uncertainty>

Is it good?
We are an awfully long way from where we started!
Do you understand it?
Commandment 8, you know?
http://www.mvps.org/access/tencommandments.htm

Nick
</sheepish uncertainty>

Hi Nick67-

I had another question. So I took what you did and replicated it for each Region ( because each region has its own folder). So the createPDF button need an error handler I think, since that particular region might not have any records.

Not sure how to create this though. I am sure it's simple. Basically if I run the createPDF click event on the button and there are no records in the querys to be created then it ends. However i need it to quit that click event and go on to the next.

You'll have to give me a couple days.  No nice way to say this:  tomorrow I have to bury my older brother

I am not certain how you altered the cmdCreatePDFs_Click event.
Did you create a loop within the code?
I take it [BA] is your regions?
Add it to qryDelinquencies
Add it to qryLearnerDetails
Create a table (most elegant) or an array to hold the path where each region will store its PDFs

I'm guessing
See if my speculations point you the right direction
Sorry for the post on the 26th.  I don't text well.  My older brother passed unexpectedly on the 18th
His funeral was the 27th.  Tough days.  Life goes on though.  No choice.

If you build the loop in, it won't need to error handle as the altered recordset will be empty for regions with no delinquents and nothing will print

 

Private Sub cmCreatePDFs_Click()
'half
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim rsRegions As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("select distinct [learner id] from qryDelinquencies", dbOpenDynaset, dbSeeChanges)
Set rsRegions = db.OpenRecordset("select distinct [BA] from qryDelinquencies", dbOpenDynaset, dbSeeChanges) 'recordset with the possible regions
rs.MoveLast
rs.MoveFirst
rsRegions.MoveLast
rsRegions.MoveFirst
Do Until rsRegions.EOF = True 'loop through regions
    Do Until rs.EOF = True 'loop through each learner in the region
        Set rs1 = db.OpenRecordset("Select * from qryLearnerDetails where [Learner ID] = " & Chr(34) & rs![Learner ID] & Chr(34) & " and [BA] = " & Chr(34) & rsRegions![BA] & Chr(34), dbOpenDynaset, dbSeeChanges)
        Call SaveAsOfficePDF(rs![Learner ID], DLookup("RegionPath", "tblRegionPaths", "[BA] = " & Chr(34) & rsRegions![BA] & Chr(34)))
        'SaveAsOfficePDF now takes the learnerID for the file name and a full path to store the file in
        'path needs a trailing \  ie c:\tempPDF\
        'I presumed you'd create a table
        'if not, you need to code another method to pass in that path
        rs1.Close
        Set rs1 = Nothing
        rs.MoveNext
    Loop
    rsRegions.MoveNext
Loop
End Sub

Select allOpen in new window

Private Sub SaveAsOfficePDF(LearnerID As String, RegionPath As String)
Dim FormatValue As String
If Application.Version > 11 Then
    FormatValue = "PDF Format (*.pdf)"
Else
    FormatValue = acFormatRTF
End If

Dim stdocname As String
stdocname = "rptLearnerDelinquencyNotice"

DoCmd.OpenReport stdocname, acPreview, , "[Learner ID] = " & Chr(34) & LearnerID & Chr(34)
DoCmd.OutputTo acOutputReport, stdocname, FormatValue, RegionPath & LearnerID & ".pdf"
DoCmd.Close acReport, stdocname, acSaveYes
End Sub

Select allOpen in new window

Ah, there were some logic bugs in cmCreatePDFs_Click()
I didn't build the loop quite right.

Here's a working sample.
Change values in tblRegionPaths as appropriate
Folder structure must exist prior to running

PS I didn't look for how this effects the emailing!
kwarden.mdb