Hi,

I am by no measure a VBA whizz, but I am successfully using the following code (adapted from codes found on this forum) to extract data from Word documents and record the data in Excel.

Sub GetFormData()
'Note: this code DOES NOT require a reference to the Word object model
Dim wdApp As Object
Dim wdDoc As Object
Dim CCtrl As Object
Dim FmFld As Object
Dim strFolder As String, strFile As String
Dim WkSht As Worksheet, i As Long, j As Long
    Application.ScreenUpdating = False
    strFolder = BrowseForFolder("Select folder containing DWM EOI document")
    If strFolder = "" Then Exit Sub
    Set WkSht = ActiveSheet
    i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
    strFile = Dir(strFolder & "\*.docm", vbNormal)
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    While strFile <> ""
        i = i + 1
        Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & strFile, AddToRecentFiles:=False, Visible:=False)
        With wdDoc
            j = 5
            For Each CCtrl In .ContentControls
                j = j + 1
                WkSht.Cells(i, j) = CCtrl.Range.Text
            Next
            For Each FmFld In .FormFields
                j = j + 1
                WkSht.Cells(i, j) = FmFld.Result
            Next
        End With
        wdDoc.Close 0
        strFile = Dir()
        DoEvents
    Wend
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
    Application.ScreenUpdating = True
End Sub
Private Function BrowseForFolder(Optional strTitle As String) As String
'Erinc Tabak
'strTitle is the title of the dialog box
Dim fDialog As FileDialog
    On Error GoTo err_Handler
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With fDialog
        .Title = strTitle
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then GoTo err_Handler:
        BrowseForFolder = fDialog.SelectedItems.Item(1) & Chr(92)
    End With
lbl_Exit:
    Exit Function
err_Handler:
    BrowseForFolder = vbNullString
    Resume lbl_Exit
End Function

Some of the content control fields include information such as contact name, project reference and contact e-mail.

My question is, how do I add to this VBA code, so that once it harvests the data from the Word document and records it in Excel - it then uses the data to send an e-mail. The e-mail would have a standard body but also some mail merge-esque personalisation, also using the data harvested from the Word document.

Any help at all would be appreciated.

Thank-you in advance for your time,

Erinc (absolute newbie but budding VBA extraordinaire)