Problem attaching string value to a variable

[Harris@Z]

I am lost solving what should be a simple problem.

I have the following coding:
sbody = "Dear Customer" & vbCrLf & vbCrLf & _
"Kindly send a Purchase Order for the following"

A debug.print shows:
Dear Customer

Kindly send a Purchase Order document for the following

However when I store the text of sBody in a table, and call the value via a procedure, then a debug.print for sbody is:
"Dear Customer" & vbCrLf & vbCrLf & _
"Kindly send a Purchase Order for the following"

If I call as say, xBody and set sBody = xBody, makes no difference.

The 'string' is stored as a Memo (Long Text). I tried changing the field to Rich Text but neither solution works.

I have numerous places in the database, that when a button is clicked, a specific email message is sent to a client. Instead of storing the body and subject text in the code, want to store in a table and retrieve the appropriate one when the button is clicked.

I have wracked my brain over and over and cannot solve what appears to be a simple problem. Can anyone guide me please?

 

[isladogs]

Your post was moderated.

As you've found out vba code doesn't work as you want in a table.
So you either need to build your email body text dynamically as required or use HTML tags such as <P> </P> in your table text.

The email must also be HTML format.

This example app may be helpful https://www.access-programmers.co.uk/forums/showthread.php?t=293368

Either way, if you are going to do customised email, I would personalise it with your customer's name

 

[Harris@Z]

Thanks so much for your quick response.

I do sort of build the code dynamically, but was hoping to store large parts in a table, e.g., "Dear " & [Customer_Name] & vbCrLf & vbCrLf & "Kindly send a Purchase Order document for the following samples received on " & [Order_received] & ":" & [Product_Name]

HTML sounds like a good option though, but as I am unsure whether all our clients can receive emails in HTML format, the messages are sent in text format only.

I will look at the example you have linked to, thank you!

 

[isladogs]

If your customers can only receive plain text email there would be no point using a rich text memo field for this purpose.

I'm really not convinced about using a table for this.
The example app shows how to build body text (etc) from various concatenated text strings. Let me know if you have any questions

 

[Harris@Z]

Hi Colin,
Thanks for your further input.
The database does work sending emails correctly (thankfully!) The database uses around 10 pre-formatted different messages sent to clients.

Essentially the reason I wanted to store these pre-formatted messages in a table is so that it would be easy to make alterations to them, which would be much easier if stored in a table rather than having to make changes in the vba.

 

[The_Doc_Man]

What I did for partly pre-formatted messages was to have a few string functions to do the building of this using VBA to just concatenate the "boiler plate" and substitute the values of a few simple parameters. Be sure to pass in the string by reference if you take that route.

 

[Gasman]

Hi Colin,
Thanks for your further input.
The database does work sending emails correctly (thankfully!) The database uses around 10 pre-formatted different messages sent to clients.

Essentially the reason I wanted to store these pre-formatted messages in a table is so that it would be easy to make alterations to them, which would be much easier if stored in a table rather than having to make changes in the vba.

You could have a table holding
LetterType
SortOrder
LetterText

and build a letter from that.?
So each record would be a separate paragraph.?
Perhaps have a field for numver of empty lines to space them as ypu want.?

HTH

 

[June7]

I tested your code and it works for me. Post code that pulls value from field. Or provide db for analysis.

 

[Harris@Z]

Thanks for your help!

This is the calling code:

Call GetResponse(ResponseID, Subject, Body)
ResponseID = 1

This is the function:
Function GetResponse(ResponseID As Integer, Subject As String, Body As String)
Dim StmtResponse As String
Dim rsResponse As Recordset

StmtResponse = "SELECT * FROM tblResponses where Response_ID = " & ResponseID
Set rsResponse = CurrentDb.OpenRecordset(StmtResponse)

Subject = rsResponse("Response_Subject")
Body = rsResponse("Response_Body")
rsResponse.Close

End Function

This is the field entry in the table:
"Dear Customer" & vbCrLf & vbCrLf & _
"Kindly send a Purchase Order document for the following samples received on " & Order_received & ":" & vbCrLf & vbCrLf & _
[OrderList] & vbCrLf & vbCrLf & _
"Unfortunately the results cannot be released before receipt of a Purchase Order document." & vbCrLf & vbCrLf & _
"Only full orders will be processed, a full order comprises of the following; (1) sample/s, (2) acceptance note and (3) purchase order." & vbCrLf & vbCrLf & _
"Kind Regards" & vbCrLf & vbCrLf & _
"Angelique"

 

[June7]

Still cannot replicate issue. My table entries have non-printing cr and lf characters, not text & vbCrLf & vbCrLf & _. What do you expect inclusion of [OrderList] in string to accomplish?

Apparently code is working.

Looks like need to provide db.

 

[Harris@Z]

Thanks for your further help, much appreciated.
I have uploaded a database which when opens, shows a form with two buttons.

The one called Variable, loads the string associated with a variable into the text window - showing the formatting without the code.

The second button called Compose from Table, extracts the same string from the table, but shows the code for formatting. Hopefully this gives a clue!

Thanks
Harris

 

[June7]

If that string in table field was created by typing into field or textbox, then yes, the vbCrLf and all special characters (", &, _) will just be actual text as typed. Need to set textbox EnterKeyBehavior property to New Line in Field and that will embed CR and LF characters. Users don't type vbCrLf.

Also, embedded field names are just text, will not be recognized as fieldname references and will not pull values from those fields.

 

[Harris@Z]

I see, thanks so much for your guidance. Was counter-intuitive to me

It is a pity - would have been the best solution for my need. But have rethought this approach and have found a potential work around which is simply to use Replace() and swop out specific 'markers' for the code required, e.g., "& vbCrLF &" for #, and [MyOrder] for " & me.Order_ID &".

Is sort of long winded, but at least allows one to have email message text in table fields rather than having to change throughout the database, when required.

It may also be useful to have these 'markers' as html commands, allowing the same field to be used for html email, as is, and replace() if non-html email. For example, <p></p> to be replaced() by "& vbCrLF &".

Thanks so much for your time!

 

[June7]

HTML codes will be processed when string is used as part of HTMLBody of email message. So can use many tags to format text into table structure or to highlight with color, bold, font, line breaks.

 

[Harris@Z]

Am I correct in the assumption that not all users are able to receive html email messages and hence better to rather use non-html emails to clients?

 

[June7]

I don't know.

I don't think Replace will accomplish what you want.

Replacing text <p><p> with text "& vbCrLF &" is still just text.

As for the field data, don't include & in strings, just spaces as appropriate, then:

Replace(string, "[OrderList]", vbCrLf & Me.OrderList & vbCrLf)