VBA macro to send email. Problem changing time from a number to a text time (.5 to 12:00 pm)

[schappelljr]

I have a macro that I used to send emails. Everything is working but I wanted to add a Start time and end time to the email. The problem that I’m having is the time is showing up as a number and not a time in the email. Example = .5 to 12:00 pm. How do I change the format to a text time?

Sub SendEmail(What_address As String, Subject_line As String, Mail_body As String)
'
' SendEmail Macro
'
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")


    Dim olMail As Outlook.MailItem
    Set olMail = olApp.CreateItem(olMailItem)
    
    olMail.To = What_address
    olMail.Subject = Subject_line
    olMail.Body = Mail_body
    olMail.Display
    


'
End Sub


'


Sub SendMassEmail()
Call Sort_lage_to_small
row_number = 3


Do
DoEvents
    row_number = row_number + 1
    Blank_email = Sheet1.Range("p" & row_number)
    If Blank_email = "0" Then Exit Do
    Dim Mail_body As String
    Dim Store_Name As String
    Dim Retail_date As String
    Dim Start_time As String
    Dim End_time As String
    
    Mail_body = Sheet1.Range("B2")
    Store_Name = Sheet1.Range("J" & row_number)
    Retail_date = Sheet1.Range("B" & row_number)
    Start_time = Sheet1.Range("F" & row_number)
    End_time = Sheet1.Range("H" & row_number)
    
    Mail_body = Replace(Mail_body, "Replace_Name", Store_Name)
    Mail_body = Replace(Mail_body, "Replace_Date", Retail_date)
    Mail_body = Replace(Mail_body, "Replace_StartTime", Start_time)
    Mail_body = Replace(Mail_body, "Replace_EndTime", End_time)
    
  Call SendEmail(Sheet1.Range("p" & row_number), Sheet1.Range("B1"), Mail_body)
 Loop
[COLOR=#000000][FONT=Arial][COLOR=#222222][FONT=Verdana] End Sub[/FONT][/COLOR][/FONT][/COLOR]

 

[MARK858]

Maybe...

Start_time = Format(Sheet1.Range("F" & row_number), "HH:MM AM/PM")

same format for End_time.

 

[schappelljr]

Thank you that worked!