What is the best way to convert a range of cells to a string? I have a function that only takes a string as input so I need to convert the range to a string, while retaining as much of the formatting as possible (i.e. it needs to look like a table or list, not just a string of characters). I've tried working with CStr(), as well as converting from a range to an array and then to a string, but I just get errors.
Edit: Code attempt
Dim email_answer As Integer
email_answer = MsgBox("Do you want to be emailled a copy of this schedule?", vbYesNo)
If email_answer = vbYes Then
Dim wb As Workbook
Dim to_send As Range
to_send = Range("D3", "D10")
If Val(Application.Version) < 14 Then Exit Sub
Set wb = ActiveWorkbook
With wb
MailFromMacWithMail body content:=CStr(to_send), _
mailsubject:="Schedule", _
toaddress:="email address", _
ccaddress:="", _
bccaddress:="", _
attachment:=.FullName, _
displaymail:=False
End With
Set wb = Nothing
End If
I know this question is already almost a year old, but I found a quick solution that works for me: You do have to create a reference to Microsoft Forms 2.0 Object Library to use the DataObject.
You can use the following solution to convert a range to a string in VBA:
Any one of these functions will do it for you.
OR
OR
you could use this function:
which would return a string with linefeed character as range rows separator and pipes ("|") as columns separator
There is a much easier way. Assuming the variable rng is a range, then writing:
rng = Left(rng,Len(rng))
will miraculously turn rng into a string.
To make a comma separated list of cell values in a range:
You could add extra functionality like inserting a semicolon instead of a comma if the row number increases.
To use this function: