Results 1 to 15 of 15
  1. #1
    ijo68 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    37

    How to use text box value in the subject line of email

    I am trying to include the value from a text box on a report in the subject line of an email. Can anyone tell me why this does not work.

    Set oOutlook = New Outlook.Application
    End If
    Set oemailItem = oOutlook.CreateItem(olMailItem)
    With oemailItem
    .Subject = [Reports]![Name of Report]![Textbox] & " : " & "Additional string"
    .Body = "String for body of email:"


    .Attachments.Add filepath
    .Display
    End With

    Thank you

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    "Doesn't work" means what? Error message? Nothing seems to happen at all? You get "Additional String" but not the report textbox value?
    Code is where? Report is open when it runs?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ijo68 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    37
    subject line blank.. this is from VBA ... the report is run from a parameter query, (attached as .pdf file in email) and based on the criteria the textbox is populated with a value which I would like to have in the subject line of the email

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    subject line blank..
    Then I guess the email item isn't being created, because at the least, the subject line should contain your text you're calling "additional string"
    this is from VBA
    That is kind of obvious but doesn't tell me what the code is on/behind. I presume a form button click event. I suppose that doesn't matter now if the "additional string" isn't added to the subject line. Can you confirm that the email is being created?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ijo68 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    37
    yes email is created with the attachment ,, just no subject line not even the additional text.. if I remove the [Reports]![Report Name] etc.. it displays the additional string... somehow it can't seem to grab the value from the report text box.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    so this results in nothing in the subject line
    .Subject = [Reports]![Name of Report]![Textbox] & " : " & "Additional string"

    but this results in "additional text" in the subject line
    .Subject = "Additional string"

    If that's what you're saying, then I'm afraid I have no clue.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm not sure you can refer to a report textbox in that way.
    If you can (and I'm not convinced) the report would have to be open, which I can't tell if it is from the bit of code you have supplied.

    Can we see the whole of the code involved?

    EDIT: And I suspect the reason it is completely blank is that within the outlook object it breaks the .Subject line.

    Try using

    Code:
    Dim sSubject as String
    
    sSubject = [Reports]![Name of Report]![Textbox]
    sSubject = sSubject & " : Additional string"
    I suspect you will get an error at the first sSubject = line
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If your code uses Docmd.OutputTo to create the PDF from the report then the report is not open. Why not replace the reference to the report control with a dLookup from the query that is the report's recordsource?

    .Subject = dLookup("[TextboxControlSource]","qryReportRecordSOurce") & " : " & "Additional string" 'assumes the parameter query returns one record for the report

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    ijo68 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    37
    On Error Resume Next
    Dim fileName As String
    Dim filepath As String
    Dim oOutlook As Outlook.Application
    Dim oemailItem As MailItem


    fileName = "Name of File" & " : " & "strGrp"
    filepath = "c:\Reports" & fileName & ".pdf"
    'create temparary file
    DoCmd.OutputTo acOutputReport, "NameofReport", acFormatPDF, filepath
    If oOutlook Is Nothing Then
    Set oOutlook = New Outlook.Application
    End If
    Set oemailItem = oOutlook.CreateItem(olMailItem)
    With oemailItem
    .Subject = [Reports]![NameofReport]![Textbox] & " : " & "Additional String"
    .Body = "StringinBody." & cbcrlf & "Thank You."
    .Attachments.Add filepath
    .Display
    End With
    'delete temparary file
    Kill filepath

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm not sure you can refer to a report textbox in that way.
    You can IF the report is open, but come to think of it, how is code supposed to know which iteration of the control if there are many of them potentially showing a different value per record? In the above code, by the time that the line attempting to get the value executes, the report will be closed. You would have to explicitly open the report, output, then get the value, then close. Again, if it is a field in the detail section, this is probably pointless (without a record ID).
    Last edited by Micron; 02-08-2021 at 06:30 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If your code uses Docmd.OutputTo to create the PDF from the report then the report is not open.
    As I mentioned using Docmd.OutputTo does not open the report so you can reference the textbox; if your paramter query which is the record source of the report only returns one record (like one invoice or order or customer, etc.) then you can leave your code almost like it is and use dLookup as suggested earlier.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Is that really your actual code, you have a report called "NameOfReport" ??
    I'm afraid you appear to have copied and pasted something that was an example somewhere and you are just expecting it to work.

    As Vlad has stated your report isn't open when you are creating the email. So you can't get a value from it.
    I doubt it would be the value you are expecting as Micron has explained.
    The first line of code isn't working as you suspect

    fileName = "Name of File" & " : " & "strGrp"

    This will simply make the value of filename be "Name Of File : strGrp" everytime. The code could simply be

    fileName = "Name of File : strGrp"

    if you have set up a variable or form control called strGrp somewhere you aren't referring to it here.
    The same problem exists in other elements of the code - you are simply adding strings together not pulling in external values.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  13. #13
    ijo68 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    37
    Sorry the .. strGrp .. is not actually part of the Code.. something I tried earlier and didn't remove from the code I pasted. in .. I decided to use an input box for user to place the string manually...since as was stated the report is not open to pull the needed string. and by the way, the names are not the real names ( Name of Report...etc..), sorry for the mix up.. still learning... Thank you all for your input .. hope I can help out some day.

  14. #14
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Don't worry - it is a very common thing for new posters to do, replacing bits of their actual code with things that they think will help.
    Unfortunately more often than not it actually adds to the confusion and means what is presented can't possibly have worked.

    Just copy and paste what you have, warts and all. It will let us spot the typo's and other errors a lot quicker!

    Edit - we won't judge if your report is called Weekly_Gimp_Mask_Sales_To_Unicorns either...
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  15. #15
    ijo68 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    37
    good to know.. Thanks again..

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

Similar Threads

  1. Text Field in Email "Subject" Line
    By MSAccessOldTimer in forum Forms
    Replies: 2
    Last Post: 08-08-2019, 05:42 AM
  2. Add multiple iterations into subject line
    By Thompyt in forum Programming
    Replies: 20
    Last Post: 06-17-2016, 12:30 PM
  3. Missing stuff in Email Subject line
    By Thompyt in forum Programming
    Replies: 3
    Last Post: 04-13-2016, 02:26 PM
  4. Replies: 5
    Last Post: 11-01-2014, 05:18 PM
  5. VBA check for email subject line
    By problem_addic in forum Access
    Replies: 4
    Last Post: 03-12-2010, 02:33 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