Sort multiple cell value from oldest to newest by month & day

Attrazion

New Member
Joined
Mar 11, 2023
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hi everyone. I just want to ask for some help regarding my problem. [I put every comments that need attention.]
VBA Code:
Sub SendReminderEmail()


Dim OutApp As Object, OutMail As Object

Dim Reminder1 As String
Dim Reminder2 As String
Dim Reminder3 As String
Dim Reminder4 As String
Dim Reminder5 As String


'Here is the sample data but in reality this is fetching a data from my confidential excel file and the cell value has a multiple entered data like the data on Reminder1
'I also want to organize it using the first 5 characters like "mm/dd".

Reminder1= "04/07 - To attend meetings" & "04/02 - To attend meetings" & "04/01 - To attend meetings"
Reminder2= "04/06 - To attend church"
Reminder3= "04/03 - To attend family gatherings"
Reminder4= "04/02 - To attend friend gatherings"
Reminder5= "04/13 - To attend sports I like"


'Dim OutApp As New Outlook.Application
'Dim OutMail As Outlook.MailItem

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

strBodyTable = Reminder1 & "<br>" & Reminder2 & "<br>" & Reminder3 & "<br>" & Reminder4 & "<br>" & Reminder5


With OutMail
.To = "Test To"
.Subject = "Test Subject" & " " & TodayDate
.CC = "Test CC"
.Display 


.HTMLBody = Replace(strBodyTable, Chr(10), "<br>", , , vbTextCompare) & .HTMLBody


End With

Set OutApp = Nothing
Set OutMail = Nothing

End Sub

The output should be like this when I run the macro.
1681399702144.png
 
Hi @Anthony47. Thank you so much. I already solved my problem with these.
VBA Code:
strBodyTable = Reminder1 & "<br>" & Reminder2 & "<br>" & Reminder3 & "<br>" & Reminder4 & "<br>" & Reminder5      '*** SEE TEXT
'NEW BLOCK>>:
strBodyTable = Replace(strBodyTable, Chr(10), "<br>", , , vbTextCompare)
Dim nArr
nArr = Split(strBodyTable, "<br>", , vbTextCompare)
nArr = Application.WorksheetFunction.Sort(nArr, , , True)
strBodyTable = Application.WorksheetFunction.TextJoin("<br>", True, nArr)
'<< END OF NEW BLOCK
With OutMail
'etc
'etc
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Just in time! (I was writing to ask for some investigation)


Thank you so much. I already solved my problem with these.
And which is the difference with the code I suggested?
 
Upvote 0
Just in time! (I was writing to ask for some investigation)



And which is the difference with the code I suggested?
It helped me a lot, it also remove the excess blank if the value is blank. There's a lot of difference but I think I just put your code on the right part of the code and boom it works properly. Thank you so much!!
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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
Back
Top