VBA Fill in Word Template and save as PDF

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
69
I'm looking for some help tweaking this code. I have data in an excel sheet and a Word template. It will fill in the template but when it goes to save it pops up and I have to manually save it. I want it to save and move on to the next line. Any help would be appreciated. The code I have so far is below.
Code:
Sub CreateWordDocuments()
Dim CustRow, CustCol, LastRow, TemplRow, DaysSince, FrDays, ToDays As Long
Dim DocLoc, TagName, TagValue, TemplName, FileName As String
Dim CurDt, LastAppDt As Date
Dim WordDoc, WordApp, OutApp, OutMail As Object
Dim WordContent As Word.Range
With Sheet1
  
  If .Range("B3").Value = Empty Then
    MsgBox "Please select a correct template from the drop down list"
    .Range("G3").Select
    Exit Sub
  End If
    TemplRow = .Range("B3").Value 'Set Template Row
    TemplName = .Range("G3").Value 'Set Template Name
    FrDays = .Range("L3").Value 'Set From Days
    ToDays = .Range("O3").Value 'Set To Days
    DocLoc = Sheet2.Range("F" & TemplRow).Value 'Word Document Filename
    
    'Open Word Template
    On Error Resume Next 'If Word is already running
    Set WordApp = GetObject("Word.Application")
    If Err.Number <> 0 Then
    'Launch a new instance of Word
    Err.Clear
    'On Error GoTo Error_Handler
    Set WordApp = CreateObject("Word.Application")
    WordApp.Visible = True 'Make the application visible to the user
    End If
    
    
    LastRow = .Range("E9999").End(xlUp).Row  'Determine Last Row in Table
        For CustRow = 8 To LastRow
                DaysSince = .Range("o" & CustRow).Value
                If TemplName <> .Range("O" & CustRow).Value And DaysSince >= FrDays And DaysSince <= ToDays Then
                                Set WordDoc = WordApp.Documents.Open(FileName:=DocLoc, ReadOnly:=False) 'Open Template
                                For CustCol = 3 To 13 'Move Through 9 Columns
                                    TagName = .Cells(7, CustCol).Value 'Tag Name
                                    TagValue = .Cells(CustRow, CustCol).Value 'Tag Value
                                     With WordDoc.Content.Find
                                        .Text = TagName
                                        .Replacement.Text = TagValue
                                        .Wrap = wdFindContinue
                                        .Execute Replace:=wdReplaceAll 'Find & Replace all instances
                                     End With
                                Next CustCol
                        
                        If .Range("I3").Value = "PDF" Then
                                       FileName = ThisWorkbook.Path & "\" & .Range("E" & CustRow).Value & "_" & .Range("F" & CustRow).Value & ".pdf" 'Create full filename & Path with current workbook location, Last Name & First Name
                                       WordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF
                                       WordDoc.Close False
                                   Else: 'If Word
                                       FileName = ThisWorkbook.Path & "\" & .Range("E" & CustRow).Value & "_" & .Range("F" & CustRow).Value & ".docx"
                                       WordDoc.SaveAs FileName
                                   End If
                                   .Range("o" & CustRow).Value = TemplName 'Template Name
                                   .Range("P" & CustRow).Value = Now
                                    If .Range("Q3").Value = "Email" Then
                                                  Set OutApp = CreateObject("Outlook.Application") 'Create Outlook Application
                                                  Set OutMail = OutApp.CreateItem(0) 'Create Email
                                                  With OutMail
                                                      .To = Sheet1.Range("K" & CustRow).Value
                                                      .Subject = "Hi, " & Sheet1.Range("F" & CustRow).Value & " We Miss You"
                                                      .Body = "Hello, " & Sheet1.Range("F" & CustRow).Value & " Its been a while since we have seen you so we wanted to send you a special letter. Please see the attached file"
                                                      .Attachments.Add FileName
                                                      .Display 'To send without Displaying change .Display to .Send
                                                  End With
                                    Else: 'Print Out
                                           
                                           WordDoc.Save
                                           WordDoc.Close
                                    End If
                        Kill (FileName) 'Deletes the PDF or Word that was just created
            End If '3 condition met
        Next CustRow
        WordApp.Quit
End With
End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,485
U can trial this line of code before the save...
Code:
WordApp.DisplayAlerts = False
HTH. Dave
 

KeithGargett

New Member
Joined
Sep 2, 2019
Messages
6
I have a similar Code within an excel workbook.

Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ActiveSheet.ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Filename:=ActiveWorkbook.FullName & " - " & TheDate & " - " & TheAssemblyNumber & "-" & TheAssemblyName & ".pdf", _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False[/FONT]
[Code\]
I think the trick is OpenAfterPublish

[QUOTE="floggingmolly, post: 5360047, member: 451874"]I'm looking for some help tweaking this code. I have data in an excel sheet and a Word template. It will fill in the template but when it goes to save it pops up and I have to manually save it.  I want it to save and move on to the next line.  Any help would be appreciated.  The code I have so far is below.
[code=rich]
Sub CreateWordDocuments()
Dim CustRow, CustCol, LastRow, TemplRow, DaysSince, FrDays, ToDays As Long
Dim DocLoc, TagName, TagValue, TemplName, FileName As String
Dim CurDt, LastAppDt As Date
Dim WordDoc, WordApp, OutApp, OutMail As Object
Dim WordContent As Word.Range
With Sheet1
  
  If .Range("B3").Value = Empty Then
    MsgBox "Please select a correct template from the drop down list"
    .Range("G3").Select
    Exit Sub
  End If
    TemplRow = .Range("B3").Value 'Set Template Row
    TemplName = .Range("G3").Value 'Set Template Name
    FrDays = .Range("L3").Value 'Set From Days
    ToDays = .Range("O3").Value 'Set To Days
    DocLoc = Sheet2.Range("F" & TemplRow).Value 'Word Document Filename
    
    'Open Word Template
    On Error Resume Next 'If Word is already running
    Set WordApp = GetObject("Word.Application")
    If Err.Number <> 0 Then
    'Launch a new instance of Word
    Err.Clear
    'On Error GoTo Error_Handler
    Set WordApp = CreateObject("Word.Application")
    WordApp.Visible = True 'Make the application visible to the user
    End If
    
    
    LastRow = .Range("E9999").End(xlUp).Row  'Determine Last Row in Table
        For CustRow = 8 To LastRow
                DaysSince = .Range("o" & CustRow).Value
                If TemplName <> .Range("O" & CustRow).Value And DaysSince >= FrDays And DaysSince <= ToDays Then
                                Set WordDoc = WordApp.Documents.Open(FileName:=DocLoc, ReadOnly:=False) 'Open Template
                                For CustCol = 3 To 13 'Move Through 9 Columns
                                    TagName = .Cells(7, CustCol).Value 'Tag Name
                                    TagValue = .Cells(CustRow, CustCol).Value 'Tag Value
                                     With WordDoc.Content.Find
                                        .Text = TagName
                                        .Replacement.Text = TagValue
                                        .Wrap = wdFindContinue
                                        .Execute Replace:=wdReplaceAll 'Find & Replace all instances
                                     End With
                                Next CustCol
                        
                        If .Range("I3").Value = "PDF" Then
                                       FileName = ThisWorkbook.Path & "\" & .Range("E" & CustRow).Value & "_" & .Range("F" & CustRow).Value & ".pdf" 'Create full filename & Path with current workbook location, Last Name & First Name
                                       WordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF
                                       WordDoc.Close False
                                   Else: 'If Word
                                       FileName = ThisWorkbook.Path & "\" & .Range("E" & CustRow).Value & "_" & .Range("F" & CustRow).Value & ".docx"
                                       WordDoc.SaveAs FileName
                                   End If
                                   .Range("o" & CustRow).Value = TemplName 'Template Name
                                   .Range("P" & CustRow).Value = Now
                                    If .Range("Q3").Value = "Email" Then
                                                  Set OutApp = CreateObject("Outlook.Application") 'Create Outlook Application
                                                  Set OutMail = OutApp.CreateItem(0) 'Create Email
                                                  With OutMail
                                                      .To = Sheet1.Range("K" & CustRow).Value
                                                      .Subject = "Hi, " & Sheet1.Range("F" & CustRow).Value & " We Miss You"
                                                      .Body = "Hello, " & Sheet1.Range("F" & CustRow).Value & " Its been a while since we have seen you so we wanted to send you a special letter. Please see the attached file"
                                                      .Attachments.Add FileName
                                                      .Display 'To send without Displaying change .Display to .Send
                                                  End With
                                    Else: 'Print Out
                                           
                                           WordDoc.Save
                                           WordDoc.Close
                                    End If
                        Kill (FileName) 'Deletes the PDF or Word that was just created
            End If '3 condition met
        Next CustRow
        WordApp.Quit
End With
End Sub
[/QUOTE]
 

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
69
I have a similar Code within an excel workbook.

Code:
[FONT=Verdana]ActiveSheet.ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Filename:=ActiveWorkbook.FullName & " - " & TheDate & " - " & TheAssemblyNumber & "-" & TheAssemblyName & ".pdf", _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False[/FONT]
[Code\]
I think the trick is OpenAfterPublish[/QUOTE]


If I put the OpenAfterPublish line it gives an error. I've been fighting with this for days now. I can't get it to work.
 

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
69
When it goes to save the PDF the box comes up to say where to save it and I have to click SAVE. I can't get it to just save without having to click the SAVE button
 

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
69
I tried this line of code and its still showing the save dialog box and I have to click SAVE.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,265
Messages
5,485,758
Members
407,512
Latest member
PearceK

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top