Using VBA to trigger a Mail Merge

RMcCreavy
Lounger
Posts: 30
Joined: 02 Nov 2018, 16:12

Using VBA to trigger a Mail Merge

Post by RMcCreavy »

Hi,

I am attempting to automate some relatively simple document generation tasks using SharePoint, Excel, Word and Outlook. The idea is to have users fill out a SharePoint list where some fields will auto populate depending on their initial inputs. Then I am populating Word documents as they would with a mail merge that pulls the SharePoint fields using a linked Excel table (via an .iqy query). Finally, I was hoping to use VBA to trigger the mail merge and send out the finished product to various email addresses.

I'm having trouble with the last step after pulling some code from various google searches. Unfortunately I'm not at all familiar with how VBA calls MS Word or Outlook. Although I have the fields mapped from the Excel table correctly in my Word template, and my code does populate the template, I'm unsure how this code is using Constants and I have no idea how to trigger the email.

This is what I have so far:

Code: Select all

Option Explicit
Dim wd As Object, wdocSource As Object, strWorkbookName As String, SPViewQuery As Excel.Workbook, wdSendToEmail As String

Sub RunMerge()
    Application.ScreenUpdating = False
    Const wdFormLetters = 0, wdOpenFormatAuto = 0
    Const wdSendToNewDocument = 0, wdDefaultFirstRecord = 1, wdDefaultLastRecord = -16

    On Error Resume Next
    Set wd = GetObject(, "Word.Application")
    If wd Is Nothing Then
        Set wd = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set wdocSource = wd.Documents.Open("A:\Document Generator\Templates\Template 1.docx")
    
    Set SPViewQuery = Excel.Workbooks.Open("A:\Document Generator\Doc Creation SharePoint Query.xlsx")
    SPViewQuery.Activate
    strWorkbookName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
    
  
    wdocSource.MailMerge.MainDocumentType = wdFormLetters

    wdocSource.MailMerge.OpenDataSource _
            Name:=strWorkbookName, _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=wdOpenFormatAuto, _
            Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
            SQLStatement:="SELECT * FROM `query$`"

    With wdocSource.MailMerge
        .Destination = wdSendToNewDocument
        .MailAddressFieldName = "Test@gmail.com"
        .MailFormat = wdMailFormatHTML
        .MailSubject = "Doc Test"
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With

    wd.Visible = True
    wdocSource.Close SaveChanges:=False

    Set wdocSource = Nothing
    Set wd = Nothing

    Application.ScreenUpdating = True
End Sub
Eventually, I'd like to have this loop through and send a single document to the mail address in Column L of the Excel template, but right now I can't get it to send output to my own email address and I'm getting a bit lost in various threads I find on google that don't quite describe the same situation.

Any help towards my understanding of how VBA could execute this task is greatly appreciated!

User avatar
HansV
Administrator
Posts: 69403
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using VBA to trigger a Mail Merge

Post by HansV »

I haven't tested it, but does this work? I removed opening the workbook, that isn't necessary for mail merge.

Code: Select all

Sub RunMerge()
    Dim wd As Object ' Word.Application
    Dim wdocSource As Object ' Word.Document
    Dim strWorkbookName As String

    On Error Resume Next
    Set wd = GetObject(Class:="Word.Application")
    If wd Is Nothing Then
        Set wd = CreateObject(Class:="Word.Application")
    End If
    On Error GoTo 0

    Set wdocSource = wd.Documents.Open("A:\Document Generator\Templates\Template 1.docx")

    strWorkbookName = "A:\Document Generator\Doc Creation SharePoint Query.xlsx"

    wdocSource.MailMerge.MainDocumentType = 4 ' wdEMail

    With wdocSource.MailMerge
        .OpenDataSource _
            Name:=strWorkbookName, _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=0, _
            Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
            SQLStatement:="SELECT * FROM `query$`"
        .Destination = 2 ' wdSendToEmail
        .MailAddressFieldName = "Test@gmail.com"
        .MailFormat = 1 ' wdMailFormatHTML
        .MailSubject = "Doc Test"
        .SuppressBlankLines = True
        .Execute Pause:=False
    End With

    wdocSource.Close SaveChanges:=False

    Set wdocSource = Nothing
    Set wd = Nothing
End Sub
Regards,
Hans

RMcCreavy
Lounger
Posts: 30
Joined: 02 Nov 2018, 16:12

Re: Using VBA to trigger a Mail Merge

Post by RMcCreavy »

Hi Hans,

I just got around to trying this and it unfortunately it did not work, although I think I'm one step away.

The error message is
"Run-time error ‘5630’: excel cannot merge documents that can be distributed by mail or fax without a valid mail address. Choose the setup button to select a mail address data field."

I gave the .MailAdddressFieldName property my own email address as a test, not the "Test@gmail" above, and my Word application is linked to my office account so I'm not sure where I need to further set up a valid address.

RMcCreavy
Lounger
Posts: 30
Joined: 02 Nov 2018, 16:12

Re: Using VBA to trigger a Mail Merge

Post by RMcCreavy »

I've just read elsewhere that the .MailAdddressFieldName should be the field name in my data source where I will pull the email from.

I changed that and the macro runs without an error, but I no longer see any output in Word or an email, just a blank / greyed out Word window.

User avatar
HansV
Administrator
Posts: 69403
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using VBA to trigger a Mail Merge

Post by HansV »

Check the Drafts and Outbox folders in Outlook - perhaps the messages are stuck there?
Regards,
Hans

RMcCreavy
Lounger
Posts: 30
Joined: 02 Nov 2018, 16:12

Re: Using VBA to trigger a Mail Merge

Post by RMcCreavy »

Ah, it was an Outlook issue, the macro did work!

Now how would I send the output as a word document and an email attachment rather than in the body of the email?

User avatar
HansV
Administrator
Posts: 69403
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using VBA to trigger a Mail Merge

Post by HansV »

That is more complicated than one would think. I recommend downloading and installing the MERGE TOOLS ADD-IN by Word MVP Doug Robbins from Merge Tools
Regards,
Hans

RMcCreavy
Lounger
Posts: 30
Joined: 02 Nov 2018, 16:12

Re: Using VBA to trigger a Mail Merge

Post by RMcCreavy »

What about saving each output as a new and separate word document?

User avatar
HansV
Administrator
Posts: 69403
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using VBA to trigger a Mail Merge

Post by HansV »

Regards,
Hans

RMcCreavy
Lounger
Posts: 30
Joined: 02 Nov 2018, 16:12

Re: Using VBA to trigger a Mail Merge

Post by RMcCreavy »

:cheers: Thanks Hans! This does look like a great tool.

Is it possible to call either of these ad ins with VBA?

User avatar
HansV
Administrator
Posts: 69403
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using VBA to trigger a Mail Merge

Post by HansV »

I don't know, but if you scroll down the last page I linked to, you'll find several macros to split a merged document.
Regards,
Hans

RMcCreavy
Lounger
Posts: 30
Joined: 02 Nov 2018, 16:12

Re: Using VBA to trigger a Mail Merge

Post by RMcCreavy »

A bit roundabout, but I found a way to merge and split my output into separate .docx files via a macro enabled Word document, and then I'm calling that Word macro from Excel so that I can scale this up and call multiple Word templates in one excel macro.

Word Macro:

Code: Select all

Option Explicit
Dim wd As Object, wdocSource As Object, strWorkbookName As String, strSavePath As String, _
MainFile As Document, OutputFile As Document, _
LastRecord As Long, RecordNumber As Long

Sub MergeAndSplit()

Application.ScreenUpdating = False
Const strSavePath = "A:\VBA_Output\"
'SP List Excel Query as Data Source
Const strWorkbookName = "A:\SP Query.xlsx"
'Open Mail Merge Template
Set MainFile = ActiveDocument
' "A:\Template.docm"

With MainFile.MailMerge

.OpenDataSource _
            Name:=strWorkbookName, _
            SQLStatement:="SELECT * FROM `query$`"
' Find Last Row in SP List
LastRecord = .DataSource.RecordCount
' Loop Through SP List
For RecordNumber = 1 To LastRecord

With .DataSource
.ActiveRecord = RecordNumber
.FirstRecord = RecordNumber
.LastRecord = RecordNumber
End With

.Destination = wdSendToNewDocument
.Execute False

Set OutputFile = ActiveDocument
OutputFile.SaveAs2 strSavePath & .DataSource.DataFields("Name").Value & ".docx", wdFormatDocumentDefault
OutputFile.Close False
Set OutputFile = Nothing

Next RecordNumber

End With

Set MainFile = Nothing
Application.ScreenUpdating = True

End Sub
Excel Macro to call Word Macro:

Code: Select all

Option Explicit
Dim wd As Object, wdocSource As Object

Sub CallWordMailMerges()
    Application.ScreenUpdating = False
    
    On Error Resume Next
    Set wd = GetObject(, "Word.Application")

    If Err.Number <> 0 Then
        Set wd = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    wd.Visible = True

    Set wdocSource = wd.Documents.Open("A:\Template.docm")
    Call wd.Run("MergeAndSplit")
    wdocSource.Close

    wd.Visible = False
    Set wdocSource = Nothing
    Set wd = Nothing
    
    Application.ScreenUpdating = True
End Sub


User avatar
HansV
Administrator
Posts: 69403
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using VBA to trigger a Mail Merge

Post by HansV »

Congratulations!
Regards,
Hans