• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 686
  • Last Modified:

Excel VBA minus one month

Hi Experts

I have this macro which adds a MS Outlook appointment based on the selected Excel Cell (which is a date).  It works OK.

As you will see, the following line sets the appointment start to be at 8am, 31 days before the ActiveCell date.  

        .Start = ActiveCell.Value - 31 + TimeValue("08:00:00")

My question is, how can I change that to be ONE MONTH before the ActiveCell date rather than 31 days?  That way, if the ActiveCell date is 24/3/2011, .Start will be 24/2/2011.

Thanks

Will

Sub Set_Outlook_Reminder()
Dim objOutlook As Object
Dim objAppt As Object
Dim objNamespace As Object
Dim objFolder As Object
 
Worksheets("Customer Database").Activate
 
    Set objOutlook = CreateObject("Outlook.Application")
    
    Set objNamespace = objOutlook.GetNamespace("MAPI")
    Set objFolder = objNamespace.GetDefaultFolder(9)
    Set objAppt = objFolder.Items.Add 'create task item
    With objAppt
        .Start = ActiveCell.Value - 31 + TimeValue("08:00:00")
        .End = .Start + TimeValue("00:30:00")
        .Subject = "Invoice " + ActiveCell.Offset(-2, 0).Value
        .Location = ""
        .Body = ""
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = 120
        .ReminderSet = True
        .Save
    End With

Set objAppt = Nothing
Set objFolder = Nothing
Set objNamespace = Nothing
Set objOutlook = Nothing

MsgBox "Successfully Added to Outlook"

End Sub

Open in new window

0
willnjen
Asked:
willnjen
  • 2
1 Solution
 
ploftinCommented:
To get the date of "one month ago", use this:
Sub Set_Outlook_Reminder()
Dim objOutlook As Object
Dim objAppt As Object
Dim objNamespace As Object
Dim objFolder As Object
 
Worksheets("Customer Database").Activate
 
    Set objOutlook = CreateObject("Outlook.Application")
    
    Set objNamespace = objOutlook.GetNamespace("MAPI")
    Set objFolder = objNamespace.GetDefaultFolder(9)
    Set objAppt = objFolder.Items.Add 'create task item
    With objAppt
        .Start = DateAdd("m",1,ActiveCell.Value) + TimeValue("08:00:00")
        .End = .Start + TimeValue("00:30:00")
        .Subject = "Invoice " + ActiveCell.Offset(-2, 0).Value
        .Location = ""
        .Body = ""
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = 120
        .ReminderSet = True
        .Save
    End With

Set objAppt = Nothing
Set objFolder = Nothing
Set objNamespace = Nothing
Set objOutlook = Nothing

MsgBox "Successfully Added to Outlook"

End Sub

Open in new window

0
 
willnjenAuthor Commented:
Perfect, except it added one month so i changed it to the following...

        .Start = DateAdd("m",-1,ActiveCell.Value) + TimeValue("08:00:00")

Thanks for your help!!!
0
 
ploftinCommented:
Sorry about that. My math is off today lol. :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now