• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

subject line to have the current hour rounded off?

J

Jenn1981

Member
Hi everyone, I hope all is well.
I am sending email using VBA and Outlook.
How can I get the subject line to have the current time rounded off.
For example, if its 2:15pm. I'm looking for the subject line to have 2pm.
Another example. If its 4:15pm the subject line should say 4pm.

Thank you so much in advance.

Jenn:)
 
Hi Jen,

I think, this will help you

MsgBox "last hour " & Application.Floor(Hour(Now()), 2) & ":00 Update"
 
Hi Jenn ,

First , I do not know whether you are looking to round off the time or just truncate it ; the FLOOR function will truncate.

If you are happy with this , it is OK , otherwise you will have to have some more code for the rounding off to the nearest hour.

As far as putting in the AM / PM is concerned , if you take the time part of the NOW function , by using Now - INT(Now) , you can compare it to see if it greater than or equal to 0.5 ; if it is , then use PM , else use AM.

Or else , you can use the FORMAT function , as in :

FORMAT(Now - INT(Now) , "hh:00 AM/PM")

Try this UDF :
Code: 
Public Function Round_up_Time(ByRef input_dt_time As Range)
                Dim inputtime As Double
                Dim onehour As Double
             
                If input_dt_time.Cells.Count > 1 Then
                   Round_up_Time = CVErr(xlErrValue)
                ElseIf input_dt_time = vbNullString Then
                   Round_up_Time = CVErr(xlErrNull)
                Else
                   inputtime = input_dt_time - Int(input_dt_time)
                   onehour = TimeValue("01:00:00")
                   inputtime = Application.WorksheetFunction.MRound(inputtime, onehour)
                   Round_up_Time = Format(inputtime, "hh:00 AM/PM")
                End If
End Function
Narayan
 
Sorry, I have another question. Is there a VBA code that will point to an excel body and put it in the subject line. For example, in my excel file it says 9am update. how can i have it look in my excel file for that subject line.

thank you so much.
 
@Debraj(ex-Roy)
Hi, buddy!
Debraj said:
Right now.. how you are looking at that file and in which cell..
Click to expand...
I'm afraid that you have a little synchro problem... I don't believe (in fact, hope that not) that @Jenn1981 will still be looking at that same cell of that same file... almost 5 hours after!... :rolleyes:
Unless she's very but very concentrated... o_O
And if so, would she be cross-eyed so as to glance both at that cell at that file and at your comment... :eek:
Unless... yes! It's that... she got so cross-eyed after the long dual glance :p
Regards!
 
I figured I create the time formulas in the excel sheet, but I don't know how to insert in the subject line.
I do know how to point in excel and insert in the body. However, how do I point and insert in the subject line.
 
Hi, Jenn1981!
Consider uploading (a) sample file(s) (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.
Regards!
 
Hi Everyone,

Attached is my file. Im trying to get the Cell A1 in my subject line. How can I tell VBA to look in A1 and insert in subject line when sending in outlook?
Thank you so much for your time and help:)
 

Attachments

  • hourtest.xlsx
    13.5 KB · Views: 4
Hi Everyone,
You guys are the best. I still cant get AM /PM in my subject line.
Here is the VBA to send in outlook.

Code: 
Sub Mail_Sheet_Outlook_Body()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Don't forget to copy the function RangetoHTML in the module.
'Working in Excel 2000-2013
  Dim rng As Range 
  Dim OutApp As Object
  Dim OutMail As Object
  
  With Application 
  .EnableEvents = False
  .ScreenUpdating = False
  End With 
  
  Set rng = Nothing 
  Set rng = ActiveSheet.UsedRange
  'You can also use a sheet name
  'Set rng = Sheets("YourSheet").UsedRange
  
  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)
  
  On Error Resume Next
  With OutMail 
  .To = "" 
  .CC = "" 
  .BCC = "" 
  .Subject = " " & Application.Floor(Hour(Now), 1) & ":00  File Update"
  .HTMLBody = RangetoHTML(rng)
  .Send  'or use .Display
  End With 
  On Error GoTo 0 
  
  With Application 
  .EnableEvents = True
  .ScreenUpdating = True
  End With 
  
  Set OutMail = Nothing
  Set OutApp = Nothing
End Sub
 
Last edited by a moderator:
Hi, Jenn1981!

Replace this:
Code: 
Application.Floor(Hour(Now), 1) & ":00 File Update"
by this:
Code: 
Format(Now(),"hh:00 am/pm") File Update"

Regards!
 
Hi, Jenn1981!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top