est
10-04-2019, 06:18 AM
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)
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)