Consulting

Results 1 to 3 of 3

Thread: Word VBA code for Mail Merge export

  1. #1
    VBAX Newbie
    Joined
    Jul 2021
    Posts
    2
    Location

    Unhappy Word VBA code for Mail Merge export

    Hello, I am new to Word VBA and am having trouble with a mail merge code.
    Everything works great on the code with the exception of where the files are being saved/exported to.

    for this line: Const FOLDER_SAVED As String = "\\DAVINCI-1\USERDATA\carla\Desktop\Test"
    No matter what directory I input it always automatically saves to My documents on the C drive.
    Any help with this would be greatly appreciated!


    Option Explicit
    Const FOLDER_SAVED As String = "\\DAVINCI-1\USERDATA\carla\Desktop\Test\"
    Const SOURCE_FILE_PATH As String = "\\DAVINCI-1\USERDATA\carla\Desktop\Test\Security Assessment - Positive Result.docx"
    Sub TestRun()
    Dim MainDoc As Document, TargetDoc As Document
    Dim dbPath As String
    Dim recordNumber As Long, totalRecord As Long
    
    Set MainDoc = ActiveDocument
    With MainDoc.MailMerge            
    
    
    
            
    .OpenDataSource Name:="\\DAVINCI-1\USERDATA\carla\Desktop\Test\Internal Controlled Goods Security Assesment.xlsx", sqlstatement:="SELECT * FROM [Letter List$]"
     
     totalRecord = .DataSource.RecordCount
     For recordNumber = 1 To totalRecord
    With .DataSource 
    .ActiveRecord = recordNumber
    .FirstRecord = recordNumber
    .LastRecord = recordNumber
    End With
    .Destination = wdSendToNewDocument
    .Execute False
    Set TargetDoc = ActiveDocument
    TargetDoc.ExportAsFixedFormat OutputFileName:=FOLDER_SAVED & .DataSource.DataFields("First_Name").Value & "_" & .DataSource.DataFields("Last_Name").Value & ".pdf", exportformat:=wdExportFormatPDF                        
    TargetDoc.Close False                        
    Set TargetDoc = Nothing                            
    Next recordNumber
    End With
    Set MainDoc = Nothing
    End Sub

  2. #2
    Try the following instead:
    Option Explicit
    'Graham Mayor - https://www.gmayor.com - Last updated - 16 Jul 2021 
    Sub TestRun()
    Dim FOLDER_SAVED As String
    Dim SOURCE_FILE_PATH As String
    Dim MainDoc As Document, TargetDoc As Document
    Dim dbPath As String
    Dim recordNumber As Long, totalRecord As Long
    Dim FSO As Object
    
        FOLDER_SAVED = Environ("USERPROFILE") & "\Desktop\Test\"
        SOURCE_FILE_PATH = FOLDER_SAVED & "Security Assessment - Positive Result.docx"
    
        Set FSO = CreateObject("Scripting.FileSystemObject")
        If FSO.FolderExists(FOLDER_SAVED) = False Then
            MsgBox "The folder '" & FOLDER_SAVED & "' does not exist!", vbCritical
            Set FSO = Nothing
            Exit Sub
        End If
    
        Set MainDoc = ActiveDocument
        With MainDoc.MailMerge
    
            .OpenDataSource _
                    Name:=FOLDER_SAVED & "Internal Controlled Goods Security Assesment.xlsx", _
                    sqlstatement:="SELECT * FROM [Letter List$]"
    
            totalRecord = .DataSource.RecordCount
            For recordNumber = 1 To totalRecord
                With .DataSource
                    .ActiveRecord = recordNumber
                    .FirstRecord = recordNumber
                    .LastRecord = recordNumber
                End With
                .Destination = wdSendToNewDocument
                .Execute False
                Set TargetDoc = ActiveDocument
                TargetDoc.ExportAsFixedFormat _
                        OutputFileName:=FOLDER_SAVED & _
                                        .DataSource.DataFields("First_Name").value & "_" & _
                                        .DataSource.DataFields("Last_Name").value & ".pdf", _
                                        exportformat:=wdExportFormatPDF
                TargetDoc.Close False
                Set TargetDoc = Nothing
            Next recordNumber
        End With
        Set MainDoc = Nothing
    End Sub
    See also https://www.gmayor.com/MergeAndSplit.htm
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Newbie
    Joined
    Jul 2021
    Posts
    2
    Location

    Red face

    Quote Originally Posted by gmayor View Post
    Try the following instead:
    Option Explicit
    'Graham Mayor - https://www.gmayor.com - Last updated - 16 Jul 2021 
    Sub TestRun()
    Dim FOLDER_SAVED As String
    Dim SOURCE_FILE_PATH As String
    Dim MainDoc As Document, TargetDoc As Document
    Dim dbPath As String
    Dim recordNumber As Long, totalRecord As Long
    Dim FSO As Object
    
        FOLDER_SAVED = Environ("USERPROFILE") & "\Desktop\Test\"
        SOURCE_FILE_PATH = FOLDER_SAVED & "Security Assessment - Positive Result.docx"
    
        Set FSO = CreateObject("Scripting.FileSystemObject")
        If FSO.FolderExists(FOLDER_SAVED) = False Then
            MsgBox "The folder '" & FOLDER_SAVED & "' does not exist!", vbCritical
            Set FSO = Nothing
            Exit Sub
        End If
    
        Set MainDoc = ActiveDocument
        With MainDoc.MailMerge
    
            .OpenDataSource _
                    Name:=FOLDER_SAVED & "Internal Controlled Goods Security Assesment.xlsx", _
                    sqlstatement:="SELECT * FROM [Letter List$]"
    
            totalRecord = .DataSource.RecordCount
            For recordNumber = 1 To totalRecord
                With .DataSource
                    .ActiveRecord = recordNumber
                    .FirstRecord = recordNumber
                    .LastRecord = recordNumber
                End With
                .Destination = wdSendToNewDocument
                .Execute False
                Set TargetDoc = ActiveDocument
                TargetDoc.ExportAsFixedFormat _
                        OutputFileName:=FOLDER_SAVED & _
                                        .DataSource.DataFields("First_Name").value & "_" & _
                                        .DataSource.DataFields("Last_Name").value & ".pdf", _
                                        exportformat:=wdExportFormatPDF
                TargetDoc.Close False
                Set TargetDoc = Nothing
            Next recordNumber
        End With
        Set MainDoc = Nothing
    End Sub
    See also https://www.gmayor.com/MergeAndSplit.htm

    Thank you very much! I made some modifications because I intended to move the files to a shared drive but still worked perfectly!

    Thank you again!

Tags for this Thread

Posting Permissions

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