Code For Excel And Outlook Blog

Using Excel VBA to set up Task Reminders in Outlook
November 2, 2008 – 10:41 pm by JP

I know I have trouble remembering when different projects or tasks are due. Here's one way to remember: Set up a list of tasks and due dates in Excel, then use VBA to set up reminders in Outlook.

It's perfect for things like birthdays, holidays, anniversaries, etc, anytime you want a reminder prior to the actual event, instead of the usual task reminders which seem to pop up right when something is due.

This code will silently create a task reminder in Outlook a set number of days before the date you specify. I say "silently," because this code does not trigger the Outlook object model guard -- it creates tasks without any popups whatsoever!

Paste this code into a standard module in Excel:

plain text version

VBA:

Dim bWeStartedOutlook As Boolean
Function AddToTasks(strDate As String, strText As String, DaysOut As Integer) As Boolean
' Adds a task reminder to Outlook Tasks a specific number of days before the date specified
' Returns TRUE if successful
' Will not trigger OMG because no protected properties are accessed
' by Jimmy Pena, http://www.codeforexcelandoutlook.com, 10/30/2008
'
' Usage:
' =AddToTasks("12/31/2008", "Something to remember", 30)
' or:
' =AddToTasks(A1, A2, A3)
' where A1 contains valid date, A2 contains task information, A3 contains number of days before A1 date to trigger task reminder
'
' can also be used in VBA :
'If AddToTasks("12/31/2008", "Christmas shopping", 30) Then
' MsgBox "ok!"
'End If
Dim intDaysBack As Integer
Dim dteDate As Date
Dim olApp As Outlook.Application
Dim objTask As Outlook.TaskItem
' make sure all fields were filled in
If (Not IsDate(strDate)) Or (strText = "") Or (DaysOut <= 0) Then
AddToTasks = False
GoTo ExitProc
End If
' We want the task reminder a certain number of days BEFORE the due date
' ex: if DaysOut = 120, then we want the due date to be -120 before the date specified
' we need to pass -120 to the NextBusinessDay function, so to go from 120 to -120,
' we subtract double the number (240) from the number provided (120).
' 120 - (120 * 2); 120 - 240 = -120
intDaysBack = DaysOut - (DaysOut * 2)
dteDate = NextBusinessDay(CDate(strDate), intDaysBack)
On Error Resume Next
Set olApp = GetOutlookApp
On Error GoTo 0
If Not olApp Is Nothing Then
Set objTask = olApp.CreateItem(3) ' task item
With objTask
.StartDate = dteDate
.Subject = strText & ", due on: " & strDate
.ReminderSet = True
.Save
End With
Else
AddToTasks = False
GoTo ExitProc
End If
' if we got this far, it must have worked
AddToTasks = True
ExitProc:
If bWeStartedOutlook Then
olApp.Quit
End If
Set olApp = Nothing
Set objTask = Nothing
End Function
Function NextBusinessDay(dteDate As Date, intAhead As Integer) As Date
' from Sue Mosher's book "Microsoft Outlook Programming"
Dim dteNextDate As Date
dteNextDate = DateAdd("d", intAhead, dteDate)
Select Case Weekday(dteNextDate)
' if Sunday, add 1 day to make it next business day (Monday)
Case 1
dteNextDate = dteNextDate + 1
' if Saturday, add 2 days to make it next business day (Monday)
Case 7
dteNextDate = dteNextDate + 2
End Select
NextBusinessDay = dteNextDate
End Function
Function GetOutlookApp() As Object
On Error Resume Next
Set GetOutlookApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set GetOutlookApp = CreateObject("Outlook.Application")
bWeStartedOutlook = True
End If
On Error GoTo 0
End Function

After you get TRUE or FALSE in the target cell, you'll want to delete the function, so it doesn't keep trying (and succeeding) to add task reminders over and over every time the worksheet recalculates.

--JP

Share and Enjoy:

Posted in Excel, Outlook, automation | No Comments Yet, Add Yours Now! »
Tags: CreateItem , GetObject , task reminder , TaskItem

Check if Workbook or Worksheet is Protected
November 2, 2008 – 3:13 pm by JP

I was looking for a function to check if a worksheet is protected. I googled the appropriate phrase ("check if worksheet is protected") and followed the first link: an Excel function posted at vbaexpress.com: VBA Express : Excel - Check if Worksheet is protected.

Here is a copy of the code:

plain text version

VBA:

Private Function SheetProtected(TargetSheet As Worksheet) As Boolean
'Function purpose: To evaluate if a worksheet is protected
If TargetSheet.ProtectContents = True Then
SheetProtected = True
Else
SheetProtected = False
End If
End Function

Works well, but what about when you want to check if a whole workbook is protected? You need a second function. And what if you want to check both at the same time? Or a Range? Looks like it's time to roll your own!

This function takes an Excel.Workbook, Excel.Worksheet, or Excel.Range Object as an argument, and returns TRUE if any one is protected. Let me explain.

If you pass a Worksheet Object, it will simply check if the worksheet is protected. If you pass a Workbook Object, it will check if the workbook is protected, but will also check each of the worksheets for protection.

If you pass a Range Object, it checks for two conditions:

If the cell is locked and the worksheet is protected (both are required for a cell to be uneditable), then it's protected
If the workbook is protected (by recursively calling itself to check if the Workbook object is protected)

plain text version

VBA:

Function IsProtected(objXL As Object) As Boolean
Dim wksht As Excel.Worksheet
Dim cell As Excel.Range
Select Case TypeName(objXL)
Case "Worksheet"
If objXL.ProtectContents Then
' still doesn't mean you can't edit the worksheet!
IsProtected = True
Exit Function
End If
Case "Workbook"
If objXL.ProtectStructure Then
IsProtected = True
Exit Function
End If
For Each wksht In objXL.Worksheets
If wksht.ProtectContents Then
' still doesn't mean you can't edit the worksheet!
IsProtected = True
Exit Function
End If
Next wksht
Case "Range"
If objXL.Cells.Count = 1 Then
If (objXL.Locked And objXL.Parent.ProtectContents) Or (IsProtected(objXL.Parent.Parent)) Then
IsProtected = True
Exit Function
End If
Else
For Each cell In objXL
If (cell.Locked And cell.Parent.ProtectContents) Or (IsProtected(cell.Parent.Parent)) Then
IsProtected = True
Exit Function
End If
Next cell
End If
End Select
End Function

Usage:

plain text version

VBA:

Dim wkb As Excel.Workbook
Set wkb = ActiveWorkbook
If IsProtected(wkb) Then
Msgbox "It's protected!"
End If
' or
Dim wksht As Excel.Worksheet
Set wksht = ActiveSheet
If IsProtected(wksht) Then
Msgbox "It's protected!"
End If
' or
Dim rng as Excel.Range
Set rng = Range("A3")
If IsProtected(rng) Then
Msgbox "It's protected!"
End If

The function should return FALSE for any other type of Object you pass to it, so be sure to only pass Workbook or Worksheet or Range Objects to it.

I got a bit lost writing this function, because of the numerous conditions for worksheet protection, and just settled with what I wrote above.

For example, just because a Worksheet Object is protected, doesn't mean you can't edit cells on the worksheet; if the cells are unlocked, you can still update cells on a protected worksheet. One way to check for that is by checking the Locked property of the Cells Object: If Cells.Locked = True, then all the cells on the worksheet are locked (IsProtected = True), if Cells.Locked = False, none of them are (IsProtected = False ?), and if there is a mix of locked and unlocked cells, Cells.Locked = Null (which is not a boolean value!)

And if you pass a Range object, does it matter if the workbook structure is protected? It depends on your specific needs.

And should Cells.Locked = False return IsProtected = False? It depends on the purpose of the function. If you just want a basic check for protection, it may be enough just to check if the worksheet is protected. But if you have specific needs, you might want to know not only if the worksheet is protected, but also if the cells are locked. Here's an updated version of the above function that does that.

plain text version

VBA:

Function IsProtected(objXL As Object) As Boolean
Dim wksht As Excel.Worksheet
Dim cell As Excel.Range
Select Case TypeName(objXL)
Case "Worksheet"
If objXL.ProtectContents Then
' still doesn't mean you can't edit the worksheet!
Select Case Cells.Locked
Case True ' all cells are locked AND worksheet is protected
IsProtected = True
Exit Function
End Select
End If
Case "Workbook"
If objXL.ProtectStructure Then
IsProtected = True
Exit Function
End If
For Each wksht In objXL.Worksheets
If wksht.ProtectContents Then
' still doesn't mean you can't edit the worksheet!
Select Case Cells.Locked
Case True ' all cells are locked AND worksheet is protected
IsProtected = True
Exit Function
End Select
End If
Next wksht
Case "Range"
If objXL.Cells.Count = 1 Then
If (objXL.Locked And objXL.Parent.ProtectContents) Or (IsProtected(objXL.Parent.Parent)) Then
IsProtected = True
Exit Function
End If
Else
For Each cell In objXL
If (cell.Locked And cell.Parent.ProtectContents) Or (IsProtected(cell.Parent.Parent)) Then
IsProtected = True
Exit Function
End If
Next cell
End If
End Select
End Function

Enjoy,
JP

Share and Enjoy:

Posted in Excel, VBA | No Comments Yet, Add Yours Now! »
Tags: ProtectContents , ProtectStructure

Check your generated emails for valid recipients
October 30, 2008 – 11:58 am by JP

I had a need for a function that checks if a recipient is valid. This could be useful in Excel to check if a name that someone input into a userform could resolve to a valid recipient. Hence the function name "ValidRecipient".

The code creates a throwaway email and tries to resolve the name. Returns TRUE if the string passed to it resolves to a valid Outlook recipient in your addressbook. It uses a second function to grab a reference to Outlook, so the code is late-bound and can be used in any project. It can be used on the worksheet as a UDF, or (ideally) in your VBA code.

Beware the Outlook (OMG) object model guard, which will be triggered when you access the Recipients collection.

plain text version

VBA:

Function ValidRecipient(strRecip As String) As Boolean
Dim olApp As Object
Dim olMsg As Object
Dim olRecips As Object
Dim olCurrentRecip As Object
On Error Resume Next
Set olApp = GetOutlookApp
On Error GoTo 0
If Not olApp Is Nothing Then
Set olMsg = olApp.CreateItem(0)
Set olRecips = olMsg.Recipients
Set olCurrentRecip = olRecips.Add(strRecip)
If olCurrentRecip.Resolve Then
ValidRecipient = True
GoTo ExitProc
End If
' or:
' olMsg.To = strRecip
' If olMsg.Recipients.ResolveAll Then
' ValidRecipient = True
' GoTo ExitProc
' End If
End If
ExitProc:
olMsg.Close (1)
Set olCurrentRecip = Nothing
Set olRecips = Nothing
Set olMsg = Nothing
Set olApp = Nothing
End Function

plain text version

VBA:

Function GetOutlookApp() As Object
' returns a reference to Outlook to the calling sub
On Error Resume Next
Set GetOutlookApp = GetObject(, "Outlook.Application")
If GetOutlookApp Is Nothing Then
Set GetOutlookApp = CreateObject("Outlook.Application")
Exit Function
End If
On Error Goto 0
End Function

Usage:

plain text version

VBA:

If ValidRecipient("Jimmy Pena") Then
MsgBox "OK!"
End If

or:

=ValidRecipient("Jimmy Pena")

Share and Enjoy:

Posted in Excel, VBA, automation | No Comments Yet, Add Yours Now! »
Tags: GetObject , Recipients collection , ResolveAll

Monitoring a range for changes
October 28, 2008 – 2:35 pm by JP

A co-worker of mine has a workbook with an inventory list in column D. He wants a way to highlight low inventory quantities, when the inventory for any particular item falls below a certain amount. At first I thought about conditional formatting, for example:

But he'd rather have a popup messagebox. I guess the more intrusive, the more likely it won't be ignored. So I came up with this code. If any cell in column D is changed, and the amount is below 50, the messagebox is shown.

plain text version

VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Excel.Range
On Error Resume Next
Set rng = Intersect(Range("D:D"), Range(Target.Address))
On Error GoTo 0
If Not rng Is Nothing Then
If (Target.Value <50) And (Target.Value <> "") Then
MsgBox "Below 50, please reorder now.", vbInformation
End If
End If
End Sub

Enjoy,
JP

Share and Enjoy:

Posted in Excel | 5 Comments »
Tags: event , intersect , worksheet_change

HTML Link Building Using Excel
October 26, 2008 – 7:22 pm by JP

While building a link list for my blogroll page (see Blogs I Read), I needed a way to create the list of links. The prospect of building all of the links by hand is pretty exhausting (yes, even the thought), so why not leverage Excel's capabilities?

First I took a list of the links and put them in a worksheet in column A. Then I put the site name in column B. Here is the formula to create the link:

="<a rel=""nofollow"" href="""&A2&""" title="""&B2&""""&">"&B2&"</a>"

Yes, I nofollow external links (except in comments).

This technique could be very useful, if you have a list of websites and want to turn them into HTML links, just paste in the above formula and adjust the cell references. I created a sample with the sites from that page and the formula filled down to create the links.

Link Building Worksheet sample

FYI - I finally upgraded Wordpress to 2.6.3, there's a fantastic plugin I found that does it automatically. If you aren't running the latest version, this plugin makes it very easy.

--JP

Share and Enjoy:

Posted in Excel, Formulas | 3 Comments »
Tags: formula , HTML , link building