[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel VBA minus one month

Posted on 2010-08-22
3
Medium Priority
?
671 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

834 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