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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
is the email contents specific to a person?
I will send it to a different person, but I just need to organize it once I run the macro like the ouput image below regardless on how I type it on the cell value.
 
Upvote 0
If you keep hiding the information from us and (worse) give us wrong information no one of us will be able to help...

I know (from this other thread: VBA - Automatically Break the Line in HTML format.) that you populate your variables from cells and some of them contains multilines; I still don't know how the original information are organized, but I can come with this penultimate guess: add this block in this position:
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
NB: I know that the line marked *** is intentionally wrong
 
Upvote 0
If you keep hiding the information from us and (worse) give us wrong information no one of us will be able to help...

I know (from this other thread: VBA - Automatically Break the Line in HTML format.) that you populate your variables from cells and some of them contains multilines; I still don't know how the original information are organized, but I can come with this penultimate guess: add this block in this position:
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
NB: I know that the line marked *** is intentionally wrong
Hi wow, this is actually works. Sorry for the bad execution of the code, I'm in rush that's why I can't focus on this. But this is actually work except on the data on Reminder 1. I appreciate your help about my concern. Thank you very much!!

It stays on 1 line and the other data is not included on the sort.

This is the current output.

1681481060542.png

This is the perfect result that I need.
1681481157337.png
 
Upvote 0
This meands that what I guessed from the other thread is not sufficient; or that from there to here your data has changed.
For any further guess I need to know:
-from where Reminder1 receive the information
-how that info is organized within the cell
An image of that part of the worksheet will be sufficient for now (for now)
 
Upvote 0
This meands that what I guessed from the other thread is not sufficient; or that from there to here your data has changed.
For any further guess I need to know:
-from where Reminder1 receive the information
-how that info is organized within the cell
An image of that part of the worksheet will be sufficient for now (for now)
The info is organized once the user enter a clarification on a dev like for example.

The real cell value was in L2 and the entered data is:
04/11 - with clarification to dev1
04/10 - with clarification to dev2

And this data is in different worksheet but I can do it on others once I completed it on 1 worksheet.

The logic I need is um regardless on how many the data entered on the cell value once I run the macro it will actually arrange properly based on the Month & Day. I just need on how to do the logic.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

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