PDA

View Full Version : Excel data to Word Email Merge



SPYUK
02-20-2012, 01:41 AM
Guy's I'd really appreciate some help!:banghead:
I have Office 2010 and an Excel sheet that I have used as the source in a Word Email merge.
I am trying to avoid the user having to open Word & run the Merge manually by providing some code in Excel to perform the task & create the emails.
The code I have fails with runtime 5174 error. Application or Object defined error at the highlighted point below.

Any help much appreciated!

Sub xxmerge()
Dim WordApp As Object
Dim fPath As String
fPath = "C:\Users\sp\desktop\MMX.docx"
Set WordApp = CreateObject("word.application")
WordApp.Documents.Open (fPath)
WordApp.Visible = True
ActiveDocument.Mailmerge.MainDocumentType = wdEMail
ActiveDocument.Mailmerge.OpenDataSource Name:= _
"C:\Users\SP\desktop\Merge File.xlsx", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\sp\desktop\Merge File.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Dat" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
With ActiveDocument.Mailmerge
.Destination = wdSendToemail
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub

Kenneth Hobs
02-20-2012, 07:57 AM
Intellisense should have told you that ActivieDocument was not in the Excel application object. Prefix it with the word object.

See these threads for examples:
'Mail Merge
' http://www.excelforum.com/excel-programming/796614-mail-merge-from-excel.html
' http://www.vbaexpress.com/forum/showthread.php?t=39586