Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
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. "Sean" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
Hi Sean
You can try ..DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
I test it this this evening Sean
First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
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 de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
to send at a specified date and time
I will see if this is possible tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message s.com... 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 de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
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(xlCellTypeCon stants) If cell.Value Like "?*@?*.?*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Ron de Bruin wrote: to send at a specified date and time I will see if this is possible tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message s.com... 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 de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
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. Normal you enter one address in a cell and loop through the cells with addresses Do you use strto also for the TO line ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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(xlCellTypeCon stants) If cell.Value Like "?*@?*.?*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Ron de Bruin wrote: to send at a specified date and time I will see if this is possible tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message s.com... 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 de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
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 de Bruin wrote: 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. Normal you enter one address in a cell and loop through the cells with addresses Do you use strto also for the TO line ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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(xlCellTypeCon stants) If cell.Value Like "?*@?*.?*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Ron de Bruin wrote: to send at a specified date and time I will see if this is possible tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message s.com... 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 de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
Very strange
Can you send me the workbook private so I can look at it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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 de Bruin wrote: 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. Normal you enter one address in a cell and loop through the cells with addresses Do you use strto also for the TO line ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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(xlCellTypeCon stants) If cell.Value Like "?*@?*.?*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Ron de Bruin wrote: to send at a specified date and time I will see if this is possible tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message s.com... 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 de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
sure can Ron, I'll mail latter today
Thanks Ron de Bruin wrote: Very strange Can you send me the workbook private so I can look at it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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 de Bruin wrote: 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. Normal you enter one address in a cell and loop through the cells with addresses Do you use strto also for the TO line ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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(xlCellTypeCon stants) If cell.Value Like "?*@?*.?*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Ron de Bruin wrote: to send at a specified date and time I will see if this is possible tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message s.com... 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 de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
Ron, just popped it on to the address on your web site.
Let me know if you have any questions Thanks Sean wrote: sure can Ron, I'll mail latter today Thanks Ron de Bruin wrote: Very strange Can you send me the workbook private so I can look at it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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 de Bruin wrote: 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. Normal you enter one address in a cell and loop through the cells with addresses Do you use strto also for the TO line ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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(xlCellTypeCon stants) If cell.Value Like "?*@?*.?*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Ron de Bruin wrote: to send at a specified date and time I will see if this is possible tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message s.com... 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 de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... Ron, just popped it on to the address on your web site. Let me know if you have any questions Thanks Sean wrote: sure can Ron, I'll mail latter today Thanks Ron de Bruin wrote: Very strange Can you send me the workbook private so I can look at it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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 de Bruin wrote: 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. Normal you enter one address in a cell and loop through the cells with addresses Do you use strto also for the TO line ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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(xlCellTypeCon stants) If cell.Value Like "?*@?*.?*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Ron de Bruin wrote: to send at a specified date and time I will see if this is possible tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message s.com... 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 de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
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 Ron de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... Ron, just popped it on to the address on your web site. Let me know if you have any questions Thanks Sean wrote: sure can Ron, I'll mail latter today Thanks Ron de Bruin wrote: Very strange Can you send me the workbook private so I can look at it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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 de Bruin wrote: 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. Normal you enter one address in a cell and loop through the cells with addresses Do you use strto also for the TO line ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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(xlCellTypeCon stants) If cell.Value Like "?*@?*.?*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Ron de Bruin wrote: to send at a specified date and time I will see if this is possible tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message s.com... 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 de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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 Ron de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... Ron, just popped it on to the address on your web site. Let me know if you have any questions Thanks Sean wrote: sure can Ron, I'll mail latter today Thanks Ron de Bruin wrote: Very strange Can you send me the workbook private so I can look at it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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 de Bruin wrote: 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. Normal you enter one address in a cell and loop through the cells with addresses Do you use strto also for the TO line ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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(xlCellTypeCon stants) If cell.Value Like "?*@?*.?*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Ron de Bruin wrote: to send at a specified date and time I will see if this is possible tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message s.com... 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 de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
E-Mail from Excel at a Certain Specified Time
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 Ron de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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 Ron de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... Ron, just popped it on to the address on your web site. Let me know if you have any questions Thanks Sean wrote: sure can Ron, I'll mail latter today Thanks Ron de Bruin wrote: Very strange Can you send me the workbook private so I can look at it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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 de Bruin wrote: 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. Normal you enter one address in a cell and loop through the cells with addresses Do you use strto also for the TO line ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message ups.com... 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(xlCellTypeCon stants) If cell.Value Like "?*@?*.?*" Then strto = strto & cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) Ron de Bruin wrote: to send at a specified date and time I will see if this is possible tomorrow -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message s.com... 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 de Bruin wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I test it this this evening Sean First time for me also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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") Ron de Bruin wrote: Hi Sean You can try .DeferredDeliveryTime never us it but something like this .DeferredDeliveryTime = DateAdd("h", 1, Now) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sean" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I do an e-mail mail merge using an Excel spreadsheet? | Excel Worksheet Functions | |||
Importing dates and military time from Excel into word doc mail me | Excel Discussion (Misc queries) | |||
General mail failure when sending e-mail from Excel | Excel Discussion (Misc queries) | |||
I have to repeat my password for e-mail every time, how do i get . | Excel Discussion (Misc queries) |