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

Macropod

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

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Mike2502

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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,528
The code in the link works just fine... provided the instructions are followed and it isn't messed with.
 

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010
The code in the link works just fine... provided the instructions are followed and it isn't messed with.

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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,528

ADVERTISEMENT

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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,401
Messages
5,624,520
Members
416,032
Latest member
dunhamsasphalt

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