Mailmerge mess

[Mr Ancovy]

Hi all,

I am just at my wits end. Sorry I am quite a noob with this VBA and have been going round in circles for days now, scouring forums and asking for AIs help (that was painful).

I am trying to create a button in Access (Office 16 version) which:
- Mailmerges fields from my query into a .docx (where a yes / no field is unchecked)
- Saves each record as a pdf
- Saves each Pdf as "my text <Field1> - <Field 2>"
- After performing this routine, each unticked box becomes ticked, then also the date field is auto filled.

So far I have:

Private Sub RunQueryToPdfCmd_Click()
    ' Set the file paths for your data source and template
    Dim dataSourcePath As String
    Dim templatePath As String
    Dim outputFolder As String
 
  ' Update these paths with your actual file locations
    dataSourcePath = "C:\Users\Administrator\Downloads\Access Mailmerge Pdf VBA\New Microsoft Access Database.accdb"
    templatePath = "C:\Users\Administrator\Downloads\Access Mailmerge Pdf VBA\New Microsoft Word Document.docx"
    outputFolder = "C:\Users\Administrator\Downloads\Access Mailmerge Pdf VBA" ' Define the output folder
  
    ' Create a Word Application object
    Dim objWord As Object
    Set objWord = CreateObject("Word.Application")

    ' Open the Word template
    objWord.Documents.Open templatePath

    ' Connect to the Access data source
    objWord.MailMerge.OpenDataSource _
        Name:=dataSourcePath, LinkToSource:=True, AddToRecentFiles:=False, _
        Connection:="Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" & dataSourcePath & ";", _
        SQLStatement:="SELECT * FROM AgenciesToContactQ"

    ' Perform the mail merge
    On Error Resume Next
    objWord.ActiveDocument.MailMerge.Execute
    If Err.Number <> 0 Then
        ' Handle the error here (e.g., display a message)
        MsgBox "Error: " & Err.Description
        Exit Sub
    End If
    On Error GoTo 0 ' Disable error handling

    ' Loop through the merged records
    Do While objWord.ActiveDocument.MailMerge.DataSource.RecordCount > 0
        ' Customize the file name based on your field values
        Dim fileName As String
        fileName = outputFolder & objWord.ActiveDocument.MailMerge.DataSource.DataFields("Agency").Value & " - " & objWord.ActiveDocument.MailMerge.DataSource.DataFields("Branch").Value & ".pdf"
      
        ' Save the current record as a PDF
        objWord.ActiveDocument.ExportAsFixedFormat OutputFileName:=fileName, ExportFormat:=17 ' 17 corresponds to PDF format
      
        ' Move to the next record
        objWord.ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord
    Loop

    ' Close Word
    objWord.Quit
    Set objWord = Nothing
End Sub

AFAIK at the moment my problem is

' Move to the next record
        objWord.ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord

Sorry but I have no idea how to fix that. My brain is turning to sludge after trying 6 or so different methods / patching bits of code from different sources, all on a hiding to nothing.

I have created a new sample database and word doc with MM setup. I even notice this new one behaves differently against the old original one. Would I even need to "flush / clean / purge - I'm not sure the correct term some old "dirty residual" code somewhere somehow?

Hopefully I have provided enough info, but please feel free to ask anything and I will try my best to give anything else needed.

TIA

 

[June7]

Using Office 2010. Get "unreadable contents" error attempting to open Word document - possibly because of language - I can get past it but not sure what was changed in the process. Why are you using mail merge instead of Access report?

 

[Gasman]

I was unable to open the word document, as only have 2007, Access thought it was corrupt.

 

[Mr Ancovy]

Using Office 2010. Get "unreadable contents" error attempting to open Word document - possibly because of language - I can get past it but not sure what was changed in the process. Why are you using mail merge instead of Access report?

Because it would be simpler to use a word doc with dynamic fields rather than boxes that don't sync in exactly to form a neat letter. Considering some addresses have one 1 line, others 2, formatting in the reports would be more aggro. I don't see why this needs to be such a major headache when they are both from the same MS Office "family". Access provides the option to wordmerge and I notice there are plenty of other people that prefer to follow this method too.

 

[Edgar_]

The mailmerge feature is buggy, I have had better results just automating Word to open an existing *.docx template previously formatted the way I want it and, using the Find and Replace feature, finding keywords within the template with the same name as fields in my table. That way I don't have to deal with any connection nonsense and other problems that mailmerge has.

 

[Edgar_]

Here's a sample file, test it and see if it helps you.

 

[Pat Hartman]

I don't ever use mailmerge either for various reasons BUT, if you use mailmerge, you first create a file that contains ONE record for each document you want to print so there is NO VBA looping in the process. You feed the file to word and word does the whole thing without other interaction.

 

[June7]

Does this help https://www.tek-tips.com/faqs.cfm?fid=3237

or https://bytes.com/topic/access/answers/673224-albert-kallals-access-word-mail-merge-2007-a

 

[Minty]

Agreed with the others that the mail merge is buggy.
Using a Word Template, I too have gone down the find and replace route, as if the data source gets large the Word document takes an eternity to load.

The Access report can be designed to cope with variable length addresses etc. Generally by using padding and concatenation in the source query, or getting a little inventive with the can grow properties of the report sections.

 

[Mr Ancovy]

Im trying to post a reply, but cant. System tells me it thinks it could be spam

 

[Mr Ancovy]

That tek tips link looks very promising, pretty much sums up perfectly what i want to do.
I did come to realise from that, at least I needed to select the word library too. So, many thanks for that pointer

I tried adapting the code to fit my database, but it doesnt like some syntax in the sql string

strSQL = "SELECT AgenciesT.Agency, AgenciesT.Branch, AgenciesT.AgyTitle, AgenciesT.AgyFirstName, AgenciesT.AgyLastName, AgenciesT.AgyAddress, AgenciesT.AgyAddress2, AgenciesT.AgyTown, AgenciesT.AgyPlz, AgenciesT.AgyEmail, AgenciesT.FirstContactMade, PositionT.Position, PositionT.JobSource"
FROM AgenciesT INNER JOIN PositionT ON AgenciesT.AgencyID = PositionT.AgencyID
WHERE (((AgenciesT.FirstContactMade)=False));"

Would there be an issue where his query is based on postcodes, as oppossed to mine is based on yes / no: false?

 

[Mr Ancovy]

Using Office 2010. Get "unreadable contents" error attempting to open Word document - possibly because of language - I can get past it but not sure what was changed in the process. Why are you using mail merge instead of Access report?

FYI, the post above is reply to your other post. thx

 

[Mr Ancovy]

I don't ever use mailmerge either for various reasons BUT, if you use mailmerge, you first create a file that contains ONE record for each document you want to print so there is NO VBA looping in the process. You feed the file to word and word does the whole thing without other interaction.

Performing the whole procedure for each record individually would also be an option, where just clicking when focussed on a single record executes the whole procedure of inserting fields into doc, exporting as pdf, then clearing up / refreshing. Do you have any idea the best code for that please?

 

[Pat Hartman]

See if this example is helpful.

Word Automation Sample

This database presents an example of using VBA to fill bookmarked fields in a Word document. The Database is A2003 format. However, the only version of Word installed on my PC is W2007 so before using the example, you may need to open any code module and change the Word reference to whatever...

www.access-programmers.co.uk

It is a sledgehammer approach and is only viable if you have a limited number of word documents you want to fill. I'm working on a different example that uses a data bound approach. This will allow you to easily map the bookmarks in a Word document with a table or query. Don't wait for it though, it is pretty far down on my to do list.

 

[Edgar_]

@Mr Ancovy Did you see any problems with the example I gave you? it does not need any bookmarks, just writing {{someField}} in the Word template. It already goes through each section of the Word document. Kinda, I think it doesn't look inside shapes, but that's just another line of the code, luckily, it can be optimized much further.

the Word document takes an eternity to load.

The one I posted worked great even for super big Long Text fields and queries with 200+ fields. But the templates were no more than 10 pages long. The computers were it was used were very fast, though, maybe the scenario you refer to has more to do with the computer.

 

[Mr Ancovy]

@Mr Ancovy Did you see any problems with the example I gave you? it does not need any bookmarks, just writing {{someField}} in the Word template. It already goes through each section of the Word document. Kinda, I think it doesn't look inside shapes, but that's just another line of the code, luckily, it can be optimized much further.

The one I posted worked great even for super big Long Text fields and queries with 200+ fields. But the templates were no more than 10 pages long. The computers were it was used were very fast, though, maybe the scenario you refer to has more to do with the computer.

Sorry for late reply Edgar,

I looked into your idea, that also seems ideal. It doesnt matter that it isnt mailmerge, just as long as the info goes into a word doc, which your version appears to do ok.

However I am a bit stuck with tweaking the code to fit into my application, maybe cos i'm not so familiar with your "maverick" non mailmerge way of doing it. If you could guide me through / enlighten a little bit more the I would be most grateful

TVM

 

[Gasman]

@Mr Ancovy Did you see any problems with the example I gave you? it does not need any bookmarks, just writing {{someField}} in the Word template. It already goes through each section of the Word document. Kinda, I think it doesn't look inside shapes, but that's just another line of the code, luckily, it can be optimized much further.

The one I posted worked great even for super big Long Text fields and queries with 200+ fields. But the templates were no more than 10 pages long. The computers were it was used were very fast, though, maybe the scenario you refer to has more to do with the computer.

@Edgar,
For me I received an error

Private Sub btnFillTheTemplate_Click()
  Dim ResultingFilePath As String
  If Not IsNull(Me.cboPeople) Then
    ResultingFilePath = templateReplacer("qryPeople", "p.PeopleId=" & Me.cboPeople, CurrentProject.Path & "\Templates\SomeLetter.docx", "TheLetter")
    Application.FollowHyperlink ResultingFilePath
  End If
End Sub

ResultingFilePath was "" despite all the other variables being populated?

However I still had a doucment opened with some text about 1220 characters?

 

[Gasman]

Sorry for late reply Edgar,

I looked into your idea, that also seems ideal. It doesnt matter that it isnt mailmerge, just as long as the info goes into a word doc, which your version appears to do ok.

However I am a bit stuck with tweaking the code to fit into my application, maybe cos i'm not so familiar with your "maverick" non mailmerge way of doing it. If you could guide me through / enlighten a little bit more the I would be most grateful

TVM

Personally I would go with the bookmarks option by @Pat Hartman ?
Seems easier to implement?

 

[Pat Hartman]

Did you see any problems with the example I gave you?

The only problem I see is that you can't use the same field more than once. So, if you are writing a letter, you won't be able to properly address it since you would need to use the LastName twice. That means that you would need to know you need to create alias' to handle that situation.

 

[Edgar_]

@Mr Ancovy Sure, send me a PM next week to remind me of that, I could optimize it too, I made it a while ago when I didn't know much about debugging VBA.
@Gasman If it opened the Word document and filled the text about 1220 characters, it worked. What test did you perform to make it return an empty string? Damn, I keep writing code that fails with your version of Office, 2007, right?
@Pat Hartman If you write the same {{field}} multiple times in the template, it should fill them all. I just tested it.