+ Reply to Thread
Results 1 to 3 of 3

VBA Excel to Word Breaking, Paste Not Working

Hybrid View

  1. #1
    Registered User
    Join Date
    02-10-2020
    Location
    Manchester, England
    MS-Off Ver
    365
    Posts
    19

    Question VBA Excel to Word Breaking, Paste Not Working

    Good Afternoon All

    I'm pulling my hair out with an issue I have with an Excel to Word macro that populates a .dotx Word Template by copying the cell in Excel and going to the bookmark in Word to paste it, then saves it as a PDF, then creates an email with the PDF attached. The code I have previously used had worked fine up until last week when the raw data behind the tool changed and I had to do some messing about to reformat it all and add some columns in - I doubt this contributed towards it although its the only thing that has changed.

    Now when I try to run the code, it gets to the .Selection.Paste line and then Word drops a 5097 error.
    My issue is that when I restart the PC and try it, it will work for the first instance and paste it into the first bookmark, but drops the error at the second bookmark.
    After that, it drops the 5097 at the first bookmark and a full restart is needed to get it to start again - so I think there is something across the applications that is breaking.
    The rest of the code works fine so I'm at a loss as to what the problem is. Also I'm very amateur at this, although I did get it to work previously.

    I've taken a lot of the fluff out of the code (like Usernames etc) and email body text, but I can't provide the Workbook as data that runs the frontsheet that populates this data is all sensitive information. I've attached an edited screenshot of the front page that it runs off so you can see that it pulls the data from A9 as a starting point then moves cells right to get their details for the Word attachment, then when its done it uses the I9 to choose the Word template to edit. The rest of the code works perfectly but I'm unsure if there is some Application issue across Office that is breaking when I run it first time.
    Is there something glaringly obvious that I've missed up to the .Selection.Paste line that is causing an issue?
    I did use the PasteSpecial previously and that worked but now it doesn't.
    Willing to buy a pint for anyone that can help me get this running...

    Sub FullProcess_ExportPDF_CreateEmail()
    
    Dim wdApp As Word.Application
    Dim SaveAsName As String
    Set wdApp = New Word.Application
    Dim SaveFolderFile As String
    Dim MerchantEmail As String
    Dim WordTemplate As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim OutputFileFolder As String
    
    ' Start Main Loop
    
    Dim x As Integer
    
    NumRows = Range("A9", Range("A9").End(xlDown)).Rows.Count
    Range("A9").Select
    For x = 1 To NumRows
    
    '############################### Create PDF File From Excel Data Code ###############################
    
        WordTemplate = ActiveCell.Offset(0, 8).Value
    
    With wdApp
        .ScreenUpdating = True
        .Visible = True
        .Activate
        'Open Merchant Contact Template
        .Documents.Add "C:\Users\EXXXXX\OneDrive\Desktop\CDD Merchant Contact Solution\" & WordTemplate
        
            'Copy Address in Column D, Row X, paste into Template
            Selection.Offset(0, 3).Copy
            .Selection.GoTo What:=wdGoToBookmark, Name:="MerchantAddress"
                .Selection.Paste
    
            'Copy Address in Column E, Row X, paste into Template
            Selection.Offset(0, 4).Copy
            .Selection.GoTo What:=wdGoToBookmark, Name:="MerchantPO1"
                .Selection.Paste
        
        'Define SaveName for PDF filename
        SaveAsName = ActiveCell.Offset(0, 0).Value
        MerchantEmail = ActiveCell.Offset(0, 4).Value
    
        'For PDF Export, use the below
        SaveFolderFile = "C:\Users\EXXXXX\OneDrive\Desktop\CDD Merchant Contact Solution\" & SaveAsName & ".pdf"
        .ActiveDocument.ExportAsFixedFormat OutputFileName:=SaveFolderFile, _
            ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
            wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
            Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
            CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
            BitmapMissingFonts:=True, UseISO19005_1:=False
    
        'Closes Word Template, without Saving Changes
        .ScreenUpdating = True
        .ActiveDocument.Close SaveChanges:=False
        '.Quit
    
    End With
    
    '############################### Create Email to Send and Attach PDF Code ###############################
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
        OutputFileFolder = "C:\Users\EXXXXX\OneDrive \Desktop\CDD Merchant Contact Solution\"
                
    On Error Resume Next
        
        With OutMail
            .SentOnBehalfOfName = "emailaddress@company.com"
            .To = MerchantEmail
            .Subject = "Customer Due Diligence - " & ActiveCell.Value & " - " & ActiveCell.Offset(0, 2).Value & " - (SR-REM)"
    
        'If this is the merchants 1st letter use the below email body
            If ActiveCell.Offset(0, 1).Value = 1 Then _
            .HTMLBody = _
                "Version 1 - Good Morning Sirs - Full Email Body to Follow"
    
        'If this is the merchants 2nd letter use the below email body
            If ActiveCell.Offset(0, 1).Value = 2 Then _
            .HTMLBody = _
                "Version 2 - Good Morning Sirs - Full Email Body to Follow"
    
        'If this is the merchants 3rd letter use the below email body
            If ActiveCell.Offset(0, 1).Value = 3 Then _
            .HTMLBody = _
                "Version 3 - Good Morning Sirs - Full Email Body to Follow"
    
        .Attachments.Add OutputFileFolder & SaveAsName & ".pdf"
        .Display 'Use .Display to not automatically send, .Send to send immediately
        
        End With
    
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
        
    '############################### Loop Code Stops Here ###############################
        
        Workbooks("CDD Merchant Mailer.xlsm").Activate
        ActiveCell.Offset(1, 0).Select
    Next
    
    End Sub
    Attached Images Attached Images
    Last edited by ChristovJN; 07-19-2022 at 11:06 AM.

  2. #2
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2503
    Posts
    1,459

    Re: VBA Excel to Word Breaking, Paste Not Working

    It may be that the clipboard is still being filled while it is being pasted at the same time. You could try replacing this:
    .Selection.Paste
    With:
    Do
        On Error Resume Next
        .Selection.Paste
        If Err.Number = 0 Then Exit Do
        DoEvents
    Loop
    On Error GoTo 0
    This will make it repeat the paste until there are no errors - worth a shot.

    It is worth including the line:
    Application.CutCopyMode = False
    after each paste event to keep the clipboard empty - a full clipboard could be half the issue, hence why a restart may be emptying the clipboard.
    If things don't change they stay the same

  3. #3
    Registered User
    Join Date
    02-10-2020
    Location
    Manchester, England
    MS-Off Ver
    365
    Posts
    19

    Re: VBA Excel to Word Breaking, Paste Not Working

    Thanks very much - I've somehow managed to get around it by defining the repeating data to be copied/pasted and replaced it with .Selection.TypeText MerchantAddressText which seems to be working really well, however I'm going to put the Clipboard clearing you've shown at the end to keep it clean. Much appreciated!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 07-27-2020, 10:57 AM
  2. excel to paste to word then attach word document to an email
    By ahalliwell in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-30-2017, 12:44 PM
  3. Replies: 3
    Last Post: 05-13-2016, 08:08 PM
  4. Replies: 0
    Last Post: 07-06-2014, 06:38 PM
  5. Replies: 1
    Last Post: 10-01-2012, 11:11 PM
  6. Replies: 1
    Last Post: 01-24-2011, 03:22 PM
  7. Replies: 1
    Last Post: 05-14-2009, 11:01 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1