Results 1 to 8 of 8
  1. #1
    vad77 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101

    Send Status update via email on a form

    This is what I have and want to do. I have a main form for Work Orders. It contains the Work Order#, dates and the person who created the work order along with their email address. (Tbl_tta, uses query qfrm_main)
    On my main form I have a subform for the Work order items, could be more than one. This form contains items like type of work to be performed and other details to that line item. (Tbl_MAC)
    Then on the subform there is another subform for the Workorder item status. The status can change multiple times and I would like to keep the history of changes.
    What I want is when a tech updates a line item to send an email to the person that created work order. I have search for a few days and looked at all different type of code before I tried my code.
    I created my button with the code below and it crashes, when I hit Debug in the pop-up this line is highlighted. "Dim www As dao.Recordset"


    Edit: The error I get is Run-time error 424, Object required.

    Code:
    Private Sub cmd_SendUpdate_Click()Dim msg_body As String
    Dim msg_subject As String
    Dim msg_to As String
    Dim www As dao.Recordset
    Set www = frm_tta.RecordsetClone
    msg_subject = "Workorder# " & www!WO & " has been updated."
    msg_body = "A workorder you created was recently updated. Please access the workorder in the database."
    msg_to = www!PreEmail
    DoCmd.SendObject acSendNoObject, , , msg_to, , , msg_subject, msg_body
    End Sub
    Here is a screen shot of my forms.
    Any help with this would be greatly appreciated.
    Thanks!

    Click image for larger version. 

Name:	DB_Image_1.JPG 
Views:	26 
Size:	174.3 KB 
ID:	30787

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    On what line? I don't see the need for a recordset, you can just refer to the form:

    msg_subject = "Workorder# " & Me.WO & " has been updated."
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, you posted the line. I'm blind.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    vad77 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    OK, changed my code to your suggestions and that seems to work in bringing up the email message. The only issue is that the email address is entered twice the second time with "mail to:" before the address. See screen shot.
    Could it be due to the fact that the email field is set to hyperlink?
    Also why don't I see my email signature in the body of the email. Is access opening a new email in a different way than if I were to do New Email in Outlook?

    New code

    Code:
    Private Sub cmd_SendUpdate_Click()Dim msg_body As String
    Dim msg_subject As String
    Dim msg_to As String
    msg_subject = "Workorder# " & Forms!frm_tta.WO & " has been updated."
    msg_body = "A workorder you created was recently updated. Please access the workorder in the database."
    msg_to = Forms!frm_tta.PreEmail
    DoCmd.SendObject acSendNoObject, , , msg_to, , , msg_subject, msg_body
    End Sub
    Click image for larger version. 

Name:	db_Image2.JPG 
Views:	23 
Size:	24.9 KB 
ID:	30788

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Lines refer to recordset clone which I also do not see need for. Modify lines as suggested by Paul to just reference form controls/fields.

    Been a while since I used 2007 but might have to declare a DAO library. Also, not correctly setting the recordset object variable. However, when using recordsetclone an object variable is not even necessary. RecordsetClone is a property of the form. Remove use of recordset as suggested and the issue is moot.

    If you are inputting the email as a hyperlink string into a Hyperlink type field then yes, that appears to be an issue. A textbox can be set to display like a hyperlink and not actually be a hyperlink. Either don't use Hyperlink type field or parse the string to extract the email address.
    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.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    vad77 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    Fixed the email address issue. Changed the email field to text in the table and on the form to add to the combo box list.
    Would like to get a resolution to the way Access opens the new email where I don't see the email signature.

  8. #8
    vad77 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    With a little more searching I found the code to add the email signature.

    Code:
    Private Sub cmd_EmailUpdate2_Click()
    
    Dim OApp As Object, OMail As Object, signature As String
    Set OApp = CreateObject("Outlook.application")
    Set OMail = OApp.createitem(0)
     With OMail
     .Display
     End With
     signature = OMail.HTMLBody
     With OMail
     OMail.to = Forms!frm_tta.PreEmail
     OMail.subject = "Workorder# " & Forms!frm_tta.WO & " has been updated."
     OMail.HTMLBody = "Workorder# " & Forms!frm_tta.WO & ", item number " & Forms!frm_tta!qsfrm_MAC_subform.Form!Item & " you created was recently updated. Please access the workorder in the database." & vbNewLine & signature
     OMail.send
     End With
     
    Set OMail = Nothing
    Set OApp = Nothing
    MsgBox "The email has been sent to " & Forms!frm_tta.PreparedBy & "."
    
    
    End Sub

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

Similar Threads

  1. Send automatic email with form
    By matirob in forum Forms
    Replies: 1
    Last Post: 03-19-2016, 09:07 AM
  2. Replies: 3
    Last Post: 02-25-2016, 09:47 AM
  3. Replies: 3
    Last Post: 12-28-2015, 04:11 PM
  4. Replies: 5
    Last Post: 05-07-2014, 09:25 AM
  5. send email from a form
    By maxbre in forum Programming
    Replies: 4
    Last Post: 11-12-2010, 01:43 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