Help with Referncing Prior Month

[harryhoudini66]

Hello,

I have a script I am using (thanks to Skip) for sending out a report daily. I now need assistance with the monthly report. The report will be sent out the first of the month and I would like the subject line to referance the prior month. Here is what I have currently. What do I need to change to have it enter the prior month automatically?

Dim objOutl
Set objOutl = CreateObject("Outlook.Application")
Set objMailItem = objOutl.CreateItem(olMailItem)
objMailItem.Display
strEmailAddr = "recipient@e-mail.com"
objMailItem.Recipients.Add strEmailAddr
objMailItem.Subject = "Prior Month Totals for "& Date-1
objMailItem.HTMLBody = "Attached is the prior month report."
objMailItem.Attachments.Add "\\fileserver2\PriorMonthReport.xlsx"
objMailItem.Display
Set objMailItem = nothing
Set objOutl = nothing

 

[Geates]

[red]month([/red]date()[red])[/red] - 1

-Geates

 

[Geates]

better yet, include dateAdd():

aDate = dateAdd("m", -1, date())
intMonth = month(aDate)
strMonth = monthName(intMonth)

msgbox strMonth & " is month number " & intMonth & " of the year"

using dateadd() guarantees the prior month is 12, instead of 0, if the current month is January.

-Geates

 

[harryhoudini66]

Thank you. How about if I want the month name written? For example:

Prior Month Totals for October

The first option looks like it may work but gives the number instead of the name.

 

[harryhoudini66]

Okay, it looks like this worked.


Dim objOutl
Set objOutl = CreateObject("Outlook.Application")
Set objMailItem = objOutl.CreateItem(olMailItem)
objMailItem.Display
strEmailAddr = "recipient@e-mail.com"
objMailItem.Recipients.Add strEmailAddr
objMailItem.Subject = "Prior Month Totals for "& MonthName(Month(Now) - 1)
objMailItem.HTMLBody = "Attached is the prior month report."
objMailItem.Attachments.Add "\\fileserver2\PriorMonthReport.xlsx"
objMailItem.Display
Set objMailItem = nothing
Set objOutl = nothing

 

[harryhoudini66]

Sorry, while I am at it. How do I insert the month name in the body of the message as well?

 

[harryhoudini66]

Nevermind, I figured it out.

 

[harryhoudini66]

Man I know I am coming accross as a total with all the questions. What if I also want to include the year?

 

[harryhoudini66]

Found something that works, not sure if ideal so feedback welcomed:


Dim objOutl
Set objOutl = CreateObject("Outlook.Application")
Set objMailItem = objOutl.CreateItem(olMailItem)
objMailItem.Display
strEmailAddr = "recipient@e-mail.com"
objMailItem.Recipients.Add strEmailAddr
objMailItem.Subject = "Prior Month Totals for "& MonthName(Month(Now) - 1)& Space(1) & Year(Now())
objMailItem.HTMLBody = "Attached is the prior month report."
objMailItem.Attachments.Add "\\fileserver2\PriorMonthReport.xlsx"
objMailItem.Display
Set objMailItem = nothing
Set objOutl = nothing

 

[guitarzan]

It will work until January, and then it will fail. See Geates' second reply.

Also, when calculating the year, you don't want the current year... you want to show the year that last month fell in.

I think the code below would give what you want.

Dim strLastMonth, strLastMonthYear
strLastMonth = monthName(month(dateAdd("m", -1, Date())))
strLastMonthYear = Year((dateAdd("m", -1, Date())))

....

objMailItem.Subject = "Prior Month Totals for " & _
   strLastMonth & Space(1) & strLastMonthYear