formatting string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm issuing a SendObject:

DoCmd.SendObject acSendNoObject, "notify email", , "email address here", , ,
"Automated Sales Call Statistics", strbody

the text message body variable I'm using is defined:
Public strbody as string

and I intialize it with
strbody = ""

when I construct the message, I always add "& vbcrlf" to the end of each
line to force a new line. The problem is that it is wrapping the text on
certain lines; it appears as if it's ignoring the VBCRLF. On other lines it
works fine. If I add two VBCRLF's to the end of each line, it double
spaces...example of wrapping the text is below

Mon 7/24/2006: 1 entries found in 1 leads Fri 7/21/2006: 0 entries found in
0 leads Thu 7/20/2006: 0 entries found in 0 leads Wed 7/19/2006: 3 entries
found in 3 leads Tue 7/18/2006: 3 entries found in 3 leads Mon 7/17/2006: 8
entries found in 4 leads Fri 7/14/2006: 4 entries found in 3 leads

I need it to appear as
Mon 7/24/2006: 1 entries found in 1 leads
Fri 7/21/2006: 0 entries found in 0 leads
Thu 7/20/2006: 0 entries found in 0 leads
Wed 7/19/2006: 3 entries found in 3 leads
Tue 7/18/2006: 3 entries found in 3 leads
Mon 7/17/2006: 8 entries found in 4 leads
Fri 7/14/2006: 4 entries found in 3 leads

other lines in the message look fine and they are all created using:
strbody = strbody & "text message line"

can anyone give me a hint what I'm doing wrong?

thanks in advance for your help!!
 
yes, I do have the & vbcrlf at the end of each line...

I also tried changing the & to a +, it made no difference

is there anything else you can think of?
 
PeterM said:
I'm issuing a SendObject:

DoCmd.SendObject acSendNoObject, "notify email", , "email address
here", , , "Automated Sales Call Statistics", strbody

the text message body variable I'm using is defined:
Public strbody as string

and I intialize it with
strbody = ""

when I construct the message, I always add "& vbcrlf" to the end of
each line to force a new line. The problem is that it is wrapping
the text on certain lines; it appears as if it's ignoring the VBCRLF.
On other lines it works fine. If I add two VBCRLF's to the end of
each line, it double spaces...example of wrapping the text is below

Mon 7/24/2006: 1 entries found in 1 leads Fri 7/21/2006: 0 entries
found in 0 leads Thu 7/20/2006: 0 entries found in 0 leads Wed
7/19/2006: 3 entries found in 3 leads Tue 7/18/2006: 3 entries found
in 3 leads Mon 7/17/2006: 8 entries found in 4 leads Fri 7/14/2006: 4
entries found in 3 leads

I need it to appear as
Mon 7/24/2006: 1 entries found in 1 leads
Fri 7/21/2006: 0 entries found in 0 leads
Thu 7/20/2006: 0 entries found in 0 leads
Wed 7/19/2006: 3 entries found in 3 leads
Tue 7/18/2006: 3 entries found in 3 leads
Mon 7/17/2006: 8 entries found in 4 leads
Fri 7/14/2006: 4 entries found in 3 leads

other lines in the message look fine and they are all created using:
strbody = strbody & "text message line"

can anyone give me a hint what I'm doing wrong?

thanks in advance for your help!!
Click to expand...

From your description, it should work. Please post your code; maybe we
can spot something.
 
is there anything else you can think of?

Offhand, no, but I'm willing to take a look at the situation for you, if you
can send me a compacted & zipped copy of your database. If you are
interested, send me a private e-mail message with your attached .zip file.
You can change any sensitive data to dummy data, as long as the problem can
be repeated in the dummy data.

My e-mail address is available at the bottom of the contributor's page
indicated below. Please do not post your e-mail address (or mine) to a
newsgroup reply. Doing so will only attract the unwanted attention of
spammers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Here's the code, thanks for looking into this, I really appreciate it

Private Sub Command152_Click()
On Error Resume Next
strbody = ""
strbody = "This is an automated email from CDMi. Please do not respond
to this email."
strbody = strbody + vbCrLf + vbCrLf
strbody = strbody + vbCrLf
get_sales_call_review_members
DoCmd.SendObject acSendNoObject, "notify email", ,
"(e-mail address removed)", , , "CDMi Automated Sales Call
Statistics", strbody
End Sub

Private Sub get_sales_call_review_members()
Dim ctr
Dim dbCDMi As Database
Dim Variables As Recordset
Dim errLoop As Error
Set dbCDMi = Application.CurrentDb
Set Variables = dbCDMi.OpenRecordset _
("SELECT variablename FROM variables_CDMi where " _
+ "variabletype = 'sales_call_review_member' order by 1",
dbOpenForwardOnly)

If Variables.EOF = True Then
style = vbOKOnly + vbCritical + vbDefaultButton1 ' Define buttons.
Title = "Cannot Find System Variable" ' Define title.
msg = "The System Variable sales_call_review_member. " + vbCrLf +
vbCrLf + "Contact System Administrator.!"
response = MsgBox(msg, style, Title)
End If
Do Until Variables.EOF = True
Forms!activity_detail!consultant_selected = Variables!VariableName
Command5_Click
strbody = strbody + "=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-"
+ vbCrLf
strbody = strbody + "Call Statistics for: " + Me.consultant_selected
+ vbCrLf
strbody = strbody + status_msg + vbCrLf
For ctr = 0 To Me.all_list.ListCount - 1
strbody = strbody + Me.all_list.ItemData(ctr) + vbCrLf
Next ctr
insert_percentages
strbody = strbody + vbCrLf + vbCrLf
Variables.MoveNext
Loop
End Sub
Private Sub insert_percentages()
strbody = strbody + " Left Voice Message: " +
Format(Me.sales_LVM_percent, "Percent") + vbCrLf
strbody = strbody + " Left No Message: " +
Format(Me.sales_LNM_percent, "Percent") + vbCrLf
strbody = strbody + " Sent Email: " +
Format(Me.sales_sent_email_percent, "Percent") + vbCrLf
strbody = strbody + " Received Email: " +
Format(Me.sales_received_email_percent, "Percent") + vbCrLf
strbody = strbody + "Received Voice Message: " +
Format(Me.sales_RVM_percent, "Percent") + vbCrLf
strbody = strbody + " Spoke With: " +
Format(Me.sales_spoke_with_percent, "Percent") + vbCrLf
strbody = strbody + vbCrLf + vbCrLf

End Sub


this is the resulting strbody
This is an automated email from CDMi. Please do not respond to this email.


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Call Statistics for: John Oberg
148 entries found in 128 leads
averaging 8 leads updated per day
Mon 7/24/2006: 1 entries found in 1 leads Fri 7/21/2006: 0 entries found in
0 leads Thu 7/20/2006: 0 entries found in 0 leads Wed 7/19/2006: 3 entries
found in 3 leads Tue 7/18/2006: 3 entries found in 3 leads Mon 7/17/2006: 8
entries found in 4 leads Fri 7/14/2006: 4 entries found in 3 leads Thu
7/13/2006: 28 entries found in 26 leads Wed 7/12/2006: 30 entries found in 29
leads Tue 7/11/2006: 38 entries found in 28 leads Mon 7/10/2006: 7 entries
found in 6 leads Fri 7/7/2006: 0 entries found in 0 leads Thu 7/6/2006: 7
entries found in 7 leads Wed 7/5/2006: 10 entries found in 9 leads Tue
7/4/2006: 0 entries found in 0 leads Mon 7/3/2006: 0 entries found in 0 leads
Fri 6/30/2006: 9 entries found in 9 leads
Left Voice Message: 81.76%
Left No Message: 2.03%
Sent Email: 8.11%
Received Email: 3.38%
Received Voice Message: 0.68%
Spoke With: 4.05%
 
PeterM said:
Here's the code, thanks for looking into this, I really appreciate it

Private Sub Command152_Click()
On Error Resume Next
strbody = ""
strbody = "This is an automated email from CDMi. Please do not
respond to this email."
strbody = strbody + vbCrLf + vbCrLf
strbody = strbody + vbCrLf
get_sales_call_review_members
DoCmd.SendObject acSendNoObject, "notify email", ,
"(e-mail address removed)", , , "CDMi Automated Sales
Call Statistics", strbody
End Sub

Private Sub get_sales_call_review_members()
Dim ctr
Dim dbCDMi As Database
Dim Variables As Recordset
Dim errLoop As Error
Set dbCDMi = Application.CurrentDb
Set Variables = dbCDMi.OpenRecordset _
("SELECT variablename FROM variables_CDMi where " _
+ "variabletype = 'sales_call_review_member' order by 1",
dbOpenForwardOnly)

If Variables.EOF = True Then
style = vbOKOnly + vbCritical + vbDefaultButton1 ' Define
buttons. Title = "Cannot Find System Variable" ' Define
title. msg = "The System Variable sales_call_review_member. "
+ vbCrLf +
vbCrLf + "Contact System Administrator.!"
response = MsgBox(msg, style, Title)
End If
Do Until Variables.EOF = True
Forms!activity_detail!consultant_selected =
Variables!VariableName Command5_Click
strbody = strbody +
"=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-" + vbCrLf
strbody = strbody + "Call Statistics for: " +
Me.consultant_selected + vbCrLf
strbody = strbody + status_msg + vbCrLf
For ctr = 0 To Me.all_list.ListCount - 1
strbody = strbody + Me.all_list.ItemData(ctr) + vbCrLf
Next ctr
insert_percentages
strbody = strbody + vbCrLf + vbCrLf
Variables.MoveNext
Loop
End Sub
Private Sub insert_percentages()
strbody = strbody + " Left Voice Message: " +
Format(Me.sales_LVM_percent, "Percent") + vbCrLf
strbody = strbody + " Left No Message: " +
Format(Me.sales_LNM_percent, "Percent") + vbCrLf
strbody = strbody + " Sent Email: " +
Format(Me.sales_sent_email_percent, "Percent") + vbCrLf
strbody = strbody + " Received Email: " +
Format(Me.sales_received_email_percent, "Percent") + vbCrLf
strbody = strbody + "Received Voice Message: " +
Format(Me.sales_RVM_percent, "Percent") + vbCrLf
strbody = strbody + " Spoke With: " +
Format(Me.sales_spoke_with_percent, "Percent") + vbCrLf
strbody = strbody + vbCrLf + vbCrLf

End Sub


this is the resulting strbody
This is an automated email from CDMi. Please do not respond to this
email.


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Call Statistics for: John Oberg
148 entries found in 128 leads
averaging 8 leads updated per day
Mon 7/24/2006: 1 entries found in 1 leads Fri 7/21/2006: 0 entries
found in 0 leads Thu 7/20/2006: 0 entries found in 0 leads Wed
7/19/2006: 3 entries found in 3 leads Tue 7/18/2006: 3 entries found
in 3 leads Mon 7/17/2006: 8 entries found in 4 leads Fri 7/14/2006: 4
entries found in 3 leads Thu 7/13/2006: 28 entries found in 26 leads
Wed 7/12/2006: 30 entries found in 29 leads Tue 7/11/2006: 38 entries
found in 28 leads Mon 7/10/2006: 7 entries found in 6 leads Fri
7/7/2006: 0 entries found in 0 leads Thu 7/6/2006: 7 entries found in
7 leads Wed 7/5/2006: 10 entries found in 9 leads Tue 7/4/2006: 0
entries found in 0 leads Mon 7/3/2006: 0 entries found in 0 leads Fri
6/30/2006: 9 entries found in 9 leads Left Voice Message: 81.76%
Left No Message: 2.03%
Sent Email: 8.11%
Received Email: 3.38%
Received Voice Message: 0.68%
Spoke With: 4.05%
Click to expand...

So if I'm reading that correctly, the fault must be in this section of
code:
For ctr = 0 To Me.all_list.ListCount - 1
strbody = strbody + Me.all_list.ItemData(ctr) + vbCrLf
Next ctr
Click to expand...

I take it "all_list" is a list box. I don't see how that could fail if
each row of the list box is really a single string containing one and
only one line in the form "mm/dd/yyyy: n entries found in m leads". Are
you sure that is the case, and there isn't just one row containing all
those lines strung together?

I'd suggest you try stepping through that section of code and examining
the variables and controls involved at every step.

Incidentally, I would use the & operator, rather than +, to ensure a
non-null concatenation result. However, I don't think that has anything
to do with your current problem.

What happens if you put two CR/LFs at the end of each row:

strbody = strbody & Me.all_list.ItemData(ctr) & vbCrLf &
vbCrLf

?
 
Hi Peter,

I recommend commenting out your On Error Resume Next statement at the
beginning of Private Sub Command152_Click(), setting a break point, and
stepping through your code one line at a time using the F8 key.

You have a potential run-time error 13 that could be hidden with your On
Error Resume Next statement. If your reference to the DAO Object Library is
lower in priority versus a reference to the ADO library (assuming that you
have the default reference to the ADO library checked), then this error will
be generated. The easy fix is to declare your recordset explicitely, as in:

Dim Variables As DAO.Recordset

For more information on this topic, please see the following article:
ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/ado_and_dao.html

What is the procedure called by Command5_Click?

It appears as if you may be missing the *very important* two words Option
Explicit as the second line of your code module. The reason I say this is
that it appears as if you are referencing several undeclared variables. For
more information, please see this article:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Even without Option Explicit included, I could not get your code to compile
until I added: Dim Title As String in Private Sub
get_sales_call_review_members().


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Dirk...thanks for responding...

I have stepped thru the code over and over and everything seems ok....If I
add another &vbcrlf at the end of each line it changes the resulting message
to be double spaced. One additoinal thing I noticed is that when the
SendObject statement is called and the email pops us, it is single spaced as
I want it to be, it's when it's delivered to the recpients is that it appears
strung together....
 
Tom...thanks for responding...

I didn't send the entire code because this form is huge...I only included
the relavent code for generating the message...I do have Option Explicit and
Option Compare Database in the code. I did remove the On Error Resume Next
statement and it changed nothing. Title is setup as a Public variable.

I have stepped thru the code over and over and everything seems ok....If I
add another &vbcrlf at the end of each line it changes the resulting message
to be double spaced. One additoinal thing I noticed is that when the
SendObject statement is called and the email pops us, it is single spaced as
I want it to be, it's when it's delivered to the recpients is that it appears
strung together....

Any other ideas?
 
PeterM said:
Dirk...thanks for responding...

I have stepped thru the code over and over and everything seems
ok....If I add another &vbcrlf at the end of each line it changes the
resulting message to be double spaced. One additoinal thing I
noticed is that when the SendObject statement is called and the email
pops us, it is single spaced as I want it to be, it's when it's
delivered to the recpients is that it appears strung together....
Click to expand...

Interesting. I just tried it -- in a simplified form, just
concatenating a series of string literals -- and it worked fine from end
to end. But it seems the problem may be a quirk of your mail client, or
those of your recipients.

What program are you using to send the mail? Outlook, Outlook Express
(which I used), or something else? Are you sending in plain text or
HTML format?
 
I've gotten it narrowed down where the problem is...it's the listbox that is
the source of the string (all_list). All_List is defined as a Value List
ListBox.

Items formatted and added to the listbox are done by:
TEMP_MSG = Format(search_date, "ddd") & " " & search_date & ": " &
Me.total_daily_counter & " entries found in " & total_leads_counter & "
leads"
Me.all_list.AddItem TEMP_MSG, listbox_counter

the resulting listbox entry looks like this:
Tue 7/25/2006: 8 entries found in 7 leads
Mon 7/24/2006: 2 entries found in 2 leads

then I construct the email message body using the string strbody:
For ctr = 0 To Me.all_list.ListCount - 1
strbody = strbody & Me.all_list.ItemData(ctr) & vbCrLf
Next ctr

then issue the sendobject:
DoCmd.SendObject acSendNoObject, "notify email", ,
"(e-mail address removed)", , , "CDMi Automated Sales Call
Statistics", strbody

I'm using Outlook 2003 as the email client. When the message pops up from
the sendobject statement, the message body looks correct:
Tue 7/25/2006: 8 entries found in 7 leads
Mon 7/24/2006: 2 entries found in 2 leads

I send the email to myself and it comes back as
Tue 7/25/2006: 8 entries found in 7 leads Mon 7/24/2006: 2 entries found in
2 leads

I've tried adding & vbcrlf to the listbox entries in all_listbox, no
difference, if I issue & vbcrlf & vbcrlf then the email looks like
Tue 7/25/2006: 8 entries found in 7 leads

Mon 7/24/2006: 2 entries found in 2 leads

the message format is plaintext

this is frustrating
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

WCG Stats Sunday 17 September 2023 3
WCG Stats Thursday 17 August 2023 3
WCG Stats Saturday 16 September 2023 3
WCG Stats Thursday 20 July 2023 3
WCG Stats Friday 11 August 2023 3
WCG Stats Friday 25 August 2023 2
WCG Stats Wednesday 19 July 2023 4
WCG Stats Tuesday 15 November 2022 2

Back
Top