Mail Merge ****up

[Mike2502]

Hi All,

I've just shot myself in the foot, I've been up all night trying to make this work but keep failing.. I've had a working mail merge and it came up with a 'compatibility fix' and I did it and now it messed up my code I think!
I've done a trial and error and it seems that my mail merge in word will not run unless my excel file is saved however this wasn't the case before?

I've attached my mail merge code from word and excel and I hope there is some way around this...

Thank you

Sub Mail_Merge21()
'
' MAIL MERGE FOR TEMPLATE 1
'

Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long
Const StrNoChr As String = """*./\:?|"
Dim MyDate
Dim Month

MyDate = Format(Date, "yyyymmdd")
Month = Format(Date, "mmmm")

    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Users\me\filename.xlsm" _
        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, _
        WritePasswordDocument:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\now\file_.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB" _
        , SQLStatement:="SELECT * FROM `Merge_1$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
       
Set MainDoc = ActiveDocument
With MainDoc
  StrFolder = "C:\Users\me\" & Month & "\"
  With .MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    On Error Resume Next
    For i = 1 To .DataSource.RecordCount
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("ID")) = "" Then Exit For
        'StrFolder = .DataFields("Folder") & "\"
        StrName = MyDate & " - " & .DataFields("File_Name")
      End With
      .Execute Pause:=False
      If Err.Number = 5631 Then
        Err.Clear
        GoTo NextRecord
      End If
      For j = 1 To Len(StrNoChr)
        StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With ActiveDocument
        '.SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=True
        .Close SaveChanges:=False
      End With
NextRecord:
    Next i
  End With
End With
Application.ScreenUpdating = True
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Sub MailMerge()
  
        'Get user who is completing document
        Dim User As String
        User = Sheets("Merge_1").Range("AC1").Value
       
        'Run Mailmerge for Sheet1
        Dim wdc
        Dim xc

        Set wdc = CreateObject("word.application")
        wdc.Application.documents.Open "C:\Users\me\" & User & "\Template1.docm"
       
        wdc.Application.Visible = False
        wdc.Application.Run "Mail_Merge21"
        Set wdc = Nothing
end sub

Edit - Sorry for the title, didn't know X was a curse!..

 

[Mike2502]

UPDATE

I set the application to visible and it does run the word documents, and I tried

 ActiveWorkbook.Saved = True

Just before the mail merge and it still doesn't work?

I have put data in my excel spreadsheet and ran the mail merge myself after saving the excel file and it works thus I'm unsure where the problem lies? ...

 

[Mike2502]

UPDATE

I have restored an old file from my recycle bin and it created a document and showed the error on the output PDF file
a field calculation error occured in record 1.

Confused asf.

 

[Macropod]

Your code is evidently based on the Send Mailmerge Output to Individual Files code in the Mailmerge Tips & Tricks thread at: Mailmerge Tips & Tricks. The code there works just fine. Have you tried repairing your Office installation?

 

[Mike2502]

Your code is evidently based on the Send Mailmerge Output to Individual Files code in the Mailmerge Tips & Tricks thread at: Mailmerge Tips & Tricks. The code there works just fine. Have you tried repairing your Office installation?

Yeah it is from there and I've repaired the word document, created a completely new one as well

It happened when the compatibility checker came up and I clicked yes..

Also as its work laptop, I do want to try avoid getting IT involved to repair my office

 

[Macropod]

I have to wonder whether the 'compatibility fix' you referred to in post 1 was something other than you thought it was. You might try using System Restore to revert your system to its previous state.

Or perhaps your mailmerge main document has acquired some form of corruption.

The field calculation error referred to in post 3 certainly a field coding fault in your backup mailmerge main document.

 

[Mike2502]

I have to wonder whether the 'compatibility fix' you referred to in post 1 was something other than you thought it was. You might try using System Restore to revert your system to its previous state.

Or perhaps your mailmerge main document has acquired some form of corruption.

The field calculation error referred to in post 3 certainly a field coding fault in your backup mailmerge main document.

Hey Macro, it's a strange one because on sheet 1 if I write data in the columns and save it, and run the mail merge manually it works... And will do bud

 

[Macropod]

on sheet 1 if I write data in the columns and save it, and run the mail merge manually it works.

I can't see how that is so, as the mailmerge SQL you posted refers to a worksheet named 'Merge_1'

 

[Mike2502]

I can't see how that is so, as the mailmerge SQL you posted refers to a worksheet named 'Merge_1'

Sorry meant merge_1 / first worksheet

 

[Mike2502]

I can't see how that is so, as the mailmerge SQL you posted refers to a worksheet named 'Merge_1'

Hi Macro,

I've always been curious on the Word Doc for mail merge what does this file need to be as this doesn't exist in my directory... Could this be causing the error and if so what document should it be?

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\now\file_.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB" _
        , SQLStatement:="SELECT * FROM `Merge_1$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess

C:\Users\now\file_.xls