Excel Sheet to Outlook

[Guest]

Hello All - I am using the Send_Range code provided by Microsoft to send an
Excel worksheet range as an email. This works great, but, because this is
always a two-week schedule that is sent, I was hoping I could add options to
expire the message "two weeks from today". Any ideas or referrals to links,
books, for help adding to this code will be greatly appreciated.
Thank you. Annie

Sub Send_Range()
' Select the range of cells on the active worksheet.
ActiveSheet.Range("A3:F20").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is displayed.
With ActiveSheet.MailEnvelope
.Introduction = "Personnel on Call This Week and Next Week"
.Item.To = "oncall_list"
.Item.Subject = "Personnel on Call This Week and Next Week"
.Item.Display
End With
End Sub

 

[David Lloyd]

Annie:

The ExpiryTime property sets the expiration time for the mail item. You can
use the DateAdd function to expire the mail item two weeks from today. For
example:

Sub Send_Range()
' Select the range of cells on the active worksheet.
ActiveSheet.Range("A3:F20").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is displayed.
With ActiveSheet.MailEnvelope
.Introduction = "Personnel on Call This Week and Next Week"
.Item.To = "(e-mail address removed)"
.Item.Subject = "Personnel on Call This Week and Next Week"
.Item.ExpiryTime = DateAdd("d", 14, Now()) 'DateAdd("ww", 2,
Now()) also works
.Item.Display
End With
End Sub

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hello All - I am using the Send_Range code provided by Microsoft to send an
Excel worksheet range as an email. This works great, but, because this is
always a two-week schedule that is sent, I was hoping I could add options to
expire the message "two weeks from today". Any ideas or referrals to links,
books, for help adding to this code will be greatly appreciated.
Thank you. Annie

Sub Send_Range()
' Select the range of cells on the active worksheet.
ActiveSheet.Range("A3:F20").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is displayed.
With ActiveSheet.MailEnvelope
.Introduction = "Personnel on Call This Week and Next Week"
.Item.To = "oncall_list"
.Item.Subject = "Personnel on Call This Week and Next Week"
.Item.Display
End With
End Sub

 

[Guest]

Hello David,
Perfect solution - thank you! I also visited your website, and added it to
"favorites".
Annie