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!..

 

[Macropod]

The references to 'C:\Users\now\file_.xlsm' and 'C:\Users\me\filename.xlsm' should be the same - and would have been if you hadn't messed with that part of the code you got from the link.

 

[Mike2502]

The references to 'C:\Users\now\file_.xlsm' and 'C:\Users\me\filename.xlsm' should be the same - and would have been if you hadn't messed with that part of the code you got from the link.

I tried to use
Run a Mailmerge from Excel, Sending the Output to Individual Files

But I couldn't get it to work thus I copied this code from someone on a different site...

 

[Macropod]

The code in the link works just fine... provided the instructions are followed and it isn't messed with.

 

[Mike2502]

The code in the link works just fine... provided the instructions are followed and it isn't messed with.

Sub 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 = """*./\:?|"
Set MainDoc = ActiveDocument
Dim MyDate
Dim Month

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

With MainDoc
  StrFolder = "C:\me\file"
  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
      On Error GoTo NextRecord
      .Execute Pause:=False
      For j = 1 To Len(StrNoChr)
        StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With ActiveDocument
        .SaveAs FileName:=StrFolder & Month & "\" & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
      End With
NextRecord:
      If Err.Num = 5631 Then Err.Clear
    Next i
  End With
End With
Application.ScreenUpdating = True
End Sub

I get an error on
'.Destination = wdSendToNewDocument'

Requested object is not available,

Also on my word document when I try to link it to my excel spreadsheet it shows an error that im unable to access it or it is opened .. I need the excel sheet open to run the word macro if that makes sense

I'm having to revamp the cod from scratch, hope you can help me.

 

[Macropod]

I get an error on
'.Destination = wdSendToNewDocument'

Requested object is not available

Let me guess - you're trying to run the code in a document that is not a mailmerge main document...

Also on my word document when I try to link it to my excel spreadsheet it shows an error that im unable to access it or it is opened .. I need the excel sheet open to run the word macro if that makes sense

No, that doesn't make sense. There is no need whatsoever for the data source to be open. Of course, given that you're evidently trying to run the macro from a document that isn't a mailmerge main document, who knows what other problems you've created for yourself...

 

[Mike2502]

Let me guess - you're trying to run the code in a document that is not a mailmerge main document...

No, that doesn't make sense. There is no need whatsoever for the data source to be open. Of course, given that you're evidently trying to run the macro from a document that isn't a mailmerge main document, who knows what other problems you've created for yourself...

Could you explain this pls that is what I'm here for... What do you mean by mail merge main document?

 

[Macropod]

Mailmerge 101: https://support.microsoft.com/en-gb...e-feature-in-word-to-create-and-to-print-form