Syntax for using spreadsheet as data source in emailmerge macro

[owlbrudder]

Version: 5.1.6.2
Build ID: 07ac168c60a517dba0f0d7bc7540f5afa45f0909
CPU Threads: 8; OS Version: Linux 4.10; UI Render: default;
Locale: en-AU (en_AU.UTF-8); Calc: group

I am writing a simple macro to run an emailmerge using email addresses stored in a spreadsheet. I am having no luck in working out the correct syntax.

I have a spreadsheet at “/home/doug/ABPA software/EmailMergeAddresses.ods”, containing Sheet1, in which I have several columns including one whose header is ‘email’ and this, naturally, contains the email addresses I want to use.

This is my code so far:

'******************************************************************************************
'// Run Emailmerge over the current document
'******************************************************************************************
Function fncEmailMerge() As Boolean
	Dim	objMailMerge As Object

	On Error GoTo hell
	
	fncEmailMerge = True
	
	objMailMerge = CreateUnoService("com.sun.star.text.MailMerge")

	'Set the document name
	objMailMerge.DocumentURL = ThisComponent.URL

	'Set the datasource name
	objMailMerge.DataSourceName = "/home/doug/ABPA software/EmailMergeAddresses.ods"
	
	'Set the data command
	objMailMerge.Command = "EmailMergeAddresses.Sheet1"
	
	'Set the command type
	objMailMerge.CommandType = constTABLE
	
	'Set the email address column
	objMailMerge.AddressFromColumn = "email"
	
	'Set the object to send the message as HTML
	objMailMerge.SendAsHTML = True
	
	'Set the object to send as email
	objMailMerge.OutputType = constEMAIL
	
	'Set the subject
	objMailMerge.Subject = "Testing an email merge"
	
	'Run the mailmerge
	objMailMerge.execute(Array())
	
	GoTo hello
	
hell:
    MsgBox "Error " & Err & ": " & Error$ + chr(13) + "At line : " + Erl + chr(13) + Now , 16 ,"An error occurred"
	fncEmailMerge = False
	
hello:
	On Error Resume Next
	
	objMailMerge.dispose
	
End Function

I am getting this error:

I would be very grateful for help in sorting our what I am doing wrong.

Cheers,
Doug

[rautamiekka]

Since the source code is 2-line XML in the ODS, the line 364 refers to a row in the sheet. Check that.

[Ratslinger]

The error you are getting points to this line (based on a test with your code):

objMailMerge.execute(Array())

and is because of invalid or missing settings.

Nowhere in com.sun.star.text.MailMerge does it specify or mention using spreadsheets. It does specify using a database Table, Query, or Command (SQL).

[owlbrudder]

Thanks Ratslinger. I was bluffed because Writer’s mailmerge wizard allows the use of spreadsheet data. I have since converted to a Base document and it all works fine.
Thanks for your help.