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
Any help towards my understanding of how VBA could execute this task is greatly appreciated!