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
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"
Range("e12"
Range("d12"
Range("E5:e8"
Selection.Copy
Range("ax1"
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"
'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"
For Each xcell In ActiveSheet.Range(Range("tolistFS"
Range("tolistFS"
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"
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