Help with coding

[zoog25]

Hello all. Here is my situation, i am trying to link excel with outlook. I've been able to get it to pretty much do what I want it to do but i'm having problems with one area. Column D in my spreadsheet holds the date of each project is due and Column E holds the time at which each project is due. Now column has three possibilites, blank, filled, or EOD (END OF DAY). The following is my code with the problem area in red.

Option Explicit


Sub AddToOutlook()


Dim OL As Outlook.Application
Dim olAppt As Outlook.AppointmentItem
Dim NS As Outlook.Namespace
Dim colItems As Outlook.Items
Dim olApptSearch As Outlook.AppointmentItem
Dim r As Long, i As Long, sSubject As String, sBody As String, sLocation As String
Dim dStartTime As Date, dEndTime As Date
Dim sSearch As String, bOLOpen As Boolean


On Error Resume Next
Set OL = GetObject(, "Outlook.Application")
bOLOpen = True


If OL Is Nothing Then
    Set OL = CreateObject("Outlook.Application")
        bOLOpen = False
End If


Set NS = OL.GetNamespace("MAPI")
Set colItems = NS.GetDefaultFolder(olFolderCalendar).Items


r = Range("B" & Rows.Count).End(xlUp).Row


For i = 16 To r
    If Range("B" & i).Value = "X" Then
        sSubject = Range("A" & i).Value & " " & ":" & " " & Range("G" & i).Value
        sBody = ""
        sLocation = Range("H16").Value


        'Problem Area
        If Range("E" & i).Value = "" Then
            dStartTime = Range("D" & i).Value & "5:00PM"
            dEndTime = Range("D" & i).Value & "5.00PM"
        ElseIf Range("E" & i).Value = "EOD" Then
            dStartTime = Range("D" & i).Value & "5:00PM"
            dEndTime = Range("D" & i).Value & "5.00PM"
        Else
            dStartTime = Range("D" & i).Value & Range("E" & i).Value
            dEndTime = Range("D" & i).Value & Range("E" & i).Value
        End If
        
        If olApptSearch Is Nothing Then
            Set olAppt = OL.CreateItem(olAppointmentItem)
            olAppt.Body = sBody
            olAppt.Subject = sSubject
            olAppt.Location = sLocation
            olAppt.Start = dStartTime
            olAppt.End = dEndTime
            olAppt.Categories = "BID"
        olAppt.Close olSave
        End If


        If bOLOpen = False Then OL.Quit
        Range("B" & i).Value = " "
    End If
Next i


End Sub

As you can see the start and end date are on column D. and when i set it too that then i get an entry of start and end date being ok but start and end time being 12:00 am. How do i get the info from column E to change it so that time either shows what is in column E or 5:00 PM which is the end of the work day here.

 

[AlphaFrog]

Dates and Time are numeric values. Use a plus (+) operator to combine them instead of the ampersand (&) which is used for text.

        If Range("E" & i).Value = "" Then
            dStartTime = Range("D" & i).Value [COLOR=#ff0000]+ #5:00:00 PM#[/COLOR]
            dEndTime = Range("D" & i).Value [COLOR=#ff0000]+ #5:00:00 PM#[/COLOR]
        ElseIf Range("E" & i).Value = "EOD" Then
            dStartTime = Range("D" & i).Value [COLOR=#ff0000]+ #5:00:00 PM#[/COLOR]
            dEndTime = Range("D" & i).Value [COLOR=#ff0000]+ #5:00:00 PM#[/COLOR]
        Else
            dStartTime = Range("D" & i).Value [COLOR=#ff0000]+[/COLOR] Range("E" & i).Value
            dEndTime = Range("D" & i).Value [COLOR=#ff0000]+[/COLOR] Range("E" & i).Value
        End If

 

[zoog25]

Thanks AlphaFrog, but a follow up questions, does it matter what the format of the time and date values are. For example i have Range D with mm/dd/yy and range E in formate of time hh:mm am/pm. If you understand what i mean, because for the last part I have tried it and it just gives me 12:00am december 31 1879.

 

[AlphaFrog]

The display format of the date or time in the cells shouldn't matter if they are Serial Date-Time values and not Text Date-Time values.

Read this short article to understand how excel stores Date-Time values.
Dates And Times In Excel

You can quickly determine if a cell contains Serial or Text date-time values by changing its format to number. A Serial date-time will display as a number. A Text date-time will not change.

If you get 12:00am december 31 1879 as a result, that indicates the cells were empty. That date-time is Excel's first date-time value or zero.

 

[zoog25]

Thanks for the info AlphaFrog. I will now try to format all the dates and times so they as you say are numbers and not text. On the flip side here is another question regarding the dates. Is it possible for you to set a cell to data validation for date and say have a little calendar pop up so a user clicks on the date they want from the calendar and the information is transferred to the cell. Similar to how travel site let you either enter a date or pick one from those mini calendars.

 

[AlphaFrog]

Thanks for the info AlphaFrog. I will now try to format all the dates and times so they as you say are numbers and not text. On the flip side here is another question regarding the dates. Is it possible for you to set a cell to data validation for date and say have a little calendar pop up so a user clicks on the date they want from the calendar and the information is transferred to the cell. Similar to how travel site let you either enter a date or pick one from those mini calendars.

There isn't a built-in Excel tool for this. Here's a step-by-step guide to create one.
Use the Calendar control to fill in dates

Or do a web search for something like Excel popup date picker and you will find several other useful guides and third party add-ins.