Results 1 to 11 of 11
  1. #1
    coletteabrown is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    7

    Macro doesn't run properly when activated by On Click of button in Form

    I've designed a Macro to rename my report to include yesterday's date and then e-mail it. When I click this Macro from the Access Objects list, it runs perfectly.

    However, I would like the Macro to run On Click of a button. I have created a form with various buttons for opening various reports. There is also this button to Email Yesterday's Production Report. When clicked it renames the form to what I have defined as the new name for my Report and emails the report without renaming.

    Here is the Macro:
    OpenReport
    Report Name: rep Yesterday's Production Report
    View: Design
    Filter name:
    Where Condition:
    Window Mode: Normal

    SelectObject
    Object Type: Report
    Object Name: rep Yesterday's Production Report
    In Database Window: No

    SetProperty


    Control Name:
    Property: Caption
    Value: = "Production" & Date()-1

    EmailDatabaseObject

    Object Type: Report
    Object Name: rep Yesterday's Production Report
    Output Format: PDF Format (*.pdf)
    To: you@gmail.com
    Cc:
    Bcc: me@gmail.com
    Subject: Daily Production
    Message Text: Hello all - Attached is yesterday's production. Thanks.
    Edit Message: Yes
    Template File:

    Any ideas on how I can get this Macro to run On Click of a button? I added the SelectObject line because I thought that would help to make sure the Macro wasn't renaming the Form, but this still doesn't work... I even tried creating a new form with just this one button on it to run this Macro and ... no luck!

    Thank you!
    Colette

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Have you ever considered writing some VBA and placing it the On Click event handler ?

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Which is applied as the attachment name? Is it the report name or the original report caption?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    coletteabrown is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    7
    @ItsMe: Unfortunately I don't have experience with writing VBA, so I'm not sure how to do that! Though it had crossed my mind, if I knew how to write VBA I would be able to do this much easier.

    @June7: Good question! The original report caption is applied to the attachment name. I'm not sure why that's happening...

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    You can place the following code in a click event. Just edit the text within the quotes. For instance, you will need to type the name of your report and the recipient email address.


    Code:
    Dim strSubject As String
    Dim strBody As String
    Dim strReport As String
    Dim strTo As String
    Dim strCc As String
    Dim strBcc As String
    strSubject = "Insert Subject Line Here"
    strBody = "Insert text to be displayed in the email body"
    strReport = "NameOfYourReport"
    strTo = "you@gmail.com"
    strCc = ""
    strBcc = "me@gmail.com"
    
    On Error Resume Next
                DoCmd.SendObject acSendReport, strReport, acFormatPDF, strTo, strCc, strBcc, strSubject, strBody, True
                
    MsgBox "There was an error. Perhaps you cancelled the Email."
    Exit Sub

  6. #6
    coletteabrown is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    7
    Hmm, thanks ItsMe but I can't get the code to work? It actually returns an error that the Attachment name is invalid: ' "Production Report" Date()-1' - though I have it written in the code as ="Production Report" & Date()-1. Like I said, I don't have much experience with VBA so I don't know how to fix that issue.

    If there's a non-VBA solution, I would much rather use that!

    For now, I have resorted to opening the report with a group in the Navigation Pane called Email. The only object in the group is the Macro I need. It's not a very clean answer, but it works for now...

    Thank you for your help!

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Well don't blame Access. It is only going to do what you ask of it.

    What is the name of your report?

    Is it
    rep Yesterday's Production Report

    If it is, you should change it. Try removing the apostrophe. This may be why your Macro is not happy.

    The only thing you need to adjust, to get the code I provided to work is the name of the report. The text within the quotes. No special characters.
    strReport = "NameOfYourReport"

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Good catch on the apostrophe!

    Should not use spaces or special characters/punctuation (underscore is exception) in ANY names.

    However, this code still uses SendObject which should do the same as the macro - use the object's caption.

    I was able to use VBA to change caption of open report and create email with the attachment using the modified caption:

    Reports("Summary").Caption = "Summary" & Date() - 1
    DoCmd.SendOption acSendReport, , acFormatPDF, strTo, strCc, strBcc, strSubject, strBody, True
    Docmd.Close acReport, "Summary", acSaveNo

    I don't use macros but I just tested one to open report, change caption, send email. It works, even with space and apostrophe (not showing in the above code) in the report name. I don't know why your original macro fails.

    Another VBA Option is:
    first save object as a PDF to external folder then open Outlook object and manipulate it to send email with the PDF file as an attachment. One advantage of this approach is can use HTML code tags to do some fancy formatting of the email body.
    Last edited by June7; 11-20-2013 at 04:04 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    coletteabrown is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    7
    Yes! It worked - must have been the apostrophe! I used your code ItsMe plus part of your code June7 to complete the VBA to change the name of the attachment!!

    Thank you both for all of your help

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Glad it's working but as noted, space and apostrophe were not issues in my test.

    Decided to test macro again. This time I see that it is not picking up the date. I end up with attachment named: Summary_&_Date()-1.pdf. Could have sworn it worked before. Weird. However, space and apostrophe still not issue but there are certainly situations where can be if not handled properly.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Good deal. And the file renaming code June provided is very nice indeed.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-23-2013, 11:46 PM
  2. Why doesn't RTF text display properly in report?
    By kenton.l.sparks@gmail.com in forum Access
    Replies: 1
    Last Post: 05-02-2012, 07:30 PM
  3. Combo box doesn't work properly
    By joe1987 in forum Forms
    Replies: 9
    Last Post: 11-10-2011, 04:34 PM
  4. Replies: 3
    Last Post: 12-09-2010, 11:50 AM
  5. On Click Event For Button On Form
    By Desstro in forum Forms
    Replies: 3
    Last Post: 08-09-2010, 02:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums