Changing Report Name

  • Thread starter Thread starter paulmitchell507
  • Start date Start date
P

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.
 
Hi Paul,

Please be more specific about what part of the process you are stuck on.

Bonnie

http://www.dataplus-svc.com
Click to expand...

I don't know how to append the date to the report name using the
Access Macro. The macro just runs the query\report and sends a copy
of the report via email. I would like to run the query\report, append
the current days date to the report name and then email the report.
 
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.
Click to expand...

Paste the following into a new Access module.

Public Sub EmailReport()
Dim strOld As String
Dim strNew As String
strOld = "Hector support calls"
strNew = strOld & Format(Date, "mm/dd/yyyy")

DoCmd.Rename strNew, acReport, strOld

On Error GoTo Err_Handler
DoCmd.SendObject acSendReport, strNew, acFormatRTF,
"(e-mail address removed)", , , "Test", "Body of text", True

Exit_Error:
DoCmd.Rename strOld, acReport, strNew
Exit_Sub:
Exit Sub
Err_Handler:
Resume Exit_Error
End Sub
__________

The above will send an email with the report named
"Hector support calls06/24/2008.rtf"
as an .rtf attachment.

To send the report, you can call it from some code event:

EMailReport
 
That will work once.
The next time, "Hector support calls" will not be there and the code will
error out.

Instead,

DoCmd.CopyObject, DoCmd.Rename strNew, acReport, strOld
 
That will work once.
The next time, "Hector support calls" will not be there and the code will
error out.

Instead,

DoCmd.CopyObject, DoCmd.Rename strNew, acReport, strOld
Click to expand...

Actually it works fine, Dave.

Public Sub EmailReport()
.........
On Error GoTo Err_Handler
DoCmd.SendObject acSendReport, strNew, acFormatRTF,
"(e-mail address removed)", , , "Test", "Body of text", True

Exit_Error:
DoCmd.Rename strOld, acReport, strNew
Exit_Sub:
Exit Sub
Err_Handler:
Resume Exit_Error
End Sub

If there is no error Exit_Err is executed immediately after the email
is sent. If there is an error, Err_Handler sends processing to
Exit_Error which renames the Report back to the original.
 
Actually it works fine, Dave.

Public Sub EmailReport()
........
On Error GoTo Err_Handler
DoCmd.SendObject acSendReport, strNew, acFormatRTF,
"(e-mail address removed)", , , "Test", "Body of text", True

Exit_Error:
    DoCmd.Rename strOld, acReport, strNew
Exit_Sub:
    Exit Sub
Err_Handler:
    Resume Exit_Error
End Sub

If there is no error Exit_Err is executed immediately after the email
is sent. If there is an error, Err_Handler sends processing to
Exit_Error which  renames the Report back to the original.
Click to expand...

Fred
I can't believe how helpfull you have been, thank you.
One last thing, I use Outlook 2k as my perferred mail client. When I
run the code, Access does exaclty what I want it to do except that
instead of sending the e-mail, the message is composed with the
correct 'To' address and the attached report but is not sent. I have
to manually click 'send' to send the e-mail. Do you know why the
message is not automaticaly sent? If I use the SendObject action via
a macro, the message is composed and sent automatically.

Regards
Paul
 
Fred
I can't believe how helpfull you have been, thank you.
One last thing,  I use Outlook 2k as my perferred mail client.  When I
run the code, Access does exaclty what I want it to do except that
instead of sending the e-mail, the message is composed with the
correct 'To' address and the attached report but is not sent.  I have
to manually click 'send' to send the e-mail.  Do you know why the
message is not automaticaly sent?  If I use the SendObject action via
a macro, the message is composed and sent automatically.

Regards
Paul- Hide quoted text -

- Show quoted text -
Click to expand...

Fred,
Please forget my last post,
DoCmd.SendObject acSendReport, strNew, acFormatRTF,
"(e-mail address removed)", , , "Test", "Body of text", FALSE

Changing true to false did the trick.

Thank you again.

Paul
 
Back
Top