docmd.copyobject (report) - will not copy in vba for some reports

[niki09]

I have the need to copy a report to I can email the report with a Title describing the data (example: I want to give the report a title of the City that the data refers to, and email that report so the pdf has the name of the City).

In my code, I have variables that correctly filter the data into a city. That works.
I then have the template report that prints the data into a pdf. All fine.
I do not want the template report emailed with the template report ame, but with the name of the data it shows.

When using the code:
DoCmd.CopyObject , RptName, acReport, "Worksheet"
"access comes up with the message
Runtime error 29068. Microsoft Access cannot complete this operation. You must stop the code and try again."
(RptName is string variable developed from the name of the city.

I have experimented by removing RptName to be "NewReport", and same error.

I have experimented with other reports I have, and access will do docmd.copyobject for a report.
But it will not do it for all of the reports I have, only some.
Access though will not do the copy for some of my reports, always giving the same error.

The error seems to appiy only to some of the reports.

I am using Access2007

[June7]

Why don't you save the PDF with preferred name instead of copying report? Programmatically copying object is a db design modification. Not a good practice.

[ItsMe]

...I do not want the template report emailed with the template report ame, but with the name of the data it shows....

I do not know that I fully understand what the objective is or why you sometimes get an error. Are you trying to provide the attachment file a custom name? I think I remember a solution for this posted somewhere. I can't seem to find it right now. I believe it used Outlook automation to rename the attachment.

What I do is use Docmd.OutputTo to save the report as a PDF. Then I use automation to attach the PDF. The problem with this is you have to create extra code to make sure there is not an existing PDF file in your temp directory and that a temp directory actually exists.

 

'before exporting your report you will want to make
'sure there is not a file in the folder with the same name
'and that the folder actually exists

'create a report in a temp folder
DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, "C:\Test\PDF_Files\Test.pdf"

'Reference Microsoft Outlook and
'use early binding
Dim objOutlook As New Outlook.Application
Dim objNewEmail As MailItem
Dim objAttachReport As Attachments
'Instantiate your objects
Set objNewEmail = objOutlook.CreateItem(olMailItem)
Set objAttachReport = objNewEmail.Attachments
'Add your file to the Attachments Collection
objAttachReport.Add "C:\Test\PDF_Files\Test.pdf", olByValue
    With objNewEmail
        .BodyFormat = olFormatRichText
        .To = "MyEmail@Domain.com"
        .Subject = "Test Message"
        .HTMLBody = "This is the body of the message"
        'Save the email to ensure the attachment is a
        'copy of the original file (olByValue)
        .Save
        .Send
    End With

'tidy up
Set objAttachReport = Nothing
Set objNewEmail = Nothing
Set objOutlook = Nothing

MsgBox "Complete"

[niki09]

I do not know that I fully understand what the objective is or why you sometimes get an error. Are you trying to provide the attachment file a custom name? I think I remember a solution for this posted somewhere. I can't seem to find it right now. I believe it used Outlook automation to rename the attachment.

What I do is use Docmd.OutputTo to save the report as a PDF. Then I use automation to attach the PDF. The problem with this is you have to create extra code to make sure there is not an existing PDF file in your temp directory and that a temp directory actually exists.

 

'before exporting your report you will want to make
'sure there is not a file in the folder with the same name
'and that the folder actually exists

'create a report in a temp folder
DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, "C:\Test\PDF_Files\Test.pdf"

'Reference Microsoft Outlook and
'use early binding
Dim objOutlook As New Outlook.Application
Dim objNewEmail As MailItem
Dim objAttachReport As Attachments
'Instantiate your objects
Set objNewEmail = objOutlook.CreateItem(olMailItem)
Set objAttachReport = objNewEmail.Attachments
'Add your file to the Attachments Collection
objAttachReport.Add "C:\Test\PDF_Files\Test.pdf", olByValue
    With objNewEmail
        .BodyFormat = olFormatRichText
        .To = "MyEmail@Domain.com"
        .Subject = "Test Message"
        .HTMLBody = "This is the body of the message"
        'Save the email to ensure the attachment is a
        'copy of the original file (olByValue)
        .Save
        .Send
    End With

'tidy up
Set objAttachReport = Nothing
Set objNewEmail = Nothing
Set objOutlook = Nothing

MsgBox "Complete"

the objective is to receive a file by an email that comes from the code, having a custom name. Access gives the file name as the name of the report.
I will give your code a try.
Thanks

[niki09]

Dear ItsMe
I have loaded the code in but it gives me an immediate compile error on the first line
Dim objOutlook As New Outlook.Application

I am using Access 2007 if this is the explanation?

[June7]

Did you set VBA reference to the Microsoft Outlook Object Library?

[niki09]

Did you set VBA reference to the Microsoft Outlook Object Library?

Dear Sir,
you have provided me the solution. My problem is now resolved.
Thanks