Changing report name for SendObject

[reclusivemonkey]

Hello Everyone,

I am new here so please forgive any faux pas. I would like to create a module that exports a report in RTF format, renames it and then sends the report to a specified email. The problem I am having is that I want to send this exported report (it has a custom name depending on the report criteria) rather than the actually report in access. I don't seem to be able to pass the name of this report to the SendObject method. I can modify the code to send the report with its default name, but not the custom one. Anyone any ideas on how to acheive this? Thanks in advance. Code starts here;

Private Sub MailReport_Click()
On Error GoTo Err_MailReport_Click


Dim frm As Form, ctl As Control
Dim stDocName As String
Dim stExpName As String
Dim CCENT_CODE As String
Dim MONTH As String

Set frm = Forms!frmReportCriteria
Set ctl = frm!TabsList

MONTH = Me.Select_MONTH
stDocName = "rptTab"

For Each Item In ctl.ItemsSelected
Next Item

MailRecipient = ctl.Column(0, Item)
EmailAddress = ctl.Column(1, Item)
CCENT_CODE = ctl.Column(2, Item)
stExpName = CCENT_CODE & "_" & MONTH & "_Detail.rtf"
DoCmd.OutputTo acReport, stDocName, acFormatRTF, stExpName

MsgBox "The report has been exported to " & stExpName

DoCmd.SendObject acSendReport, "C:\Documents and Settings\efah.NORTHGATE\Desktop\" & stExpName, acFormatRTF, EmailAddress, , , "Tab Report for " & MONTH, _
"Please find enclosed your tab report for the month of " & MONTH & ". Regards,"

Exit_MailReport_Click:
Exit Sub

Err_MailReport_Click:
MsgBox Err.Description
Resume Exit_MailReport_Click

End Sub

 

[reclusivemonkey]

I've found a more long winded way to do this using DoCmd.CopyObject to create a report with the right name, then export/send this, and then delete the custom report with a DoCmd.DeleteObject. However the name of the exported report is limited to eight characters. Is it possible to have more? Sorry about replying to my own post, just thought it might be useful for anyone else.

 

[WayneRyan]

rm,

Have you looked at:

Name "SomeOldFileName.exp" As "SomeNewFineName.exp"

Wayne

 

[reclusivemonkey]

Hi Wayne,

Where would I put the Name command? I am exporting the report, then sending it. So whatever I rename the report, surely the SendObject command is going to rename the report as its attaching it to the mail?