Good afternoon, I hope I can get some help. I have written some vba code in excel that generates pdf files and emails them to various people. The code works ok when I run it on a laptop with office 2010. However, if I try to run it on Office 2007 with DDE activated by selecting the "Confirm file format conversion on open" option in Word option I get a runtime 5853 error invalid parameter. The DDE option works fine on office 2010 but generates the error onoffice 2007. Both work filne when the "Confirm file format conversion on open" is not selected, but I want to be able to retain the format from excel to the mailmerge. I have shown the code below
The error occurs on the line "wdDoc.MailMerge.DataSource.ActiveRecord = x", even when the value of x = 1 and its on the first record. Can someone please help. I have been on the issue now for weeks.
Sub MMPrimaryMM()
'
' MMPrimaryMM Macro
'
Dim wdOutputName, wdInputName, PDFFileName As String
Dim x As Integer
Dim nRows As Integer
Dim wdApp As Object
Windows("T-bill Notification letters.xlsm").Activate
Path = Cells(2, 2)
Path2 = Cells(2, 2) & "\" & "TBill Primary Auction"
wdInputName = Path & "mailmerge fd bid.doc"
wdOutputName = Path & "mailmerge fd bid output.doc"
'Const wdFormLetters = 0, wdOpenFormatAuto = 0
'Const wdSendToNewDocument = 0, wdDefaultFirstRecord = 1
'Const wdDefaultFirstRecord = 1
'This will get you the number of records "-1" accounts for header
Workbooks.Open Filename:= _
Path & "Mailmerge Primary Auction.xlsx"
Windows("Mailmerge Primary Auction.xlsx").Activate
nRows = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row - 1
wdDefaultLastRecord = nRows
' open the mail merge layout file
Dim wdDoc As Object
Dim wdDoc5 As Object
Set wdDoc = GetObject(wdInputName, "Word.document")
Set wdApp = GetObject(, "Word.Application") 'establishing the word application
'wdDoc.Application.Visible = False
For x = 1 To nRows
Add = ""
' Get Email Address
Windows("Mailmerge Primary Auction.xlsx").Activate
Add = Cells(x + 1, 19)
'With wdDoc.MailMerge.DataSource
wdDoc.MailMerge.DataSource.ActiveRecord = x
wdDoc.MailMerge.MainDocumentType = wdFormLetters
wdDoc.MailMerge.Destination = wdSendToNewDocument
wdDoc.MailMerge.SuppressBlankLines = True
wdDoc.MailMerge.DataSource.FirstRecord = x 'wdDefaultFirstRecord
wdDoc.MailMerge.DataSource.LastRecord = x ' wdDefaultLastRecord
wdDoc.MailMerge.Execute Pause:=False
Set wdDoc5 = wdApp.Application.ActiveDocument
wdDoc5.Application.Visible = True
' show and save output file
' cells(x+1,2)references the first cells starting in row 2 and increasing by 1 row with each loop
'Date_to_Save = Cells(x + 1, 1)
'RepDate = Format(Cells(x + 1, 1), "dd-mm-yyyy")
'PDFFileName = Path2 & "\" & "PRIMARY " & RepDate & " " & Cells(x + 1, 3) & ".pdf"
'wdDoc5.ExportAsFixedFormat PDFFileName, 17, Range:=wdExportFromTo, From:=x, To:=x
'************End of PDF section*************
'************Start of emailing code*********
'Set Mail_Object = CreateObject("Outlook.Application")
' With Mail_Object.CreateItem(o)
' .Subject = "CONFIRMATION LETTER FOR PRIMARY T-BILL AUCTION FOR YOUR CLIENT " & Cells(x + 1, 3) ' CHANGE TO SUIT
' .To = Add 'CHANGE TO SUIT
' .Body = "Please find attached T-bill confirmation letter for your client " & Cells(x + 1, 3) & Chr(13) & "Kindly note that you should confirm the investment details in the confirmation letter before printing on Stanbic IBTC Bank letter-headed paper and dispatching to your client accordingly." & Chr(13) & "Please refer all issues on this confirmation to GMOConfirmation@stanbicibtc.com " & Chr(13) & Chr(13) & "Best Regards," & Chr(13) & "GMO Confirmation Team" 'Change comments to suit
' .Attachments.Add PDFFileName
' .Send
'End With
Next x
' cleanup
wdDoc.Close SaveChanges:=False
wdDoc5.Close SaveChanges:=False
wdApp.Application.Quit wdDoNotSaveChanges
Set wdDoc = Nothing
Set wdDoc5 = Nothing
Windows("Mailmerge Primary Auction.xlsx").Activate
Windows("Mailmerge Primary Auction.xlsx").Close
'Set Mail_Object = Nothing
End Sub
The error occurs on the line "wdDoc.MailMerge.DataSource.ActiveRecord = x", even when the value of x = 1 and its on the first record. Can someone please help. I have been on the issue now for weeks.
Sub MMPrimaryMM()
'
' MMPrimaryMM Macro
'
Dim wdOutputName, wdInputName, PDFFileName As String
Dim x As Integer
Dim nRows As Integer
Dim wdApp As Object
Windows("T-bill Notification letters.xlsm").Activate
Path = Cells(2, 2)
Path2 = Cells(2, 2) & "\" & "TBill Primary Auction"
wdInputName = Path & "mailmerge fd bid.doc"
wdOutputName = Path & "mailmerge fd bid output.doc"
'Const wdFormLetters = 0, wdOpenFormatAuto = 0
'Const wdSendToNewDocument = 0, wdDefaultFirstRecord = 1
'Const wdDefaultFirstRecord = 1
'This will get you the number of records "-1" accounts for header
Workbooks.Open Filename:= _
Path & "Mailmerge Primary Auction.xlsx"
Windows("Mailmerge Primary Auction.xlsx").Activate
nRows = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row - 1
wdDefaultLastRecord = nRows
' open the mail merge layout file
Dim wdDoc As Object
Dim wdDoc5 As Object
Set wdDoc = GetObject(wdInputName, "Word.document")
Set wdApp = GetObject(, "Word.Application") 'establishing the word application
'wdDoc.Application.Visible = False
For x = 1 To nRows
Add = ""
' Get Email Address
Windows("Mailmerge Primary Auction.xlsx").Activate
Add = Cells(x + 1, 19)
'With wdDoc.MailMerge.DataSource
wdDoc.MailMerge.DataSource.ActiveRecord = x
wdDoc.MailMerge.MainDocumentType = wdFormLetters
wdDoc.MailMerge.Destination = wdSendToNewDocument
wdDoc.MailMerge.SuppressBlankLines = True
wdDoc.MailMerge.DataSource.FirstRecord = x 'wdDefaultFirstRecord
wdDoc.MailMerge.DataSource.LastRecord = x ' wdDefaultLastRecord
wdDoc.MailMerge.Execute Pause:=False
Set wdDoc5 = wdApp.Application.ActiveDocument
wdDoc5.Application.Visible = True
' show and save output file
' cells(x+1,2)references the first cells starting in row 2 and increasing by 1 row with each loop
'Date_to_Save = Cells(x + 1, 1)
'RepDate = Format(Cells(x + 1, 1), "dd-mm-yyyy")
'PDFFileName = Path2 & "\" & "PRIMARY " & RepDate & " " & Cells(x + 1, 3) & ".pdf"
'wdDoc5.ExportAsFixedFormat PDFFileName, 17, Range:=wdExportFromTo, From:=x, To:=x
'************End of PDF section*************
'************Start of emailing code*********
'Set Mail_Object = CreateObject("Outlook.Application")
' With Mail_Object.CreateItem(o)
' .Subject = "CONFIRMATION LETTER FOR PRIMARY T-BILL AUCTION FOR YOUR CLIENT " & Cells(x + 1, 3) ' CHANGE TO SUIT
' .To = Add 'CHANGE TO SUIT
' .Body = "Please find attached T-bill confirmation letter for your client " & Cells(x + 1, 3) & Chr(13) & "Kindly note that you should confirm the investment details in the confirmation letter before printing on Stanbic IBTC Bank letter-headed paper and dispatching to your client accordingly." & Chr(13) & "Please refer all issues on this confirmation to GMOConfirmation@stanbicibtc.com " & Chr(13) & Chr(13) & "Best Regards," & Chr(13) & "GMO Confirmation Team" 'Change comments to suit
' .Attachments.Add PDFFileName
' .Send
'End With
Next x
' cleanup
wdDoc.Close SaveChanges:=False
wdDoc5.Close SaveChanges:=False
wdApp.Application.Quit wdDoNotSaveChanges
Set wdDoc = Nothing
Set wdDoc5 = Nothing
Windows("Mailmerge Primary Auction.xlsx").Activate
Windows("Mailmerge Primary Auction.xlsx").Close
'Set Mail_Object = Nothing
End Sub