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!