Ask Your Question

Syntax for using spreadsheet as data source in emailmerge macro [closed]

asked 2017-04-09 06:28:36 +0200

owlbrudder gravatar image

updated 2020-08-02 12:29:33 +0200

Alex Kemp gravatar image

Version: 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("")

    '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

    GoTo hello

    MsgBox "Error " & Err & ": " & Error$ + chr(13) + "At line : " + Erl + chr(13) + Now , 16 ,"An error occurred"
    fncEmailMerge = False

    On Error Resume Next


End Function

I am getting this error:image description

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

Cheers, Doug

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2017-12-10 17:16:23.237459


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

rautamiekka gravatar imagerautamiekka ( 2017-04-09 10:58:19 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-04-10 01:12:55 +0200

Ratslinger gravatar image

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


and is because of invalid or missing settings.

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

edit flag offensive delete link more


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.

owlbrudder gravatar imageowlbrudder ( 2017-04-10 07:39:31 +0200 )edit

Question Tools

1 follower


Asked: 2017-04-09 06:28:36 +0200

Seen: 290 times

Last updated: Apr 10 '17