Add date to report name

[paulmitchell507]

I have a Macro in Access 2000 that sends a report via email using the
'SendObject' action. The object name, or report name is 'Hector
support calls'. I would like add the days date to the report name,
simular to the format below

(="C:\AccessReports\" & Format(Date(),"ddmmyyyy") & "_Hector support
calls.xls")


I don't want to export the report to disk, run the code above and
then
mail the report, I would like to rename the report when the Macro
sends the e-mail.


Any advice would be appreciated.

 

[Wayne-I-M]

Hi Paul

We use this system every week -
We send a report to multiple clients with the date added to the report title
the report is copied to our DB and then I send the the archive DB
then delete the copy

This way I have a back up of all the reports I have sent (for various
reasons I can't run them again) and the client has a dated report win/win

The trick is to copy the report ( using DoCmd.CopyObject) and then rename it
at the time it is being sent.

Like this

Private Sub ButtonName_Click()
DoCmd.CopyObject , "SomeReportName" & Date, acReport, "SomeReportName"
DoCmd.SendObject acReport, "SomeReportName", "RichTextFormat(*.rtf)",
EMailAdrress, "", "", "this is the title " & Date, "this is the subject",
False, ""
End Sub

Of course this will mean you have a copy of the report but you can add to
this code to delete the copy right after sending (if you don't want to keep
it)

CurrentDb.TableDefs.Delete "SomeReportGoeshere"

But as I said you maay be better just copying them another DB and keeping a
copy - up to you

Hope this helps

 

[Wayne-I-M]

Sorry I made a mistake - you need to send the "copy" (the one with the date
added)

ooops


Private Sub Command1_Click()
DoCmd.CopyObject , "SomeReportName" & Date, acReport, "SomeReportName"
DoCmd.SendObject acReport, "SomeReportName" & Date, "RichTextFormat(*.rtf)",
EMailAdrress, "", "", "this is the title ", "this is the subject", False, ""
End Sub


Note I have changed this bit
DoCmd.SendObject acReport, "SomeReportName" & Date,
So you are sending the dated report - which what I think you want to do

Sorry for the mistake