Results 1 to 4 of 4
  1. #1
    cbuechner is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    60

    Inputboxes

    I wanted to select the subject line as SET SR then Set SR = CurrentDb.OpenRecordset("SELECT engine_type from routerchain then OlMail.Subject = "New Quote Process Input" & " " & SR!Engine_Type
    This did not select the proper subject line. This selected the first line in the QY. I then added a Inputbox, however, now I have two input boxes and have to enter the number twice.

    If it is possible, I would like to reduce this to just one inputbox. In adiditon, but not absolutely necessary, I would like to add the signature line. When I include the attachment OlMail.Attachments.Add "K:\Public\Quote\Instructions\PDF Instructions.pdf", the signature line disappears.

    Here is the code I have so far

    Public Function CreateRIT_ReportEmail()


    'write the default Outlook contact name list to the active worksheet
    Dim rs As DAO.Recordset
    Dim SR As DAO.Recordset
    Dim OlApp As Object
    Dim OlMail As Object
    Dim ToRecipient As String


    Set OlApp = CreateObject("Outlook.Application")
    Set OlMail = OlApp.createitem(olmailitem)




    Set rs = CurrentDb.OpenRecordset("SELECT Email FROM routerchain WHERE Engine_ID=" & Inputbox("Enter Engine Type"))
    Set SR = CurrentDb.OpenRecordset("SELECT engine_type from routerchain Where Engine_ID=" & Inputbox("Enter Engine Type"))


    Do While rs.EOF = False
    ToRecipient = rs!Email
    OlMail.Recipients.Add ToRecipient
    rs.MoveNext
    Loop


    rs.Close
    Set rs = Nothing


    'fill in Subject field
    OlMail.Subject = "New Quote Process Input" & " " & SR!Engine_Type
    OlMail.Body = "Please Review the Instructions and complete applicable sections"
    OlMail.Attachments.Add "K:\Public\Quote\Instructions\PDF Instructions.pdf"


    'Display the message
    OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it


    End Function

  2. #2
    cbuechner is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    60
    I changed the 2nd input box to confirm, so it is ok. The only part I need now is the signature line.


    Public Function CreateRIT_ReportEmail()


    'write the default Outlook contact name list to the active worksheet
    Dim rs As DAO.Recordset
    Dim SR As DAO.Recordset
    Dim OlApp As Object
    Dim OlMail As Object
    Dim ToRecipient As String


    Set OlApp = CreateObject("Outlook.Application")
    Set OlMail = OlApp.createitem(olmailitem)


    Set rs = CurrentDb.OpenRecordset("SELECT Email FROM routerchain WHERE Engine_ID=" & Inputbox("Enter Part ID"))
    Set SR = CurrentDb.OpenRecordset("SELECT engine_type from routerchain Where Engine_ID=" & Inputbox("Confirm Part ID"))


    Do While rs.EOF = False
    ToRecipient = rs!Email
    OlMail.Recipients.Add ToRecipient
    rs.MoveNext
    Loop


    rs.Close
    Set rs = Nothing


    'fill in Subject field
    OlMail.Subject = "New Quote Process Input" & " " & SR!Engine_Type
    OlMail.Body = "Please Review the Instructions and complete applicable sections"
    OlMail.Attachments.Add "K:\Public\Quote\Instructions\PDF Instructions.pdf"


    'Display the message
    OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it


    End Function

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    to avoid entering part id twice modify your code to something like this


    Code:
    dim PartID as string
    PartID=Inputbox("Enter Part ID")
    
    if isnumeric(partID) then 'ok to proceed
    
        Set rs = CurrentDb.OpenRecordset("SELECT Email FROM routerchain WHERE Engine_ID=" & PartID)
        Set SR = CurrentDb.OpenRecordset("SELECT engine_type from routerchain Where Engine_ID=" & PartID)
        ....
    
    elseif len(partID)<>0 then
    
        msgbox "not a number"
    
    else
    
       msgbox "no part number entered, email aborted
    
    end if
    
    with regards adding a signature - see this link for ideas https://www.slipstick.com/developer/...signature-vba/

  4. #4
    cbuechner is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    60
    That worked, and looking over the signature link.

    Thank you!

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

Similar Threads

  1. Replies: 5
    Last Post: 10-25-2017, 02:41 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