Time Format Issue (Email message body)

[Vacuum Sealed]

Hi all

The code below sends SMS-Emails to staff members, up til today, the start
times were untered into the relavent column manually where the cells in
questions were formatted as Text.

I have combined 2 seperate processes so that the time is automatically
inserted, BUT..!!!!!

The time being inserted is formated "h:mm AM/PM".

The problem I have is that when I send the SMS-Emails off, the Time converts
back to a numeric value so instead of 7:00 AM, it is displaying eg
0.0177777766666666......

I tried to format the smsStartTime using the following.

mySMStartTime = Format(smsStartTime, "h:mm AM/PM"), alas this also produced
the same numeric result.



Sub sendEmail(smsAddress As String, smsWorkDay As String, smsName As String,
mySMStartTime As String, smsMessage As String)
' Is working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

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

strbody = "Hi " & smsName & " " & mySMStartTime & " start " & smsWorkDay
& " morning please ...{END}"

On Error Resume Next
With OutMail
.To = smsAddress
.CC = ""
.BCC = ""
.Subject = "Start Time"
.Body = strbody
.Display
'.send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Sub sendEmails()

Dim smsAddress As String
Dim smsWorkDay As String
Dim smsName As String
Dim smsStartTime As String
Dim smsMessage As String
Dim mySMStartTime As String

mySMStartTime = Format(smsStartTime, "h:mm AM/PM")

For i = 4 To 29

smsAddress = Sheets("Main").Range("G" & i).Value
smsWorkDay = Sheets("Main").Range("E1").Value
smsName = Sheets("Main").Range("F" & i).Value
smsStartTime = Sheets("Main").Range("E" & i).Value
smsMessage = Sheets("Main").Range("H" & i).Value

' If smsAddress = "" Then
' MsgBox (Sheets("Main").Range("G" & i).Value & " - does not have a valid
email, please change and retry")
' Exit For
' End If

If smsMessage = "Y" Then
Call sendEmail(smsAddress, smsWorkDay, smsName, mySMStartTime, smsMessage)
Sheets("Main").Range("I" & i).Value = "Y"
End If

Next i

End Sub

Appreciate the assist
Mick.

 

[Don Guillett]

Try converting your time to a STRING

 

[Ron Rosenfeld]

Hi all

The code below sends SMS-Emails to staff members, up til today, the start
times were untered into the relavent column manually where the cells in
questions were formatted as Text.

I have combined 2 seperate processes so that the time is automatically
inserted, BUT..!!!!!

The time being inserted is formated "h:mm AM/PM".

The problem I have is that when I send the SMS-Emails off, the Time converts
back to a numeric value so instead of 7:00 AM, it is displaying eg
0.0177777766666666......

I tried to format the smsStartTime using the following.

mySMStartTime = Format(smsStartTime, "h:mm AM/PM"), alas this also produced
the same numeric result.

Can you try something like:

"Hi " & smsName & " " & CStr(mySMStartTime) & " start " & smsWorkDay & " morning please ...{END}"

 

[Vacuum Sealed]

Thx for replying Guy's

Tried CStr() but it also returned the numeric value.

So, instead I went straight to the source cells and changed them to TEXT and
instead of entering the time as "h:mm", I use "0.00" and then combine
another Cell so that I get "0.00am" or pm, whichever fits the IF statement.

So now it all works perfectly.

Thats again.

Cheers
Mick.

 

[Ron Rosenfeld]

Thx for replying Guy's

Tried CStr() but it also returned the numeric value.

So, instead I went straight to the source cells and changed them to TEXT and
instead of entering the time as "h:mm", I use "0.00" and then combine
another Cell so that I get "0.00am" or pm, whichever fits the IF statement.

So now it all works perfectly.

Thats again.

Cheers
Mick.

Glad you got it sorted. Thanks for the feedback.