E-Mail from Excel at a Certain Specified Time

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

I am using Ron De Bruin's excellent code to e-mail various files from
Excel, however is the feature available whereby I can specify within
the code a delayed time the mail should be sent at. Just like I can
specify the Importance, Read receipt etc

If I was to do this in outlook I'd choose Options - Do Not Deliver
Before etc. So I just wish to include this feature within the code by
specifying a date and Time. Thus I still run my Macro but my mail will
remain in Outlooks Outbox until the given time and date.

BTW, my date and Time would be values within a sheet, say Sheet2 A1


Thanks
 
Not 100% sure but i think Outlook has the ability to have code put in it and
if so,
I am sure you could get it to read the date/time in the sheet specified.
 
Hi Sean

You can try
..DeferredDeliveryTime

never us it but something like this
.DeferredDeliveryTime = DateAdd("h", 1, Now)
 
Thanks Ron

Not much look, wasn't sure of the syntax but I tried these two and both
shot the mail out straight off

..DeferredDeliveryTime = DateAdd("16:50", 1, Now)
..DeferredDeliveryTime = DateAdd("h", 1, "16:50")
 
Hi Sean

This is working Ok on my machine

.DeferredDeliveryTime = DateAdd("n", 3, Now)

It wait for 3 minuts in the outbox before it go out
 
Thanks Ron

If however I wanted to send at a specified date and time, rather than x
mins after I actually run my code
 
Ron many thanks for your interest

I have another piece of your code which I use to e-mail except I've one
slight problem and don't understand why its happening. The code below
is supposed to CC those addresses in cell AH3, it does except it also
includes an address in the CC field that is in AH2. I don't fully
undersatnd the code, but apart from above it works great

For Each cell In ThisWorkbook.Sheets("E-Figures") _
.Range("AH3:AH3").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)
 
This is working OK on my machine

.DeferredDeliveryTime = "1/6/2007 10:40:00 AM"
is supposed to CC those addresses in cell AH3, it does except it also
includes an address in the CC field that is in AH2.
Click to expand...

Normal you enter one address in a cell and loop through the cells with addresses
Do you use strto also for the TO line ?
 
Thanks Ron, works great

I use your strto to populate the CC field ctually. So I have the
addresses I want to CC in AH3 (only one in this particular instance).
The Address I want to populate the To field is in AH2. It works except
AH2 also is CC'd and I can't understand why
 
Ron, just popped it on to the address on your web site.

Let me know if you have any questions

Thanks
 
Hi Sean

Not working with one cell, include rw 4
..Range("AH3:AH4")

If you want to use cell then use
.CC = ThisWorkbook.Sheets("E-Figures").Range("AH3").Value
 
Thanks Ron, I knew away round it but couldn't figure why the code
itself wouldn't work when I wasn't actually specifying the row AH2 for
the CC field
 
Hi Sean

I agree with you that it is strange that it not working with a range with one cell.
But there are other problems also with specialcells, for example the limit.
See http://www.rondebruin.nl/specialcells.htm

I add the DeferredDeliveryTime example to the tips page for outlook mail code today.

Have a nice weekend
 
Thanks for that Ron, I'm constantly trying to improve features on my
files. Next task is to use your PDF code when I get Office'07
 
Back
Top