Quick Tips: How to use “Out of Office” auto-reply on emails

Standard

 

Ever wanted to take a vacation but discover you missed tons of email delivered to you? Wouldn’t be nice if those coworkers would read their stupid daily reminder telling them you are going to be parasailing in Cozumel? Wouldn’t it just make too much sense if you could set up an auto responder so when they emailed you, they automatically got something along the lines of “Hey there, I’ll be out of the office until next Tuesday. Please reach out to (whoever) in my absence, or feel free to leave me a voice mail.” Well, you’re in luck, because this is a simple and easy setting to turn off and on, and it will save you a few headaches.

For Outlook 2010 and 2013:

  1. Click ‘File’ and then click ‘Info’ on the left side.
  2. Click ‘Automatic Replies (Out of Office).’
  3. Select ‘Send Automatic Replies.’
  4. You can change he time and date by selecting ‘Only send during this time range.’
  5. Where you see ‘Inside my organization,’ go ahead and type the message that you want to auto-reply. This will send an auto response to all people in your network, or coworkers who email you.
  6. Typing your message in the ‘Outside my organization’ will do the auto response for people outside your work (such as, say, a friend who is not your coworker, who decides to message your work email).
  7. OK.
  8. To turn this off, simply go into automatic replies and un-check the area to turn it off.
  9. That’s it!

2881878

For Outlook 2007:
  1. Open Outlook, click ‘Tools’ up on your tool bar and then click ‘Out of Office Assistant’ from the dropdown.
  2. Select ‘Send Out of Office Auto-Replies.’
  3. You can specify exact times of when to when by selecting ‘Only send during this time range,’ and you can even set the start and end times.
  4. Where you see ‘Inside my organization,’ go ahead and type the message that you want to auto-reply. This will send an auto response to all people in your network, or coworkers who email you.
  5. Typing your message in the ‘Outside my organization’ will do the auto response for people outside your work (such as, say, a friend who is not your coworker, who decides to message your work email).
  6. OK.
  7. To turn this off, simply go back into ‘Tools’ –> ‘Out of Office Assistant,’ and select ‘Do not send out of office auto-replies.’

2881879

 

For OWA (your email website you log into from your browser):
  1. Log into your OWA.
  2. Click ‘Options’ and then click ‘Out of Office Assistant.’
  3. To turn it on, click ‘Send Out of Office Auto-Replies.’
  4. You can also specify specific times of when to when here as well.
  5. Enter the message that you want people within your organization to see.
  6. For external people, or people who are not coworkers, type your message where it says ‘Send out of office auto-replies to external senders.’
  7. To turn it off, click ‘Do not send out of office auto-replies.’
  8. Save (always save!)

 

Bing, boom, bop, you will no longer have to come back with a sunburn and jet lag, only to spend 2 hours reading through tons of email, and then to remind the people in management that you’ve been gone for two weeks ;)

Excel Intermediate: Cleaning up formulas using Named Ranges

Standard

 

NOTE: All samples will be shown using Excel 2010. All examples and references will work in Excel 2003 – 2013, just the look and feel will be different.

One of the most difficult parts of understanding a complex Excel formula is determining what data is being referenced in the various ranges. For example, this formula seems more complex than it really is. You have no way of determining what is being compared in the IF statements without analyzing the ranges.

{=SUM(IF(Data!F2:F501='Dash Board'!C3,IF(Data!H2:H501>='Dash Board'!C4,IF(Data!I2:I501>='Dash Board'!C5,1,0),0),0))}

NOTE: This is an Array Function. If you are not familiar with them please read this blog.

All you can say for sure is that you are getting the total of rows on the Data page that meets three separate criteria.

Here is the same formula using Named Ranges:

{=SUM(IF(State=Project_State,IF(Employees>=Techs_Required,IF(Budget>=Project_Budget,1,0),0),0))}

That’s better. Now we know that we are testing the following criteria:

  • Is the State the same as the Project State?
  • Is the number of Employees greater than or equal to the Techs Required?
  • Is the Budget greater than or equal to the Project Budget?

All rows that meet these criteria will be counted.

Now let’s see how we set this up. In the upper left hand corner is a box that shows the current cell / range that is selected. This is called the Name Box.

Named Range 1

In the image above, you can see that cell E8 is currently active, and this is reflected in the Name Box. But did you know that you can change the Name of any cell or range of cells just by typing in the Name Box? Let’s give this a try. Click in cell C3 to make this the active Cell. Now click in the Name Box and type in “Project_State” (remember this is the name used in our second formula above). Once you press <Enter> the cell C3 will also be known as Project_State and can be referred to as such in any formula or reference.

Named Range 2

Now we need to create the references to cells C4 and C5 for our formula. Make cell C4 the active cell and name it “Techs_Required.” Finally, make cell C5 the active cell and name it “Projected_Budget” (the reason I gave it this name and not the name shown in B5 is because we will have another field named “Budget” on the Data tab).

That should cover everything on the “Dash Board” tab; now let’s work on the “Data” tab. There are two ways to name a range. Let’s try both methods.

First method:

Click in the cell A2 to make this the active cell. Now press <Ctrl><Shift><Down Arrow>. This will select all the cells under “First Name”. Your screen should look something like this:

Named Range 3

Now, in the Name Box type in “First_Name.” Once you press <Enter>, the range A2:A501 will also be known as Project_State and can be used in any formula or reference.

Second method:

Click in cell A1 to make this the active cell. Now press <Ctrl><Shift><End>. This will select the entire data table. Now, from the menu bar, select Formulas and click on Create from Selection.

Named Range 4

You will get a pop-up asking where the titles can be found. Select only Top Row.

Named Range 5

Ta-da! We now have named ranges for each column. To check this, press the dropdown arrow to the left of the Name Box.

Named Range 6

As you can see, all spaces in the title will be replaced with underscores (“_”).

So now we can create our formulas in a more sentence-like style. Here are a few examples that can be created quickly and will be easy to understand by anyone reading them.

  • =SUM(Budget)
  • =Countif(State, “=CA”)
  • =Average(Budget)
  • {=SUM(IF(State=Project_State,IF(Employees>=Techs_Required,IF(Budget>=Projected_Budget,1,0),0),0))}

A couple of thing to keep in mind about named ranges:

  • If a name already exists on the tab, it will automatically be overwritten without prompting
  • If a name already exists on another tab, you must specify the tab or it will default to the current tab’s name
    • For example Data!Budget will reference the range Budget on the Data tab
    • Adding new cells to the end of a range does not automatically extend the range
    • Inserting new cells in the middle of a range does automatically extend the range
    • Do not use reserved words when naming a range (select, if, sum…)

As you can see, using named ranges can not only make reviewing formulas easier, but you can also create new formulas more quickly.

Tech Tips for Techs: Is obvious spam still making it into your Office 365 inbox?

Standard

 

In this TechTip I want to go over an issue that I’ve seen pop up periodically that might save you a support call to Microsoft.

In the Office 365 platform Microsoft uses a scoring system when it comes to dealing with spam. It’s scaled from -1 to 9 but, interestingly enough, they only use -1, 0, 1, 5, 6, and 9. The other numbers in the scale aren’t used at all by the platform. (Weird, huh?) When a message lands in 365, the content filters will scan the message and apply a score to it based on a combination of variables that include Microsoft’s own spam definitions as well as those set by the system administrator.

These scores are referred to as SCL – Spam Confidence Level. An SCL of -1 gets applied to messages that are explicitly defined as safe, e.g., whitelisted email addresses, IP addresses, or domains. These “bypass” the spam filter. An SCL of 0 or 1 gets applied to messages that the filter has scanned and determined to be safe, and will get dropped into the recipient’s inbox. An SCL of 5 or 6 is given to messages that are ‘quite likely’ to be spam, and those get dropped into the recipient’s junk email folder. An SCL of 9 is given to messages that are ‘most certainly’ spam, and these get dropped into the recipient’s junk email folder as well.

It should be noted that the behavior described above is what the Exchange Online platform does by default. A system administrator can define their own spam/content filtering policies that treat these messages differently, e.g., deleting a message that gets scored with a 9 instead of putting it in the junk email folder. The default content filter policy (which, by the way, cannot be deleted) sits at the lowest priority, allowing a sysadmin to apply a higher priority to their own custom rulesets if so desired.

Now to the meat and potatoes – the issue we’ve seen crop up with some frequency is where a user reports that a lot of [obviously] spam messages are making it through to their inbox. In looking through the message headers, we see that said messages are getting scored with an SCL of 5 or 6, but for some reason they’re not going to junk mail. We check the content filter, and everything looks good there. So why are these messages not going to junk mail?

Simple.

One of the things you should check is to look at the Block/Allow settings in that user’s OWA. From the gear in the top right corner, click Options. From there, click Block or Allow on the left. More than likely, you’re going to see that the “Don’t move email…” option is selected. To enforce the spam filter to do what it’s supposed to do, choose the “Automatically filter…” option, and then click Save.

 

14-0709_1

14-0709_2

 

Voila!

 

Tech Tips for Techs: Checking license and provisioning status in Office 365

Standard

 

In this TechTip, we’ll talk about the Powershell command you can use to check on license provisioning status for your users in Office 365. (Yes, I said Powershell. Unfortunately, Microsoft doesn’t expose a lot of information through the GUI, and this is one of those scenarios where we have to go to a CLI.)

Why is this important, you might ask? A common scenario that pops up where I leverage this command is when I have a user who is unable to access or use certain features in 365. Let’s say you have a user who is assigned an E3 seat and everything is working beautifully except for OneDrive (it’s grayed out in the bar across the top of the Microsoft portal). First thing I would personally check is to make sure that she has Sharepoint assigned and in good standing.

In order to do that, fire up Powershell, connect to 365 using a Global Administrator account, and run the following command:

(Get-MsolUser -UserPrincipalName user@domain.com).Licenses[0].ServiceStatus

 

An E3 license is going to give you output that looks like this:

14-0626_1

 

Using my OneDrive scenario, we can see why this particular user doesn’t have access. Because OneDrive leverages the Sharepoint platform, and the license is stuck in PendingInput status, there has been no provisioning and therefore, no access. At this point, unless this user is actively using their account or has litigation hold enabled, you could potentially remove the license, wait for it to disappear, and then reassign it. However, if this user has any data in the account at all or if lit hold is enabled, DO NOT unassign the license. In that case, you will want to open a ticket with your support provider, if it’s been stuck in PendingInput for over 24 hours.

 

Keeping tidy by using inbound mail rules within Outlook.

Standard

 

As spring is coming to a close, there are a few weeks to indulge in the yearly “reason” we call it Spring Cleaning. As professionals in our respective fields, and with the technology age being what it is, we all have an abundance of inbound emails that can sometimes become hard to keep track of. Today I am going to give a quick rundown on how to neatly manage your incoming emails by setting up rules.

1)      Create a new sub-folder under your inbox labeled as a topic, group, company, etc. Name it anything you want to filter by

1

2)      Now, on the top ribbon, select the menu “Rules” and select “Manage Rules and Alerts.”

2

3)      Select “New Rule” at the top left.

4)      On the next page, this is where we start customizing the rule. You will have many options like “Move messages from someone to a folder” and “Move messages with specific words in the subject to a folder”.

3

5)      If you hit next after selecting one, it will take you to the next page where you select condition(s).

4

6)      In the bottom window, you have the option to send those messages to a specified folder, this being the folder we created before.

5

You can set as many folders and rules as needed to keep you organized. You know yourself better than anyone and if you have any hint of OCD like myself, then rules and organization are for you.