Results 1 to 8 of 8
  1. #1
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70

    Email report to value (email address) in a field

    Our school district provides Thinkpad's to approximately 20,000 students. I have a database that pulls student records from an AS400 and a SQL database, and allows the student to enter a repair request in through Access. The student selects their name, and Access fills in all their student information from the linked databases. The student then enters the problem they are having and submits this via email to the tech for their school.



    I don't want the student to be able to view the report because it will contain their student ID, which needs to remain private. The student, of course, knows their student ID, but other students standing nearby will be able to see it.

    I need a button at the bottom of the form (frmRepairRequest) to email a report (rptRepairRequest) to the school tech. The report is based on a query (qry123) that contains a field with the tech's email address (fldTechEmail). After the email is send, a message box (which I know how to code) will display a blurb about "your request has been sent, etc.". Clicking "Ok" will return the student to the name selection screen for the next student.

    I'm sure it's possible to do this, but I'm in over my head in knowing how. Any help you can give me will be very much appreciated.

    Thanks.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    One approach is the SendObject method. You can either build a string with all the info as the email body or have an open report object sent as an attachment.

    This is a common topic in forum.
    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.

  3. #3
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70

    Still having trouble getting the code to work

    I have spent most of the morning looking through the forums and trying different solutions, but to no avail. Out of desperation, I thought I'd create a macro. I can get it to work if I hard code the email address in, but the email address is dependent upon which school a student is at. Is it possible to grab a field in a macro and use that variable to populate the email address? Ideally, I would like to do this with vba from the form and not mess with a macro, but I'd be THRILLED if I could get either method to work!

    The report I'm attempting to send is based on data collected in a temporary table (the contents of which are deleted every time a student enters a new ticket). The report is rpt_RepairRequest. The temporary table is tbl_temp. The field containing the email address is TechEmail.

    Within the form, clicking the button at the bottom of the screen writes the info to a temporary table (via vba ... this part works), then I need it to send an email to a specific email address ... and I am having NO luck figuring out how to do that.

    I can usually tinker with existing code and get things to work, but I am not a programmer, so it's trial and error, and at this point, I'm completely lost.

    Thanks again for your help!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Certainly can grab email address from a form. Code in Button click event like:

    DoCmd.SendObject acSendReport, "rpt_RepairRequest", acFormatPDF, Me!TechEmail, "something", "something"

    Without knowing more about your data structure and form design, can't be more specific.
    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.

  5. #5
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70
    Thank you!!!! That works, but with one small caveat ... it opens the email rather than just sending it. How do I tell it to send rather than open the email in Outlook?

  6. #6
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70
    Also, the subject and message box should be a generic statement each time. How do I include them?

  7. #7
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70
    One more thing, and then it would be perfect ... can the subject line be the ticket number (TicketNum) and student name (StudentName)? The subject can be a generic "Report attached."

  8. #8
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70
    I got it to work! THANK YOU!!!!!!!!!!!!!!!!!

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

Similar Threads

  1. strEmai=.... - how to pick up Email Address field
    By Baldeagle in forum Programming
    Replies: 11
    Last Post: 08-05-2014, 03:07 PM
  2. Replies: 1
    Last Post: 05-01-2014, 11:37 AM
  3. Replies: 1
    Last Post: 07-16-2013, 12:16 PM
  4. email address field opens in Outlook?
    By pbouk in forum Forms
    Replies: 1
    Last Post: 06-04-2013, 03:29 PM
  5. Prompt for email address on field
    By burrina in forum Forms
    Replies: 9
    Last Post: 01-20-2013, 06:55 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