none
Delay email delivery using DeferredDeliveryTime RRS feed

  • Question

  • Hi

    I am a teacher with novice skills in VB and need some assistance in using the DeferredDeliveryTime function.  I need to send rosters, and date dependent notices out to teachers using Outlook.  As matters can change before the notification I must keep them on hold until all is okay.

    I have a number of columns including, email in column 1, Subject in col 2 etc up to a Date variable in column 5

    The problem is that the emails are being sent immediately and that the deferred delivery function is not referencing the column.  I can reference one cell but not the column.  How can I reference the dates in column 5 using the offset fucntion.

    Dim OutlookApp As Object

    Dim MItem As Object
    Dim cell As Range
    Dim email_ As String
    Dim subject_ As String
    Dim body_ As String
    Dim attach_ As String
    Dim DateSend_ As String

    'Create Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")

    'Loop through the rows
    For Each cell In Columns("a").Cells.SpecialCells(xlCellTypeConstants)

    email_ = cell.Value
    subject_ = cell.Offset(0, 1).Value
    body_ = cell.Offset(0, 2).Value
    attach_ = cell.Offset(0, 3).Value
    DateSend_ = cell.Offset(0, 4).Value

    'Create Mail Item and send it
    Set MItem = OutlookApp.CreateItem(0)
    With MItem
    .To = email_
    .Subject = subject_
    .Body = body_
    .Attachment = "attach_"
    DateSend_ = cell.Offest(0,4).Value

    ‘Delay send
    .DeferredDeliveryTime = DateSend_
    MItem.Send

    End With

    Next

    End Sub

    Thursday, April 25, 2013 11:06 PM

All replies

  • Are you getting back any values or errors?
     
    You realize this is actually a question about Excel programming, right? You possibly might get more knowledgeable answers in an Excel forum rather than an Outlook forum.
     
    Have you looked at examples of using the Offset() funtion such as http://support.microsoft.com/kb/324991?

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "Peter_kk" <=?utf-8?B?UGV0ZXJfa2s=?=> wrote in message news:d35155f8-f37a-410e-9015-7830e397079f...

    Hi

    I am a teacher with novice skills in VB and need some assistance in using the DeferredDeliveryTime function.  I need to send rosters, and date dependent notices out to teachers using Outlook.  As matters can change before the notification I must keep them on hold until all is okay.

    I have a number of columns including, email in column 1, Subject in col 2 etc up to a Date variable in column 5

    The problem is that the emails are being sent immediately and that the deferred delivery function is not referencing the column.  I can reference one cell but not the column.  How can I reference the dates in column 5 using the offset fucntion.

    Dim OutlookApp As Object

    Dim MItem As Object
    Dim cell As Range
    Dim email_ As String
    Dim subject_ As String
    Dim body_ As String
    Dim attach_ As String
    Dim DateSend_ As String

    'Create Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")

    'Loop through the rows
    For Each cell In Columns("a").Cells.SpecialCells(xlCellTypeConstants)

    email_ = cell.Value
    subject_ = cell.Offset(0, 1).Value
    body_ = cell.Offset(0, 2).Value
    attach_ = cell.Offset(0, 3).Value
    DateSend_ = cell.Offset(0, 4).Value

    'Create Mail Item and send it
    Set MItem = OutlookApp.CreateItem(0)
    With MItem
    .To = email_
    .Subject = subject_
    .Body = body_
    .Attachment = "attach_"
    DateSend_ = cell.Offest(0,4).Value

    ‘Delay send
    .DeferredDeliveryTime = DateSend_
    MItem.Send

    End With

    Next

    End Sub


    Ken Slovak MVP - Outlook
    Friday, April 26, 2013 2:52 PM
    Moderator
  • Hi Peter,

    Thank you for posting in the MSDN Forum.

    Dates are stored in Excel as doubles, only displayed as dates per the formatting on the sheet. The Value2 property on the cells returns the stored long value. You need to convert this value to the date yourself. You can use the DateTime.FromOADate method to do this conversion.

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, May 6, 2013 11:46 AM
    Moderator