Ask Your Question
0

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

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

owlbrudder gravatar image

updated 2017-12-10 17:16:00 +0200

erAck gravatar image

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: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

Comments

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
0

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):

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).

edit flag offensive delete link more

Comments

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

Stats

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

Seen: 183 times

Last updated: Apr 10 '17