Microsoft Office Forums Using VBA for Mail Merging

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-30-2019, 09:15 AM
vbaformailmerge vbaformailmerge is offline Using VBA for Mail Merging Windows 10 Using VBA for Mail Merging Office 2016
Novice
Using VBA for Mail Merging
 
Join Date: May 2019
Posts: 4
vbaformailmerge is on a distinguished road
Unhappy Using VBA for Mail Merging

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

Last edited by vbaformailmerge; 05-31-2019 at 02:18 AM.
Reply With Quote
  #2  
Old 05-30-2019, 04:54 PM
macropod's Avatar
macropod macropod is online now Using VBA for Mail Merging Windows 7 64bit Using VBA for Mail Merging Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,668
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 05-31-2019, 01:02 AM
vbaformailmerge vbaformailmerge is offline Using VBA for Mail Merging Windows 10 Using VBA for Mail Merging Office 2016
Novice
Using VBA for Mail Merging
 
Join Date: May 2019
Posts: 4
vbaformailmerge is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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
Reply With Quote
  #4  
Old 05-31-2019, 04:55 AM
macropod's Avatar
macropod macropod is online now Using VBA for Mail Merging Windows 7 64bit Using VBA for Mail Merging Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,668
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Tried using it how?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 05-31-2019, 07:23 AM
vbaformailmerge vbaformailmerge is offline Using VBA for Mail Merging Windows 10 Using VBA for Mail Merging Office 2016
Novice
Using VBA for Mail Merging
 
Join Date: May 2019
Posts: 4
vbaformailmerge is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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.
Reply With Quote
  #6  
Old 05-31-2019, 03:43 PM
macropod's Avatar
macropod macropod is online now Using VBA for Mail Merging Windows 7 64bit Using VBA for Mail Merging Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,668
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 06-03-2019, 05:10 AM
vbaformailmerge vbaformailmerge is offline Using VBA for Mail Merging Windows 10 Using VBA for Mail Merging Office 2016
Novice
Using VBA for Mail Merging
 
Join Date: May 2019
Posts: 4
vbaformailmerge is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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!
Attached Files
File Type: doc test letter.doc (29.5 KB, 4 views)
File Type: xlsx test merge.xlsx (18.8 KB, 4 views)
Reply With Quote
  #8  
Old 06-03-2019, 05:20 AM
macropod's Avatar
macropod macropod is online now Using VBA for Mail Merging Windows 7 64bit Using VBA for Mail Merging Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,668
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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 View Post
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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Tags
loop, mail merge, vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using VBA for Mail Merging 0 showing in address when mail merging Shazz Mail Merge 2 03-11-2016 01:54 AM
Using VBA for Mail Merging 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


All times are GMT -7. The time now is 01:35 PM.


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