Combining 2 Text Strings in Body of E-Mail Q

[Seanie]

I am trying to create a string of text to place in the message body of
an e-mail. Using Ron De Bruins code I've run in to the "Too many line
continuations". I've a requirement for 31 lines, but it hits this
error on line 24. How can I combine 2 text strings to appear in the
message body of the reports. My code with only the first stringbody
is:-

Sub Mail_New_Version()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

ActiveWindow.TabRatio = 0.908

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

For Each cell In ThisWorkbook.Sheets("Report").Range("BJ1:BJ25")
strbody = strbody & cell.Value & vbNewLine
Next

strbody1 = ThisWorkbook.Sheets("Master").Range("E1").Value & _
ThisWorkbook.Sheets("Master").Range("E2").Value & _
ThisWorkbook.Sheets("Master").Range("E3").Value & _
ThisWorkbook.Sheets("Master").Range("E4").Value & _
ThisWorkbook.Sheets("Master").Range("E5").Value & _
ThisWorkbook.Sheets("Master").Range("E6").Value & " " &
ThisWorkbook.Sheets("Report").Range("B62").Value & _
ThisWorkbook.Sheets("Master").Range("E7").Value & " " &
ThisWorkbook.Sheets("Report").Range("B63").Value & _
ThisWorkbook.Sheets("Master").Range("E8").Value & " " &
ThisWorkbook.Sheets("Report").Range("B64").Value & _
ThisWorkbook.Sheets("Master").Range("E9").Value & _
ThisWorkbook.Sheets("Master").Range("E10").Value & _
ThisWorkbook.Sheets("Master").Range("E11").Value & " " &
ThisWorkbook.Sheets("Report").Range("B33").Value & _
ThisWorkbook.Sheets("Master").Range("E12").Value & " " &
ThisWorkbook.Sheets("Report").Range("B34").Value & _
ThisWorkbook.Sheets("Master").Range("E13").Value & " " &
ThisWorkbook.Sheets("Report").Range("B35").Value & _
ThisWorkbook.Sheets("Master").Range("E14").Value & _
ThisWorkbook.Sheets("Master").Range("E15").Value & _
ThisWorkbook.Sheets("Master").Range("E16").Value & " " &
ThisWorkbook.Sheets("Report").Range("B56").Value & _
ThisWorkbook.Sheets("Master").Range("E17").Value & " " &
ThisWorkbook.Sheets("Report").Range("B57").Value & _
ThisWorkbook.Sheets("Master").Range("E18").Value & " " &
ThisWorkbook.Sheets("Report").Range("B58").Value & _
ThisWorkbook.Sheets("Master").Range("E19").Value & _
ThisWorkbook.Sheets("Master").Range("E20").Value & _
ThisWorkbook.Sheets("Master").Range("E21").Value & " " &
ThisWorkbook.Sheets("Report").Range("B49").Value & _
ThisWorkbook.Sheets("Master").Range("E22").Value & " " &
ThisWorkbook.Sheets("Report").Range("B50").Value & _
ThisWorkbook.Sheets("Master").Range("E23").Value & " " &
ThisWorkbook.Sheets("Report").Range("B51").Value & _
ThisWorkbook.Sheets("Master").Range("E24").Value & " " &
ThisWorkbook.Sheets("Report").Range("B52").Value



With Destwb
On Error Resume Next
With OutMail
.To = ThisWorkbook.Sheets("Master").Range("B1").Value
.CC = ""
.BCC = ""
.Subject = ThisWorkbook.Sheets("Report").Range("B2").Value
.Body = strbody
.ReadReceiptRequested = False
.Importance = 1
.Send
Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys "%S"
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[D_Rennie]

helo.

when sending a email, all you realy want to do is build a text string
and in your case you are creating a large string from many cell over tw
worksheets.
You have fully qualified the code (e
thisworkbook.sheets("shet").range("A1"0.value and that
good though in this case realy hampers the readability of what you ar
doing. you need to make use of WITH statements like below. also to hel
the readibility a little when referencing a cell the .value is th
defult propity used so in a case like this i would leave it out. and yo
are using a lot of line continuations ( _ ) these only realy help th
readiblity and you are limited to the number of lines the code ca
spread so wither pull it into less lines or every now and then break ou
and set the variable like below(i think that makes sence)

well anyhow try the below, at lease it should give you a idear of wha
needs to happen.


Code
-------------------
Sub Mail_New_Version()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim Cl As Range
Dim strbody1 As String

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

ActiveWindow.TabRatio = 0.908

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

For Each Cl In ThisWorkbook.Sheets("Report").Range("BJ1:BJ25")
strbody1 = strbody1 & Cl & vbNewLine
Next Cl
With ThisWorkbook
With .Sheets("Master")
strbody1 = strbody1 & .Range("E1") & .Range("E2") & .Range("E3") & _
.Range("E4") & .Range("E5") & .Range("E6") & " "
End With

strbody1 = strbody1 & .Sheets("Report").Range("B62") & .Sheets("Master").Range("E7") & " " & _
.Sheets("Report").Range("B63") & .Sheets("Master").Range("E8") & " " & .Sheets("Report").Range("B64")


With .Sheets("Master")
strbody1 = strbody1 & .Range("E9") & .Range("E10") & .Range("E11") & " "
End With

strbody1 = strbody1 & .Sheets("Report").Range("B33") & .Sheets("Master").Range("E12") & " " & _
.Sheets("Report").Range("B34") & .Sheets("Master").Range("E13") & " " & .Sheets("Report").Range("B35")

With .Sheets("Master")
strbody1 = strbody1 & .Range("E14") & .Range("E15") & .Range("E16") & " "
End With


strbody1 = strbody1 & .Sheets("Report").Range("B56") & .Sheets("Master").Range("E17") & " " & .Sheets("Report").Range("B57") & _
.Sheets("Master").Range("E18") & " " & .Sheets("Report").Range("B58")


With .Sheets("Master")
strbody1 = strbody1 & .Range("E19") & .Sheets("Master").Range("E20") & .Sheets("Master").Range("E21") & " "
End With


strbody1 = strbody1 & .Sheets("Report").Range("B49") & .Sheets("Master").Range("E22") & " " & .Sheets("Report").Range("B50") & _
.Sheets("Master").Range("E23") & " " & .Sheets("Report").Range("B51") & .Sheets("Master").Range("E24") & " " & .Sheets("Report").Range("B52")



With Destwb
On Error Resume Next
With OutMail
.To = .Sheets("Master").Range("B1")
.CC = ""
.BCC = ""
.Subject = .Sheets("Report").Range("B2")
.Body = strbody1
.ReadReceiptRequested = False
.Importance = 1
.Send
Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys "%S"
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

End With

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Su

 

[Seanie]

Thanks, I'm a bit lost with your code, but doesn't readability matter?
My text is designed to read downwards, not in a paragraph format. I
can understand that continuation lines are limited but I thought
having 2 "string bodies" would get around this for me, but just don't
know how to combine them within this part-

With OutMail
.To = .Sheets("Master").Range("B1")
.CC = ""
.BCC = ""
.Subject = .Sheets("Report").Range("B2")
.Body = strbody1
.ReadReceiptRequested = False
.Importance = 1
.Send
Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys "%S"
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

 

[D_Rennie]

ive done a few very long text strings for use in emails, and yes you ar
correct readibility when constructing these string has proven to be
bit of a contest. i found that if i grouped it in sections (like Compan
headers, References numbers, return sender ect) this way it made it
little easer to edit it. though doing it like you have is quite easy t
read.

the part of the code that you have pointed th
.body is the part of the email that will mak
up the text part.

so you can assign it as many variables as you like. so to join th
variables (strings)
Code
-------------------
.Body = strbody1 & srtBody & "keep joing stuff here
-------------------
its just like you have done in the building of the streBody variable.

also just noted on your code that you have referenced a workbook
destWb
though this variable has not been set and you go on to fully qualifi
the code. there is no point in having the with destWb and end with (it
just aboue and below what you last posted)

good luck, if have have any other troubles, please post a workbook an
ill take a look at it. (rember to remove any senctive information bu
leaving enough (or inserting fake information) to complie the messag
string.

cheer

 

[Seanie]

Thanks, understand now. The Destwb was copied from another (working
code), so I edited and left that in (by mistake), I usually tidy up
when I test the code. Will have a play with this code over the w/e.
Thanks again