Making a string variable out of the end of a memo field

[Lightwave]

Right - CRM database I have code that takes information from a text box and puts the first line in the subject line of an e-mail.

It then takes the whole field and puts that into the message body of an e-mail but unfortunately this includes the first line


For example

If I type the following into my memo field


Dear John,

Thanks for the note talk to you tomorrow


And the user hits the e-mail button I get


Subject : Dear John,

Message
Dear John,

Thanks for the note talk to you tomorrow

While I would like

Subject : Dear John,

Message
Thanks for the note talk to you tomorrow

Anyone got any ideas how I could automatically parse the memo field in a string variable so that I get everything in a memo field after the first return my code is listed at the bottom of this post. I'm using the split function at the moment to get everything before a return. I think I need to make a loop and loop through all the lines after 0 and build up a string from there…

Any pointers would be appreciated…

[Private Sub Command83_Click()
On Error GoTo Err_Command83_Click

Dim strMessage As String
Dim strSendTo As String
Dim Position As Integer
Dim Tstr As String

Tstr = Me.Details
strSendTo = Me.Combo86.Column(2)
strMessage = Me.Details

Position = 0

DoCmd.SendObject , , , strSendTo, , , (Split(Tstr, vbCrLf)(Position)), strMessage, True

Exit_Command83_Click:
    Exit Sub

Err_Command83_Click:
    'MsgBox Err.Description
    Resume Exit_Command83_Click
    
End Sub

 

[pbaldy]

The first way that comes to mind is the Mid() function along with the InStr() function to find the position of the return (or the comma).

 

[JPaulo]

Hi, I created a small example for you.

 

[Lightwave]

Thanks to both of you.

I ended up using the mid function and instr functions the redone code was as follows.

Private Sub Command83_Click()
On Error GoTo Err_Command83_Click

Dim strMessage As String
Dim strEmailbody As String
Dim strSendTo As String
Dim Position As Integer
Dim Tstr As String
Dim IntGet As Long

Tstr = Me.Details
strSendTo = Me.Combo86.Column(2)
strMessage = Me.Details
IntGet = InStr(strMessage, vbCrLf)
strEmailbody = Mid(strMessage, IntGet)

Position = 0

DoCmd.SendObject , , , strSendTo, , , (Split(Tstr, vbCrLf)(Position)), strEmailbody, True

Exit_Command83_Click:
    Exit Sub

Err_Command83_Click:
    'MsgBox Err.Description
    Resume Exit_Command83_Click
    
End Sub

 

[JPaulo]

Thanks for the feedback, can help others in the future.

 

[bulrush]

Why not have 2 text boxes on your Access form? One for email subject, one for email text.

 

[JPaulo]

It's a good feeling, I have some examples as well, but the friend Lightwave, asked for help in memo field.

Resolved

 

[Lightwave]

Why not have 2 text boxes on your Access form? One for email subject, one for email text.

Perfectly feasible its just that a significant amount of the time the record will not actually be e-mailed forward. Probably 95% of the time. Thus creating a field on the form where there is relatively little space would personally I feel be a bit of a waste.

If the user knows that they are going to e-mail the message then and they know that subsequent to hitting the e-mail button the items before the first return will appear in the subject line they have the option of writing a title should they wish.

Generally I will split grouped data up as much as possible as I like clear distrinct delineation between fields types.