Using VBA for Mail Merging

vbaformailmerge · #1 05-30-2019, 09:15 AM

I have been playing around with codes but I cannot figure out myself how to do this properly.

I have large databases and letter templates in which I normally would use the Finish & Merge button to merge these documents into a pdf within increments - For example;

If my database contained 3000 records I would split the records to be saved from records 1-1000 until I reach the end. I would greatly appreciate if anybody could help me with what to do? I would need a code to cycle through a loop and save the records as pdfs but in increments of a variable.

this was the code when I recorded a macro:

Code:

Sub test2()
'
' test2 Macro
'

    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = 1
            .lastRecord = .FirstRecord + 15
        End With
        .Execute Pause:=False
    End With
    ActiveDocument.ExportAsFixedFormat OutputFileName:= _
        "1-15 test.pdf" _
        , 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
    ChangeFileOpenDirectory _
        "filedirectory"
    Windows("filename.doc").Activate
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = 15
            .lastRecord = .FirstRecord + 15
        End With
        .Execute Pause:=False
    End With
    ActiveDocument.ExportAsFixedFormat OutputFileName:= _
        "15-29 records.pdf" _
        , 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
    ChangeFileOpenDirectory _
        "file directory"
    Windows("file.doc").Activate
End Sub
Option Explicit

(I have changed the filename locations to random ones to hide protected data)

I have been using it with other code I found on this forum so I am unsure how to put this into a loop?

If anyone knows of a way to do this or any tips/code I could work off of, I would be eternally grateful

macropod · #2 05-30-2019, 04:54 PM

See Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum: https://www.msofficeforums.com/mail-...ps-tricks.html
The code there outputs every record as a separate file. It could be adapted, though, to group records if that's what you really want.

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.

vbaformailmerge · #3 05-31-2019, 01:02 AM

Quote:

Originally Posted by macropod

See Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum: https://www.msofficeforums.com/mail-...ps-tricks.html
The code there outputs every record as a separate file. It could be adapted, though, to group records if that's what you really want.

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.

I tried using this code but nothing happens? I changed the folder name and made sure it was set to .doc too. Can't seem to figure out why it is not working

macropod · #4 05-31-2019, 04:55 AM

Tried using it how?

vbaformailmerge · #5 05-31-2019, 07:23 AM

Quote:

Originally Posted by macropod

Tried using it how?

I put your code into a new macro and edited the variables to suit my data etc, but when I click run nothing happens, not sure why, so I don't know how to make it run correctly.

macropod · #6 05-31-2019, 03:43 PM

Without actually seeing the problem mailmerge main document and some sample data, its impossible to diagnose the issue. Can you attach the document & sample data to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

vbaformailmerge · #7 06-03-2019, 05:10 AM

Quote:

Originally Posted by macropod

Without actually seeing the problem mailmerge main document and some sample data, its impossible to diagnose the issue. Can you attach the document & sample data to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

Oh, I'm so sorry - of course ill link it for you.

So for example, I will be using the main doc and the database for merging so my fake database contains 30 records which I would like to merge and save as pdf for every 5 records together in one document.

So I should end up with 6 pdfs in total with 5 records each. If that makes sense! I was playing around with a macro but I turned on the comp today and its vanished!

any idea why it disappeared? Thank you so much!

macropod · #8 06-03-2019, 05:20 AM

Your mailmerge main document is trying to link to a CSV file, not to your Excel file.

There is also no VBA code in your mailmerge main document.

Hardly surprising, therefore, that it doesn't 'work'.

Quote:

Originally Posted by vbaformailmerge

I was playing around with a macro but I turned on the comp today and its vanished!

any idea why it disappeared?

Evidently, you've either deleted the macro or it was never in the document - perhaps it was in a Word template that you closed without saving changes.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
0 showing in address when mail merging	Shazz	Mail Merge	2	03-11-2016 01:54 AM
Merging, but not with mail	ksims3	Word	1	07-29-2014 08:27 PM
How would you go about mail merging this set of data	willsonfang	Mail Merge	1	09-29-2013 07:16 PM
Gaps in address mail merging	Trixz_D	Mail Merge	2	08-05-2012 04:23 AM
Mail Merging more than 1 line of data ??	Keith24249	Mail Merge	0	10-16-2009 06:36 AM