Concatenate

RLPeloquin

New Member
Joined
Jul 4, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
The code works fine but I'd like to Concatenate certain cells to put together on same line. As it is stands now when email is sent each cell is on the next line. Here's the code I'm having problems with. Thanks in advance!!
strBody = "Here is a copy of your power usage: " & RangeConc(ActiveSheet.Range("F1,F2,G3,H3,A1,A2,A3,A4,A5,C7,C8,C9,C10"))' I want to concatenate some of these cells.

Public Function RangeConc(ByRef argRange As Range) As String

If Not argRange Is Nothing Then
For Each c In argRange
sTmp = sTmp & c.Text & vbCrLf
Next c
RangeConc = sTmp
End If
End Function
Sub Send_Email()
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim c As Range
Dim sTmp As String
strSubject = "Results from Excel Spreadsheet"
strFrom = "email"
strTo = "email"
strCc = ""
strBcc = ""
strBody = "Here is a copy of your power usage: " & RangeConc(ActiveSheet.Range("F1,F2,G3,H3,A1,A2,A3,A4,A5,C7,C8,C9,C10"))

Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send
Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

End Sub
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

RLPeloquin

New Member
Joined
Jul 4, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
Or can you copy rows 1 thru whatever and still remain the same format as it was before sending
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top