Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Outlook --Progmatically send email with date trigger

Status
Not open for further replies.

MrsTFB

MIS
Oct 3, 2000
307
US
I'm not sure how to ask this, but here goes. I'm sending out an email about a quote that needs completed. Within THAT email is the Target Date. I have been asked to place in the reciepent's calendar, a reminder for one week PRIOR to the target date.
Anyone ever done anything like that?
VBA is used to send the notification message, so I could add a line of code in there.....but not sure what it would say.
I'm gonna post the existing code:

Private Sub OptionButton15_Click()
'begins new quote


Unload FSpword
oldstatusbar = Application.DisplayStatusBar
Application.DisplayStatusBar = True

Application.ScreenUpdating = False




If UCase(TextBox1.Text) = "62458" Then
Application.StatusBar = "Preparing and delivering e-mail notification"

Unload FSpword
Sheets("Sign-Off Record").Select
Range("e12").Value = "FS"
Range("d12").Value = TextBox2.Text
Range("E5:e8").Select
Selection.Copy




Range("ax1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

ActiveWorkbook.Save

'Dimension variables.

Dim OL As Object, MailSendItem As Object
Dim W As Object
Dim MsgTxt As String, SendFile As String
Dim ToRangeCounter As Variant
Dim RFQNum As String



'Pulls text from file for message body
MsgTxt = "Flavious Smith has initiated Quote # " + Range("Ax2").Text + " Target Date: " + Range("ax3").Text + " Customer: " + Range("ax1").Text + " Description: " + Range("ax4").Text


'Ends Word session
'Set W = Nothing

'Starts outlook session
Set OL = CreateObject("outlook.application")
Set MailSendItem = OL.createitem(olmailitem)

ToRangeCounter = 0

'Identifies number of recipients for To list.
'Workbooks("email.xls").Activate
For Each xcell In ActiveSheet.Range(Range("tolistFS"), _
Range("tolistFS").End(xlToRight))
ToRangeCounter = ToRangeCounter + 1
Next xcell

If ToRangeCounter = 256 Then ToRangeCounter = 1

'creates message
With MailSendItem
.Subject = ActiveSheet.Range("subjectcell" + " " + RFQNum).Text
.body = MsgTxt

'creates "TO" list
For Each xrecipient In Range("tolistFS").Resize(1, ToRangeCounter)
recipientlist = recipientlist & ";" & xrecipient
Next xrecipient

.to = recipientlist
.Send

End With

'ends outlook session
Set OL = Nothing

Application.CutCopyMode = False





End If

I hope that makes sense:
Thanks in advance,
MRStfb
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top