Replacement Code for TextJoin Function

Hi All,

Looking for some help generating a VBA code that replaces the TextJoin function in my Code. I am using a userform submission to open and create a pre-formatted email. After completing the code we releaized that not everyone that was using it was upgraded to Excel 365 and therefore cannot us the TextJoin function. I have posted the code below. Could someone please help generate an alternate formula that would do the same thing? (combine checkbox tags, remove blanks and separate with a comma) Thanks for the help!!


Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hello," & vbNewLine & vbNewLine & _
"A new incident report has been created: " & vbNewLine & _
"Incident Type: " & Me.tb15.Value & vbNewLine & vbNewLine & _
"Facility: " & Me.Facility.Value & vbNewLine & _
"Incident Date: " & Me.IncidentDate.Value & vbNewLine & _
"Incident Time: " & Me.IncidentHour.Value & ":" & Me.IncidentMinute.Value & vbNewLine & vbNewLine & _
"Worker Job Title: " & Me.WorkerJobTitle.Value & vbNewLine & _
"Location of Incident: " & Me.Location.Value & vbNewLine & _
"Incident Description: " & Me.Description.Value & vbNewLine & _
"Description of Injury/Illness: " & Me.Injury.Value & vbNewLine & _
"Onsite First-Aid: " & Me.OnsiteExplain.Value & vbNewLine & _
"Offsite Medical Treatment: " & Me.OffsiteLocation.Value & vbNewLine & vbNewLine & _
"Contributing Factors: " & WorksheetFunction.TextJoin(", ", True, Me.Chemical.Tag, Me.Temperature.Tag, Me.Pressure.Tag, Me.HazardousEnergy.Tag, Me.PoorHousekeeping.Tag, Me.SlipperySurface.Tag, Me.PoorVisibility.Tag, Me.Noisy.Tag, Me.UnevenFloor.Tag, Me.EquipMalfunction.Tag, Me.SharpProtruding.Tag, Me.DamagedEquipment.Tag, Me.LeakDrip.Tag, Me.InsufficientSafetyDevice.Tag, Me.NotFollowingSOP.Tag, Me.Inexperienced.Tag, Me.Rushing.Tag, Me.NotWearingPPE.Tag, Me.PoorTechnique.Tag, Me.InadequateTraining.Tag, Me.PoorDesign.Tag, Me.MissingProcedures.Tag, Me.WorkloadStaffing.Tag, Me.InadequateMaintenance.Tag) & vbNewLine & _
"Immediate Corrective Actions: " & Me.CorrectiveActions.Value & vbNewLine & vbNewLine & _
"Thank you"

On Error Resume Next
With xOutMail
.To = "example email" ''''Change TO Recipient Here
.CC = ""
.BCC = ""
.Subject = "FROI - " & Me.tb15.Value & " - " & Me.Facility.Value ''''Change Subject Line Here
.Attachments.Add
.body = xMailBody
.Display
End With

Thanks for the response, I started with that originally, but couldn't figure out how to omit the blank Tags. The textjoin allowed me to do that.

Thanks AlphaFrog, I am now getting a syntax error, I am guessing that I need to surround that section of code with something. Any suggestion?

Please Login or Register  to view this content.

Thank you AlphaFrog and TMS. It seems to work!!

The following is what I have saved in my toolbox of goodies:

Please Login or Register  to view this content.

Copy that into a regular module and you shouldn't need to change your TextJoin formula at all.