Results 1 to 10 of 10
  1. #1
    AMJADJ is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jul 2018
    Posts
    34

    Capture parameter value and insert into email subject

    Hi All

    I am fairly new to VBA, however, have managed to write a script that does the following:



    --> Opens an access report (upon opening requires the user to enter the Order ID)
    --> Saves the report as a PDF
    --> Emails this PDF file to specified email addresses

    What I would like to do is capture the ID the user enters and insert this into the email subject I.E. New Order "ID ENTERED" Requires authorisation.

    I could post the existing code but as I am new to this, didn't want a slap on the wrist on my first post because I had not posted code in the expected manner!!


    Thank you in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    email rpt as pdf using:

    docmd.SendObject acSendReport ,"rMyReport",acFormatPDF,sTo,,,sSubj,sBody

  3. #3
    AMJADJ is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jul 2018
    Posts
    34
    Hi Ranman

    Thank you for taking the time out to reply, the code successfully creates a PDF file and emails to the named addresses. The issue I have is, when I click on the button to open the report, the user will enter an ID(lets say 5), the report opens with the information for ORDER 5. I then have another button on the report that creates the pdf file and sends out an email. However the subject currently is just new order requiring authorisation. I would like this to say new order 5 requiring authorisation i.e. the user entry is "captured" and then placed in the subject.

    Hope this clarifies the issue.

    Regards

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Most of us would use a form to gather user input. You could concatenate that value into your subject string. You can post your code with no hand slaps; using the code tags (# icon) preserves formatting.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    AMJADJ is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jul 2018
    Posts
    34
    Apologies, maybe I am not explaining it properly!

    The user enters the order details into a form, and once happy there is a button to submit authorisation email. This opens a report (because the report and form are different layout), currently as the report is based on a query, it asks the user to enter the order ID for which the report should be opened. What I want is the ID the form was open at, to be used when opening the report. I have got a little further with the below, however my understanding is open report with preview was without it printing it. The below code is still looking to print the report:

    Code:
    Dim strWhere As String
    Dim DocName As String
    DocName = "ID: Overview"
    strWhere = "[ID]='" & Me.ID & "'"
    DoCmd.OpenReport "Sample Order Form", acViewPreview, , strWhere
    Going into debug mode, acviewpreview value is 2 and strwhere is 3 (which is the correct id - correct record I had opened in my form)

    Hope this makes sense, I am close (I think) and thank you so much for your time, really appreciated.

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    So your subject is

    "Whatever " & Me.ID & " more text"

  7. #7
    AMJADJ is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jul 2018
    Posts
    34
    Yes that would be the code within the email subject bit, however when the button for "Email authorisation" is pressed (which runs the above code), access attempts to open the report, send it to the printer, but whilst doing so, it "bombs out" and quits and restarts.
    I was under the impression acView Preview is print preview without it sending it to the printer. That is where I am at now trying to understand why it is still attempting a print.

    Thank you for your time.

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is there more code? That won't email, but it should preview rather than print the report. Or is there code in the report that might be printing it?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    AMJADJ is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jul 2018
    Posts
    34
    This has now been resolved. The code at the beginning was changed to

    Code:
    strWhere = Me.ID
    DoCmd.OpenReport "Sample Order Despatch Note", acViewPreview, , "ID = " & strWhere
    Thank you all for taking time in helping me.

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Missing stuff in Email Subject line
    By Thompyt in forum Programming
    Replies: 3
    Last Post: 04-13-2016, 02:26 PM
  2. can your report name be sent to an email subject
    By Compufreak in forum Access
    Replies: 8
    Last Post: 11-20-2012, 03:03 AM
  3. VBA check for email subject line
    By problem_addic in forum Access
    Replies: 4
    Last Post: 03-12-2010, 02:33 PM
  4. Field Name into Subject/Body of an email?
    By Stanggirlie in forum Programming
    Replies: 0
    Last Post: 01-05-2009, 11:51 AM
  5. Email field name into subject/body?
    By Stanggirlie in forum Access
    Replies: 0
    Last Post: 01-02-2009, 11:07 AM

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