PDA

View Full Version : VBA Excel/Email Recurring Task help!



jsabo
11-30-2012, 11:47 AM
Hello,

First time poster. I'd appreciate some help if ya can. I have set up a script that I will use to automatically attach an excel file to an email which is automatically filled out. It is set to automatically be high-priority and also is set to issue reminder after 5 days. My two questions are:first, is there a way to set a recurring task reminder? Would it be issuing two tasks with different intervals or would it just be one that reminds every 5 days? I might only want to issue the reminder 2x (one after 5 days, one after 10 days and that's it). Second question, how can I add a reminder for myself automatically? Here's what I have that works just issuing one reminder:

'begin email creation


'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String, MailSub As String, MailTxt As String

'************************************************* ********
'Set email details; Comment out if not required
Const MailTo = ""
Const MailCC = ""
Const MailBCC = ""
MailSub = "Code 2 and 3 Documents - Please Respond"
MailTxt = "Please see the attached which shows which documents are currently Code 2 or 3. The shaded items in column J are crucial as they have been with you for over 10 days. These need to be revised based on the comments you've received and returned ASAP. Thank you!"
'************************************************* ********

'Turns off screen updating
Application.ScreenUpdating = False

'Makes a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "Code 2 and 3.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\Users\jsabo\Desktop\" & FileName

'Creates and shows the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = MailTo
.Cc = MailCC
.Bcc = MailBCC
.importance = 2
.FlagDueBy = DateAdd("d", 5, Now)
.Subject = MailSub
.Body = MailTxt
.Attachments.Add WB.FullName
.Display
End With

'Deletes the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restores screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing

'end email creation