Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sendobject (A2k)

    Sorry for my posts being so close together.

    Using send object, is it possible to send data from a query:

    [pre] Select Case Neg
    Case "KB"
    Email = "Kate@someaddress.co.uk"
    Case "KF"
    Email = "Kev@someaddress.co.uk"
    Case "DS"
    Email = "Debbie@someaddress.co.uk"
    Case "DT"
    Email = "Diane@someaddress.co.uk"
    Case "MR"
    Email = "Malcolm@someaddress.co.uk"
    Case "AC"
    Email = "Alex@someaddress.co.uk"
    Case "AH"
    Email = "Angela@someaddress.co.uk"
    Case Else
    Email = ""
    End Select

    DoCmd.SendObject , , , Email, , , Me.[EST_NO] & _
    "/" & Me.[SUP_NO] & " Delay Enquiry :- " & "(Date - Time :- " & Now() & ")", Me.[EST_NO] & "/" & Me.[SUP_NO] & vbCrLf & _
    Me.[INS_NME] & vbCrLf & _
    Me.[OWN_NME] & vbCrLf & _
    ************************************************** *************
    INSERT HERE NEW PARTS SELECTED FROM A QUERY
    ************************************************** *************pre]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sendobject (A2k)

    You could open a recordset on a query, loop through its records and assemble a string to be used as message text.
    If that is not what you want, please explain.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sendobject (A2k)

    Hans

    Thats exactly what I wanted, can you help on the syntax ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sendobject (A2k)

    Using DAO (set a reference to the Microsoft DAO 3.6 Object Library):

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strMsg As String

    Set dbs = CurrentDb
    ' Substitute correct query name
    Set rst = dbs.Open("qrySomething", dbOpenDynaset)

    Do While Not rst.EOF
    ' Modify as needed, substituting correct field names
    strMsg = strMsg & "Part: " & rst!SomeField & ", " & rst!OtherField & vbCrLf
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sendobject (A2k)

    Hans

    Sorry I didn't come back earlier on this, I was exploring different possibilities.

    One method which is close to working is as follows:

    I've added a hidden listbox set to "value" which I can populate from the subform by dblClicking the item:
    <pre>Me.Parent.lstPart.AddItem [ITM_DES]</pre>


    I'm sure I can loop through this and populate the MsgText of the E.Mail.

    I'm struggling though to fill more than one column ie:

    <pre>Me.Parent.lstPart.AddItem [ITM_DES] & vbTab & [QTY]</pre>


    I've tried various ways to do this to no avail !

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sendobject (A2k)

    If you want the user to be able to select individual records, I would use a multi-select list box (with several columns) instead of a subform to display the records. You can easily loop through the selected items in the list box.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sendobject (A2k)

    example:

    <pre>SELECT PRT.EST_NO, PRT.SUP_NO, PRT.ITM_DES, PRT.QTY FROM PRT WHERE _
    (((PRT.EST_NO)=forms!frmPartEnq!EST_NO) And ((PRT.SUP_NO)=forms!frmPartEnq!SUP_NO)); </pre>


    <pre>MsgBox Me.lstPart.Column(2) & " " & Me.lstPart.Column(3)</pre>


    <img src=/S/wink.gif border=0 alt=wink width=15 height=15> Gives me a good start <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sendobject (A2k)

    Hans, i'm stuck on the code a little.
    I get one item only, I know its in the loop

    <pre>Dim n As Integer
    Dim Prt As String
    For n = 0 To lstPart.ListCount - 1
    If lstPart.Selected(n) Then

    DoCmd.SendObject , , , Email, , , Me.[EST_NO] & _
    "/" & Me.[SUP_NO] & " Part Enquiry :- " & "(Date - Time :- " & Now() & ")", Me.[EST_NO] & "/" & Me.[SUP_NO] & vbCrLf & _
    Me.[REG] & " " & Me.[VEH_MAK] & " " & Me.[VEH_MOD] & vbCrLf & vbCrLf & _
    "Please Supply The Following Parts:" & vbCrLf & lstPart.ItemData(n)

    End If
    Next n</pre>


  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sendobject (A2k)

    You have put the SendObject within the loop, so there will be a separate mail for each selected item. If you want one mail for all selected items, use

    Dim strMsg As String
    strMsg = Me.[EST_NO] & "/" & Me.[SUP_NO] & vbCrLf & _
    Me.[REG] & " " & Me.[VEH_MAK] & " " & Me.[VEH_MOD] & vbCrLf & vbCrLf & _
    "Please Supply The Following Parts:"
    For n = 0 to lstPart.ListCount - 1
    If lstPart.Selected(n) Then
    strMsg = strMsg & vbCrLf & lstPart.ItemData(n)
    End If
    Next n
    DoCmd.SendObject , , , Email, , , Me.[EST_NO] & _
    "/" & Me.[SUP_NO] & " Part Enquiry :- " & "(Date - Time :- " & Now() & ")", strMsg

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sendobject (A2k)

    Thank you Hans, works like a dream.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •