Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-22-2023, 06:51 PM
Ollier Ollier is offline Mail Merge simply not working... Windows 10 Mail Merge simply not working... Office 2021
Novice
Mail Merge simply not working...
 
Join Date: Nov 2023
Posts: 1
Ollier is on a distinguished road
Default 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
Attached Images
File Type: jpg MailMerge SQL Prompt.jpg (39.3 KB, 11 views)

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)
Reply With Quote
Reply

Tags
macro error, mailmerge, mailmerge automate

Thread Tools
Display Modes


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 simply not working... 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft