#1
|
|||
|
|||
Mail Merge simply not working...
Hi,
I'm pretty new to VBA so I can imagine what I'm doing wrong will be a simple fix but I just seem incapable of figuring it out. I have tried using Macropod's code when he was answering a similar question but that does not seem to work either in this scenario (https://www.msofficeforums.com/mail-...html#post61731). What I am trying to accomplish is when a button is clicked (on a separate excel document purely used to hold the macro), Excel will open the relevant document and make the necessary changes to ensure the formatting is pulled through correctly during the Mail Merge, this is encompassed in Call Adding_Columns. This works as expected. Next, the macro will open Word and link through to that newly updated excel spreadsheet. When I run the code below I get 1 of 2 issues, either Word stops responding and I have to force quit, or a new .xls file is created 1 folder above where the data set is stored, I then get a SQL box asking me to select the table in the new .xls document with no options to change the document. No matter what I've tried I can't seem to get around this issue so would really appreciate some help! Code below: Code:
Sub TESTING_W_WORD() Const strPESREPORT As String = "C:\Users\OllieR\OneDrive\1 - Business Support\Ollie Testing PEHS Macros\ADHB PES Report_Pilot.xlsx" Const strMailMerge As String = "C:\Users\OllieR\OneDrive\1 - Business Support\Ollie Testing PEHS Macros\Mail Merge Draft 1.docx" Dim openword As Word.Application Dim wddoc As Word.Document Call Adding_Columns On Error GoTo CreateObj Set openword = GetObject(, "Word.Application") GoTo gotApp CreateObj: Set openword = CreateObject("Word.Application") gotApp: On Error GoTo 0 openword.Visible = True With openword .DisplayAlerts = wdAlertsNone .Documents.Open (strMailMerge) With ActiveDocument.MailMerge .MainDocumentType = NotAMergeDocument .OpenDataSource Name:=strPESREPORT, Format:=wdOpenFormatDocument, linktosource:=True, Revert:=False, _ Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=strPESREPORT;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _ SQLStatement:="SELECT * FROM 'Sheet1'" .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With openword.DisplayAlerts = wdAlertsAll End With Windows("ADHB PES Report_Pilot").Close End Sub Adding Columns code (which works as expected) Code:
Sub Adding_Columns() Const strPESREPORT As String = "C:\Users\OllieR\OneDrive\1 - Business Support\Ollie Testing PEHS Macros\ADHB PES Report_Pilot.xlsx" Workbooks.Open (strPESREPORT) Windows("ADHB PES Report_Pilot.xlsx").Activate Application.DisplayAlerts = xlAlertsNone Range("F1").EntireColumn.Insert Range("F3").Value = "DOB" Range("F4").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",TEXT(RC[-1],""dd/MM/yyyy""))" Range("F4").Select Selection.AutoFill Destination:=Range("F4:F5000"), Type:=xlFillDefault Range("AJ:AJ").EntireColumn.Insert Range("AJ3").Value = "Start Date" Range("AJ4").FormulaR1C1 = "=IF(RC[-1]="""","""",TEXT(RC[-1],""dd/MM/yyyy""))" Range("AJ4").Select Selection.AutoFill Destination:=Range("AJ4:AJ5000"), Type:=xlFillDefault ActiveWorkbook.Save Application.DisplayAlerts = xlAlertsAll End Sub Last edited by Ollier; 11-22-2023 at 08:29 PM. Reason: adjusting so code is correctly formatted, added screenshot of error (changed file location off onedrive to attempt to solve) |
Tags |
macro error, mailmerge, mailmerge automate |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mail Merge Output to Different Files not working | Rachquit | Mail Merge | 12 | 04-27-2023 08:25 PM |
Find Recipient not working in Mail Merge | businessteachertz | Mail Merge | 0 | 08-23-2019 08:19 AM |
Mail Merge date not working | Dave1816uk | Mail Merge | 4 | 03-05-2015 07:40 AM |
Mail merge to html not working | CRMCathy | Mail Merge | 2 | 02-17-2015 07:59 PM |
Merge mail not working | jdpowell@brazoriainet.com | Mail Merge | 1 | 07-25-2008 07:51 PM |