Outlook Tricks: Attachments Reminder

Tech Tip Tuesday, Presidents' Day Edition – Outlook tricks: Attachments Reminder

Ever send an email and intend to include an attachment and totally forget? Don’t worry, now there’s a simple solution!

This macro is designed to remind you to attach a document each time you mention the word “attach” in any form (e.g., attachment, attached, etc) in an email if you have not actually attached anything. It is very easy to set up (should you decide to use it) and takes no more than a minute to insert.

NOTE: If your email signature has a picture in it, this macro will treat that image as an attachment and won’t work properly. However there’s a spot in the code you can adjust to account for this, just read through the first section to see instructions for adjusting the code.

To install:

1)    Open Outlook and hit Alt+F11 (or go to Tools>Macro>Visual Basic Editor)

2)    On the left you will see “Project 1,” expand it and following subfolders till you see “ThisOutlookSession.” Double click on this and copy the following VB Code into the editor: (EVERYTHING BETWEEN THE “=” BRACKETS)

====================================================

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

Dim m As Variant

Dim strBody As String

Dim intIn As Long

Dim intAttachCount As Integer, intStandardAttachCount As Integer

On Error GoTo handleError

'Edit the following line if you have a signature on your email that includes images or other files. Make intStandardAttachCount equal the number of files in your signature.

intStandardAttachCount = 0

strBody = LCase(Item.Body)

intIn = InStr(1, strBody, "original message")

If intIn = 0 Then intIn = Len(strBody)

intIn = InStr(1, Left(strBody, intIn), "attach")

intAttachCount = Item.Attachments.Count

If intIn > 0 And intAttachCount <= intStandardAttachCount Then

  

    m = MsgBox("It appears that you meant to send an attachment," & vbCrLf & "but there is no attachment to this message." & vbCrLf & vbCrLf & "Do you still want to send this email?", vbQuestion + vbYesNo + vbMsgBoxSetForeground)

    If m = vbNo Then Cancel = True

  

End If

handleError:

If Err.Number <> 0 Then

    MsgBox "Outlook Attachment Reminder Error: " & Err.Description, vbExclamation, "Outlook Attachment Reminder Error"

End If

‘-X

End Sub

====================================================

3)    Hit Save and close out of your VBA editor. You’re done!

Excel Tricks: Converting one name field to multiple fields

Tech Tip Tuesday – Excel tricks: Converting one name field into two (first and last)

Using formulas to extract certain data from a field can be extremely useful (and fun). One common example is when you have a list of names with both the first and last name included, and you need to have the list separated into two fields (first and last name). If the names are consistently separated by one comma and one space, you can use this formula to extract the first and last names separately into two fields.

In this exercise we’ll use three formulas. All three are very useful, but become even more powerful when combined. They are:

LEFT(text,num_chars) – Returns the specified number of characters from the start of a text string.

MID(text,start_num,num_chars) – Returns the characters from the middle of a text string, given a starting position and length.

FIND(find_text,within_text,start_num) – Returns the starting position of one text string within another text string. FIND is case-sensitive.

In the example below, note that the names are located in cells A2 through A7, and we can see that all the names are setup consistently as last name, first name, separated by one comma and one space. This is important, because the formula will only work if the names are consistently listed.

The formula =left(A2,find(", ",A2,1)-1) will grab the last name starting with the first character until the last character of the last name, stopping when it finds the comma and space. The trick we use here is combining the formula “left”, with the formula “find”. Here we want to grab the “left” characters from cell A2, and the number of characters we want to return will be whatever character number is found directly before the comma. Therefore instead of typing a number into the “left” formula, we insert another formula where we “find” the comma and space, and then subtract that character by 1 to tell the “left” formula how many characters to display. For the example below it will return “Smith” because it finds the “, “ at character 6, so 6-1 = 5, and the “left” formula will return the first 5 characters of cell A2.

Here’s the result of the formula when applied to all the cells:

Now for the formula to grab the first name: =MID(A2,FIND(", ",A2,1)+2,100). It takes all of the characters to the right of the comma and space, using ‘mid’ and starting at the number just past the comma and space. I told it to return the next 100 characters, that way we are sure to grab all of the characters of the first name. Don't worry, it will actually only return the number of characters available, and won't result in any blanks.

Here’s how it looks when it’s applied to all the other cells below:

And there you have it!