Mail Merge ****up

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010
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(y)

VBA Code:
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

VBA Code:
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!..
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010
UPDATE

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

VBA Code:
 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

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010
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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,514
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

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010

ADVERTISEMENT

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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,514
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

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010

ADVERTISEMENT

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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,514
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

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010
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?

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,263
Messages
5,600,596
Members
414,391
Latest member
Pandurang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top