+ Reply to Thread
Results 1 to 2 of 2

VBA Excel/Email Recurring Task help!

Hybrid View

jsabo VBA Excel/Email Recurring... 11-29-2012, 01:52 PM
jsabo Re: VBA Excel/Email Recurring... 11-29-2012, 05:55 PM
  1. #1
    Registered User
    Join Date
    11-28-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    73

    VBA Excel/Email Recurring Task help!

    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
    Thanks!
    Last edited by jsabo; 11-29-2012 at 01:59 PM.

  2. #2
    Registered User
    Join Date
    11-28-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    73

    Re: VBA Excel/Email Recurring Task help!

    bump.... any ideas?
    Last edited by jsabo; 11-30-2012 at 02:18 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1