Concatenate cells with line breaks in them

J

jerote

I've been seraching everywhere for a formula that does the following:

Take the name from a cell, concatenate it with a few strings and al
that becomes a single link called "Send Email".

Everything is working fine, but when it comes to the body I need t
concatenate something like:

"Dear, NAME!" then a line break and then a text from another cell. Th
problem comes when I concatenate the text from a cell that has lin
breaks i.e.:

"I am sending you something.

Hope you are well.

Best!

Me"

The formula works fine without this last bit unless the text has no lin
breaks, and I'm banging my head against the wall with this one.

Please HELP!

Thanks a lot
 
L

Living the Dream

Hi

This is the pre-structured email I send out.

Hi,

Please be advised that We will be picking up the following Order(s) :

Vendor : Your Company Name
Load ID : 9876543
PO No(s) : 1234567
Pallet Stack(s) : 22

Bound For : Your Destination

Day : Friday
Date : 18/01/2013
Time : 08:00 Approx

NOTE:
We strive to meet all expected Pick up Arrival times provided although
infrequent events and circumstance outside our control may affect the
Pick up Time

Should you have any issues or concerns on the morning of the pick up
please contact the Fleet Controller
( As early as possible to avoid potential inconveniences ).

Regards,
Transport

This is the I use which you will have to modify it to suit your needs

HTH\
Mick.

Sub sendEmails()

Dim emailaddr As String
Dim cLoad As String
Dim cPO As String
Dim cDay As String
Dim cDat As String
Dim CTime As String
Dim cStacks As String
Dim cDC As String
Dim eVendorsName As String
Dim WEDating As String

For i = 6 To 30000

WEDating = Sheets("SUPPORT DATA").Range("B4").Value
cStatus = Sheets("TMS DATA").Range("B" & i).Value
cLoad = Sheets("TMS DATA").Range("D" & i).Value
eVendorsName = (Sheets("TMS DATA").Range("H" & i).Value)
cDC = Sheets("TMS DATA").Range("K" & i).Value

If cLoad = "" Then
Exit For
End If

go = False
If cStatus = "COMMITED" Then

cPO = Sheets("TMS DATA").Range("E" & i).Value

tn = Now()
cDat = Weekday(tn, vbMonday)
'condition for fridays
If (cDat = 5) Then
cDay = Format((tn + 2) + WEDating, "Dddd")
cDat = Format((tn + 2) + WEDating, "dd/mm/yyyy")
Else
cDay = Format(tn + WEDating, "Dddd")
cDat = Format(tn + WEDating, "dd/mm/yyyy")
End If

'cDat = Format(Sheets("TMS DATA").Range("R" & i).Value, "dd/mm/yyyy")
CTime = Sheets("TMS DATA").Range("AB" & i).Value
cStacks = Sheets("TMS DATA").Range("N" & i).Value

If Sheets("TMS DATA").Range("AF" & i).Value = "" Then
'get email address
cVendorDC = CStr(Sheets("TMS DATA").Range("G" & i).Value)
cVendorName = (Sheets("TMS DATA").Range("H" & i).Value)

found = False
For j = 6 To 30000
If CStr(Sheets("SUPPORT DATA").Range("D" & j).Value) =
cVendorDC Then
found = True
emailaddr = Sheets("SUPPORT DATA").Range("F" & j).Value
If emailaddr = "" Then
MsgBox (Sheets("SUPPORT DATA").Range("E" & j).Value
& " - does not have a valid email, please change and retry")
Exit For
End If
go = True
Exit For
End If
Next j
If found = False Then
MsgBox ("DC Number : " & cVendorDC & Chr(10) & "DC Name : " &
cVendorName & Chr(10) & Chr(10) & " Was not found, please create an
entry in the data sheet")
End If

If go = True Then
Call Module4.sendEmail(emailaddr, eVendorsName, cLoad, cPO,
cStacks, cDC, cDay, cDat, CTime)
Sheets("TMS DATA").Range("AF" & i).Value = "Y"

End If

End If
End If

Next i

End Sub

Sub sendEmail(emailaddr As String, eVendorsName As String, cLoad As
String, cPO As String, cStacks As String, cDC As String, cDay As String,
cDat As String, CTime As String)
' Is working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

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

strbody = "Hi, " & Chr(10) & _
"" & Chr(10) & _
"Please be advised that We will be picking up the following
Order(s) :" & Chr(10) & _
"" & Chr(10) & _
"Vendor : " & eVendorsName & Chr(10) & _
"" & Chr(10) & _
"Load ID : " & cLoad & Chr(10) & _
"PO No(s) : " & cPO & Chr(10) & _
"Pallet Stack(s) : " & cStacks & Chr(10) & _
"Bound For : " & cDC & Chr(10) & _
"" & Chr(10) & _
"Day : " & cDay & Chr(10) & _
"Date : " & cDat & Chr(10) & _
"Time : " & CTime & " Approx" & Chr(10) & _
"" & Chr(10) & _
"NOTE:" & Chr(10) & _
"We strive to meet all expected Pick up Arrival times provided
although infrequent events and " & Chr(10) & _
"circumstance outside our control may affect the Pick up Time" &
Chr(10) & _
"" & Chr(10) & _
"Should you have any issues or concerns on the morning of the
pick up please contact the Fleet Controller" & Chr(10) & _
"( As early as possible to avoid potential inconveniences )" &
Chr(10) & _
"" & Chr(10) & _
"Regards, " & Chr(10) & _
"Transport"

On Error Resume Next
With OutMail
.To = emailaddr
.CC = ""
.BCC = ""
.Subject = "Pick-Ups - " & cDat
.Body = strbody
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub
 
J

jerote

Living said:
Hi

This is the pre-structured email I send out.

Hi,

Please be advised that We will be picking up the following Order(s) :

Vendor : Your Company Name
Load ID : 9876543
PO No(s) : 1234567
Pallet Stack(s) : 22

Bound For : Your Destination

Day : Friday
Date : 18/01/2013
Time : 08:00 Approx

NOTE:
We strive to meet all expected Pick up Arrival times provided although
infrequent events and circumstance outside our control may affect the
Pick up Time

Should you have any issues or concerns on the morning of the pick up
please contact the Fleet Controller
( As early as possible to avoid potential inconveniences ).

Regards,
Transport

This is the I use which you will have to modify it to suit your needs

HTH\
Mick.

Sub sendEmails()

Dim emailaddr As String
Dim cLoad As String
Dim cPO As String
Dim cDay As String
Dim cDat As String
Dim CTime As String
Dim cStacks As String
Dim cDC As String
Dim eVendorsName As String
Dim WEDating As String

For i = 6 To 30000

WEDating = Sheets("SUPPORT DATA").Range("B4").Value
cStatus = Sheets("TMS DATA").Range("B" & i).Value
cLoad = Sheets("TMS DATA").Range("D" & i).Value
eVendorsName = (Sheets("TMS DATA").Range("H" & i).Value)
cDC = Sheets("TMS DATA").Range("K" & i).Value

If cLoad = "" Then
Exit For
End If

go = False
If cStatus = "COMMITED" Then

cPO = Sheets("TMS DATA").Range("E" & i).Value

tn = Now()
cDat = Weekday(tn, vbMonday)
'condition for fridays
If (cDat = 5) Then
cDay = Format((tn + 2) + WEDating, "Dddd")
cDat = Format((tn + 2) + WEDating, "dd/mm/yyyy")
Else
cDay = Format(tn + WEDating, "Dddd")
cDat = Format(tn + WEDating, "dd/mm/yyyy")
End If

'cDat = Format(Sheets("TMS DATA").Range("R" & i).Value
"dd/mm/yyyy")
CTime = Sheets("TMS DATA").Range("AB" & i).Value
cStacks = Sheets("TMS DATA").Range("N" & i).Value

If Sheets("TMS DATA").Range("AF" & i).Value = "" Then
'get email address
cVendorDC = CStr(Sheets("TMS DATA").Range("G" & i).Value)
cVendorName = (Sheets("TMS DATA").Range("H" & i).Value)

found = False
For j = 6 To 30000
If CStr(Sheets("SUPPORT DATA").Range("D" & j).Value) =
cVendorDC Then
found = True
emailaddr = Sheets("SUPPORT DATA").Range("F" & j).Value
If emailaddr = "" Then
MsgBox (Sheets("SUPPORT DATA").Range("E" & j).Valu

& " - does not have a valid email, please change and retry")
Exit For
End If
go = True
Exit For
End If
Next j
If found = False Then
MsgBox ("DC Number : " & cVendorDC & Chr(10) & "DC Name : " &
cVendorName & Chr(10) & Chr(10) & " Was not found, please create an
entry in the data sheet")
End If

If go = True Then
Call Module4.sendEmail(emailaddr, eVendorsName, cLoad, cPO,
cStacks, cDC, cDay, cDat, CTime)
Sheets("TMS DATA").Range("AF" & i).Value = "Y"

End If

End If
End If

Next i

End Sub

Sub sendEmail(emailaddr As String, eVendorsName As String, cLoad As
String, cPO As String, cStacks As String, cDC As String, cDay As String

cDat As String, CTime As String)
' Is working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

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

strbody = "Hi, " & Chr(10) & _
"" & Chr(10) & _
"Please be advised that We will be picking up the following
Order(s) :" & Chr(10) & _
"" & Chr(10) & _
"Vendor : " & eVendorsName & Chr(10) & _
"" & Chr(10) & _
"Load ID : " & cLoad & Chr(10) & _
"PO No(s) : " & cPO & Chr(10) & _
"Pallet Stack(s) : " & cStacks & Chr(10) & _
"Bound For : " & cDC & Chr(10) & _
"" & Chr(10) & _
"Day : " & cDay & Chr(10) & _
"Date : " & cDat & Chr(10) & _
"Time : " & CTime & " Approx" & Chr(10) & _
"" & Chr(10) & _
"NOTE:" & Chr(10) & _
"We strive to meet all expected Pick up Arrival times provided
although infrequent events and " & Chr(10) & _
"circumstance outside our control may affect the Pick up Time" &

Chr(10) & _
"" & Chr(10) & _
"Should you have any issues or concerns on the morning of the
pick up please contact the Fleet Controller" & Chr(10) & _
"( As early as possible to avoid potential inconveniences )" &
Chr(10) & _
"" & Chr(10) & _
"Regards, " & Chr(10) & _
"Transport"

On Error Resume Next
With OutMail
.To = emailaddr
.CC = ""
.BCC = ""
.Subject = "Pick-Ups - " & cDat
.Body = strbody
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Click to expand...

Thanks a lot for the fast reply. I believe the information you sent has
something to do with a macro or similar programming language?

Is there a simpler way to just have the function or formula (the
hyperlink created in a cell) read the cell with the complete text from
another cell in the same file, different sheet? I know the problem is
the spaces in between.
This is because I need to send out several emails with the same info but
would like it to fill the email without having to copy/paste every
time.

Thanks again!

J
 
L

Living the Dream

I'm not aware of a cell function that will do it.

That said, I'm not a Guru either.

It maybe easier to drop your workbook (minus any sensitive data)onto a
file sharing site where we can access the file and do the programming
for you.

HTH
Mick.
 
C

Claus Busch

Hi,

Am Tue, 15 Jan 2013 10:20:33 +0000 schrieb jerote:
"Dear, NAME!" then a line break and then a text from another cell. The
problem comes when I concatenate the text from a cell that has line
breaks i.e.:

"I am sending you something.

Hope you are well.

Best!

Me"
Click to expand...

if you concatenate with VBA the line breaks still remain.
"Dear! in A1
The name in B1
The string with line breaks in A2
Then try:

Sub Test()
Dim myStr As String

myStr = [A2]
myStr = [A1] & " " & [B1] & "," & Chr(10) & myStr
[A3] = myStr
End Sub


Regards
Claus Busch
 

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

Macro to concatenate with previous bold cell 0
Word Mail Merge - keeping paragraph breaks that appear in original Excel cell data 2
Constructing Range Name calls with Concatenate 4
Concatenate and Line Breaks 1
Concatenate many cells 8
concatenate values 4
Concatentate text and insert blank line 2
Generating Functions with "Concatenate" 2

Top