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

 

[Gasman]

@Pat Hartman I was just curious to see the method.
However it seemed so much more complicated to me, especially when I had to resolve that error.
Having just a quick look at your method, I could see that I would be able to debug any issue so much quicker. Hence why I said I would use your bookmark method

 

[Pat Hartman]

I agree, I think the bookmark method is better which is why I use it I should get back to work on my sample that uses a loop to match table/query fields to bookmarks. In the real world, that is the best method. Some of my clients have thousands of word documents although most have less. The first app I built that needed to do more than a couple of documents was an app for an insurance company and they had thousands because we have 50 states and each has their own rules and so many endorsements and sets of policy documents have potentially 50 versions each. The company was already using the bookmark method but they were using mailmerge from Excel. Access streamlined the whole process dramatically

 

[Edgar_]

So, just like that, you go and disqualify my attempt to help OP, @Pat Hartman?

 

[Gasman]

So, just like that, you go and disqualify my attempt to help OP, @Pat Hartman?

I plumped for Pat's method as I could understand that easily. So I should be able to modify it to my needs, if I needed to do this.
Your method would take me a lot more time, and who knows how much longer for the O/P if they do get to understand it.

If I wanted to do it the O/P's original way I would have a loop that produced just the records for the document, which in this case appears to be just the one.

 

[Gasman]

@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?

I would not worry about it unless it happens to others.
I have found that some things do not work in 2007, but sometimes that highlights an error in logic?
I did not spend too much time on it, as I mentioned it would take me a while to get to understand it all, and I was just curious as to the method.

 

[Mr Ancovy]

So I also have just another (small, hopefully) thing to iron out.
I have been trying to make a calculated query ( im guessing using "Iif" is the way. I have tried getting help from some YT tut's but my need is not based on a math / numerical calc.

For the Letter intro there is 2 options :
1 "Sehr geehrte" - when writing to "Frau"/"Damen und Herren" OR
2 "Sehr geehrter" - when writing to "Herr".
So by default everything shows as "Greeting: "Sehr geehrte" & " " & [BriefTitle]"". Although I need to include condition when Title is HERR, to add "r" after geehrte = geehrter

Sorry, but the builder doesnt quite want to "play ball" and am a bit muddled how / where to correctly type in the criteria (which column, "input" "output")?

Hope I made that clear enough.

TY

 

[Pat Hartman]

So, just like that, you go and disqualify my attempt to help OP, @Pat Hartman?

Perhaps we have a language issue. Maybe you should read my response again and not be so sensitive. I was just agreeing with Gasman.

 

[Edgar_]

Perhaps we have a language issue. Maybe you should read my response again and not be so sensitive. I was just agreeing with Gasman.

No, there isn't a language barrier here. This is a community where people seek assistance and others offer their help. You previously asserted that my solution was flawed without providing any evidence to support your claim. Afterward, I refuted your assertion, and yet, you, as a Staff member with "Super Moderator" credentials, continued to promote your theoretical approach without acknowledging my response. I'm not sure what kind of discussion this is if it's not a dismissive and unfriendly disqualification.

 

[Edgar_]

@Mr Ancovy
Try: Greeting: Iif([agyTitle]="Herr","Sehr geehrte" & " " & [BriefTitle],"Sehr geehrter" & " " & [BriefTitle])

 

[cheekybuddha]

This is a long thread and I haven't read it thoroughly, so apologies if I'm repeating anything already said.

Word mailmerge directly to Access has forever been buggy - don't do it unless you want a lot of headaches.

To automate merge from Access, write your query results to a csv file and use that as the datasource for your merge.

Search 'super easy word merge' on this page by Albert Kallal for an example implementation.

 

[Pat Hartman]

Yes, your solution works. I am sorry, I forgot to mention that after you corrected me. I was thinking that you were using bookmarks which require unique names. You aren't using bookmarks so find and replace works.

The advantage to using field objects is that they allow the user to manually fill a document as they occasionally want to do. They can open the document and the only place they can type is into the field objects. These are treated as bookmarks when filling automatically but actual bookmarks are only used when the data field may require more than 255 characters of input.

 

[Mr Ancovy]

@Mr Ancovy
Try: Greeting: Iif([agyTitle]="Herr","Sehr geehrte" & " " & [BriefTitle],"Sehr geehrter" & " " & [BriefTitle])

Das ist perfekt. Danke Sehr

Although, for your future reference: I did have to switch the R after Herr. Huiii, Deutsche sprache is so verdammnt komplitziert.


Die förmliche Anrede in Brief, Fax oder E-Mail lautet:

• Sehr geehrter Herr Hofmeier
• Sehr geehrte Frau Riedmüller
• Sehr geehrte Frau Mayer, sehr geehrter Herr Mayer
• Sehr geehrte Damen und Herren

 

[Edgar_]

Yes, your solution works. I am sorry, I forgot to mention that after you corrected me. I was thinking that you were using bookmarks which require unique names. You aren't using bookmarks so find and replace works.

The advantage to using field objects is that they allow the user to manually fill a document as they occasionally want to do. They can open the document and the only place they can type is into the field objects. These are treated as bookmarks when filling automatically but actual bookmarks are only used when the data field may require more than 255 characters of input.

I avoided the bookmarks because they were not compatible with a library of code that would have to be implemented for the web later, so I had to overcome the 255 characters limit in my solution by using the clipboard.

It is the code for the clipboard that makes the solution look intimidating, but when I made it, I didn't know better. I should have placed that code in a separate module and the main code that does the heavy lifting should have its own module too. It can also be further optimized with better loops and refactoring. So, if anyone needs it more polished, they can PM me.

 

[Mr Ancovy]

Can I reply to messages with links in them yet

 

[Mr Ancovy]

This is a long thread and I haven't read it thoroughly, so apologies if I'm repeating anything already said.

Word mailmerge directly to Access has forever been buggy - don't do it unless you want a lot of headaches.

To automate merge from Access, write your query results to a csv file and use that as the datasource for your merge.

Search 'super easy word merge' on this page by Albert Kallal for an example implementation.

Thanks for that idea, I don't know what the matter is with it - maybe it's just such old code.

I edited the declaration to allow for PtrSafe/ Longptr. But still get "compile error- type mismatch" at

temppath = Space(255)  ' initialize the buffer to receive the path
   slength = GetTempPath(255, temppath)  ' read the path name
   temppath = Left(temppath, slength)  ' extract data from the variable

 

[cheekybuddha]

maybe it's just such old code.

Yes, probably!

Is that code for getting a filepath?

You ought to be able to switch it out for the built-in Application.FileDialog

 

[Gasman]

Can I reply to messages with links in them yet

Probably, give it a go.

 

[Mr Ancovy]

Yes, probably!

Is that code for getting a filepath?

You ought to be able to switch it out for the built-in Application.FileDialog

I don't know.

Instructions are pretty shonky tbh :
"How to use:
Simply merge (I assume he means export) the 2 forms and 2 modules of code into your application. Then place a button on you ms-access form with the following ONE LINE code. ?!?

MergeSingleWord ?!? In what context? As macro? As VBA? As title?

That is it!! You are done!. Now, just hit the "add new template" button What "new template button", where?!? to create a word template". Now insert your merge fields, and SAVE the document. Next, just try hitting the word merge button again. This is way cool, and easy. I will write up some better instructions, but I have included a sample form for you to try."

Sorry maybe its just me, but this is just way too sloppy, too many headaches and too much aggro. Inconsistent and innefective unless i'm missing something...

 

[Gasman]

Really?, I seem to recall using it a good few years back (2017) and not having any issues?

Perhaps take the time to actually look at and use the sample DB, then you might have a clue as to how it is meant to work?