Solved

Excel VBA minus one month

Posted on 2010-08-22
3
622 Views
Last Modified: 2012-05-10
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
Comment
Question by:willnjen
  • 2
3 Comments
 
LVL 5

Accepted Solution

by:
ploftin earned 500 total points
ID: 33497232
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
 

Author Comment

by:willnjen
ID: 33497293
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
 
LVL 5

Expert Comment

by:ploftin
ID: 33497374
Sorry about that. My math is off today lol. :)
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question