7

How to schedule a delivery of mail to a specific time in VBA

Code to generate mail

Set olApp = CreateObject("Outlook.application")
Set olItem = olApp.CreateItem(0)
olItem.display
olItem.To = "bhagyashri.kulkarni@mindtree.com"
olItem.Subject = "Auto Generated - Consolidated Task Tracking Report"
olItem.htmlbody = Join(aBody, vbNewLine)
olItem.Attachments.Add outputFileName
olItem.display

Will the DeferredDeliveryTime be useful? How to use it ?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
user3332507
  • 103
  • 1
  • 4
  • Use VBScript. See [THIS](http://stackoverflow.com/questions/10211250/excel-vba-email-does-not-send-when-computer-is-locked) example – Siddharth Rout Aug 12 '16 at 09:19
  • @SiddharthRout, you don't have to, there's also a mailitem property for this: https://msdn.microsoft.com/de-de/library/office/ff869452.aspx user3332507: It's all in there, just read and try for yourself. ;) If you have a specific problem, you are welcome to come back and ask. – Tom K. Aug 12 '16 at 09:22

1 Answers1

7

I was just too curious, to not answer this question. This piece of code should work. 

Sub stackfun()  
Set olApp = CreateObject("Outlook.application")
Set objMail = olApp.CreateItem(olMailItem)
With objMail
 .Display    
 .To = "tomisgreat@something.com"
 .Subject = "you rule"
 'HTML for fun
 .BodyFormat = olFormatHTML     
 .HTMLBody = "<HTML><H2>Y HELO THAR</BODY></HTML>"
 .DeferredDeliveryTime (#11:59:59 PM#)
End With
End Sub

Sadly .DeferredDeliveryTime only works after Outlook 2013, Siddarth Rout recommended this VB driven approach in the comments.

Community
  • 1
  • 1
Tom K.
  • 1,020
  • 1
  • 12
  • 28