Docmd.outputto Error

[DaveWatson]

Hi
I am using the Docmd.Outputto command to save the currently active report in either Word or Excel format.



The report consists of a concatenated field (=[title] & " " & [first name] & " " & [last name]) and a number of unique fields (eg [emailaddress] and [mobilephoneno]

When the report is exported using the export command from the ribbon, the data is output correctly. However when the data is output via VBA, the concatenated field does not report correctly - the output is

Mr Alan Biggs, biggsemail address, biggs mobile no
Mrs Jane Doe, doeemail address, does mobile no
Mrs Jane Doe, anemail address, a mobile no
Mrs Jane Doe, anotheremail address, anothermobile no
Mrs Jane Doe etc etc

The code is very simple:

Private Sub Command27_Click()
On Error Resume Next
DoCmd.OutputTo acOutputReport, , acFormatRTF, , True
End Sub

Any help greatly appreciated
Dave

[June7]

Specifically, what is wrong with that output? You show data separated by commas. Are commas really in the output?

[DaveWatson]

No, I have just used those to separate the fields.

I am getting the following from the docmd
Name email address Phone No
Mr Alan Biggs biggs@emailaddress.com 01234 5678
Mrs Jane Doe doe@anotheremailaddress 01234 8765
Mrs Jane Doe classy@emailaaddress 01543 9876
Mrs Jane Doe dee@emailadddress3.com 07872 3456

instead of
Name email address Phone No
Mr Alan Biggs biggs@emailaddress.com 01234 5678
Mrs Jane Doe doe@anotheremailaddress 01234 8765
Ms Nelly Classy classy@emailaaddress 01543 9876
Mr Edward Dee dee@emailadddress3.com 07872 3456

which 'export to Word' from the ribbon correctly provides...
So the first field reports incorrectly when using docmd, but correctly via the built-in ribbon...

[June7]

I have little experience with exporting reports. I know it can be frustrating. I would have to examine the report. If you want to provide db, follow instructions at bottom of my post.

Why do you need this export?

[DaveWatson]

This is a membership database for a sports club. The membership secretary does not have the full Access 2007 on her machine, so I need a db that works in runtime mode, so that for example she can print labels and send bulk emails (I haven't cracked sending bulk emails via Access yet!). 'export to word' and 'export to excel' in the ribbon cannot be used in runtime mode (I tried creating a custom ribbon for the report. Whilst these were visible on the custom ribbon when the db was opened in Access, they do not appear when in run-time mode - this is mentioned in many posts in the forums) - and the workaround seems to be docmd.outputto or docmd.transferspreadsheet.

I have split the concatenated field ((=[title] & " " & [first name] & " " & [last name]) back into its three separate parts, and the docmd works perfectly! This looks unsightly on an address label though.

I will need to sanitise the tables before I can upload the db, as it is currently populated with live addresses of real people.

Thanks
Dave

[June7]

Possible alternatives:

1. Word/Access mail merge for label output

2. Access report to print 'labels' - I do this

Bulk email could be two approaches:

1. one email with multiple addressees (bcc)

2. many emails - one to each address

Either will involve looping code to read each address and either add it to an address string or to send individual email.