Concatenate onto a new line

[smichaels1234]

I am trying to concatenate onto a new line usine this:

DoCmd.SendObject acSendNoObject, , , "somone@something.com", , , "Records that did not pass through", "These are the Elements that did not pass through" & "Trocar Part #: " & myArray(1) & "Trocar Serial Number: " & myArray(2) & "Shipper #: " & myArray(3) & "Customer Name: " & myArray(4), False

 

[KenHigg]

I'm not sure what your problem is but my guess is you're asking how to get you text to skip to a new line and the answer is to put vbCRLF in the string:

DoCmd.SendObject acSendNoObject, , , "somone@something.com", , , "Records that did not pass through", "These are the Elements that did not pass through" & vbcrlf & "Trocar Part #: " & myArray(1) & "Trocar Serial Number: " & myArray(2) & "Shipper #: " & myArray(3) & "Customer Name: " & myArray(4), False

 

[smichaels1234]

That's exactly what I was looking for. Thanks.

 

[KenHigg]

You're welcome. I have several db's with like email routines and the end users love 'em. If they want to send someone an email on a particular item they just click and all the relevant data is in the body of the email -

 

[pbaldy]

Ken has already solved your issue, but I wanted to throw out a thought. It's a personal preference, but when putting together a more involved email body like that I use a string variable, as I think it makes the SendObject line cleaner and easier to understand. In other words:

Dim strBody As String
strBody = "Blah blah " & vbCrLf & " and more blah"
DoCmd.SendObject..., strBody,...

There's nothing wrong with how you have it, I just wanted to point out another way that for me is easier to read.

 

[KenHigg]

Totally agree... Your docmd.sendobject is a bear -

You can even code your message to mimick the look of the actual email to make it easier to debug / tweak. Here's a pc of one I've done:

    Dim strBody As String
    
    strBody = vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf
    strBody = strBody & "MRO Part Information" & vbCrLf
    strBody = strBody & "=====================================================" & vbCrLf
    strBody = strBody & "            MRO: " & Me.MRO_location & vbCrLf
    strBody = strBody & "           Ship: " & Me.AC_tail_nbr & vbCrLf
    strBody = strBody & "          Visit: " & Me.Visit_nbr & vbCrLf
    strBody = strBody & "         Mfg Pn: " & Me.Mfg_pn & vbCrLf
    strBody = strBody & "            DPN: " & Me.My_pn & vbCrLf
    strBody = strBody & "        Keyword: " & Me.Part_keywd & vbCrLf
    strBody = strBody & "        Created: " & Me.Request_ts & vbCrLf
    strBody = strBody & "       Deadline: " & Me.Deadline_ts & vbCrLf
    strBody = strBody & "   Promise Date: " & Me.Promise_ts & vbCrLf
    strBody = strBody & "            Qty: " & Me.Qty & vbCrLf
    strBody = strBody & "           Unit: " & Me.UOM & vbCrLf
    strBody = strBody & "=====================================================" & vbCrLf & vbCrLf

 

[fearoffours]

ooooh very smart layout Ken. I'd add to your rep if only the forum would let me!

 

[KenHigg]

ooooh very smart layout Ken. I'd add to your rep if only the forum would let me!

That's odd... I wonder if I turn my little green light on if it'll work?

 

[smichaels1234]

Now what I need it to do is loop so that their will be many records that show up. I kind of want to build a table and the records come underneath. Something like this:

Trocar Part Number Trocar Serial Number Shipper Number Customer Name


This is the code that I am using:

Function SeekOnMultiFields()
Dim rstDest1 As Recordset
Dim rstDest2 As Recordset
Dim qdf1 As QueryDef
Dim qdf2 As QueryDef
Dim dbs As Database
Dim myArray(1 To 4) As Variant
Dim strBody As String
Set dbs = CurrentDb()
Set qdf1 = dbs.QueryDefs("qry_GetShipment_To_Populate_TrocarMasterTable")
Set qdf2 = dbs.QueryDefs("qry_TrocarPartSerial")
Set rstDest1 = qdf1.OpenRecordset(dbOpenDynaset)

GetHandler:
qdf2.Parameters("trocarPart") = rstDest1!Item_Number
qdf2.Parameters("trocarSerial") = rstDest1!Lot_Serial_Number
Set rstDest2 = qdf2.OpenRecordset(dbOpenDynaset)
With rstDest2
On Error Resume Next
rstDest2.MoveLast
rstDest2.MoveFirst
If rstDest2.RecordCount > 0 Then
.Edit
rstDest2!Date_Last_Shipped = rstDest1!Ship_Date
rstDest2!Customer_Number = rstDest1!Customer_Number
rstDest2!Ship_to = rstDest1!Ship_to
rstDest2!Standard_Cost_at_First_Shipment = rstDest1!Accum_Material_Cost
.Update
Else
myArray(1) = rstDest1!Item_Number
myArray(2) = rstDest1!Lot_Serial_Number
myArray(3) = rstDest1!Shipper_Number
myArray(4) = rstDest1!Customer_Name
'MsgBox "Record Not Found"
End If
rstDest2.Close
rstDest1.MoveNext
If Not rstDest1.EOF Then
GoTo GetHandler
End If
End With
strBody = "These are the Elements that did not pass through " & vbCrLf & vbCrLf & "Trocar Part #: " & myArray(1) & vbCrLf & "Trocar Serial #: " & myArray(2) & vbCrLf & "Shipper #: " & myArray(3) & vbCrLf & "Customer Name: " & myArray(4)
DoCmd.SendObject acSendNoObject, , , "someone@something.com", , , "Records that did not pass through", strBody, False
End Function

In Red is where I think I need the loop.

 

[fearoffours]

That's odd... I wonder if I turn my little green light on if it'll work?

Nah it's down to the fact that I repped you on something else recently.

 

[KenHigg]

Thanks -