Creating Outlook Appointments from Email

I'm sure everyone who uses Outlook runs into this issue of creating a meeting invite based on an email that was just received. I'm surprised Microsoft hasn't added a button by default to make it easy, but just because it's not there doesn't mean it's not possible. (Another reason to switch to Gmail, it's two clicks away - should really be one click away, but then ...)

The quick and dirty way to do it is to just drag the message onto the calendar tab and it will automatically open up an appointment for the next hour with the message in the body of the invite. What it doesn't do is to add the people on the email to the invitees list - you still have to add them manually. Now, we don't want to do that, do we? A VBA macro ought to do it. Thankfully, you don't have to write that yourself (unless you suffer from a serious case of NIH syndrome), as someone (who works for Microsoft in this case) has already done this for you - a few years ago!

The quick steps are:

1. Download this file (it's safe don't worry) and save it to your Desktop: http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-components-postattachments/00-02-50-65-92/NewMeetingRequestFromEmail.bas
2. Open Outlook, hit Alt-F11 to open the Visual Basic Editor
3. Hit Ctrl-M to open the import dialog and select the downloaded file to import. Save and close. 
4. Open any email and from the quick access toolbar, hover over an item and right click to select "Customize Quick Access Toolbar ..." 
5. In the configuration pop-up window, select "Macros" from the "Choose commands from: " drop-down menu. 
6. You should see the macro that you just saved there, click "Add >>" to add it to the Quick Access Toolbar. (Choose a different icon to make it look like a Calendar to satisfy your inner OCD)

Now, when you open any message you can simply click this icon and it will do all wonderful things for you:

• Create a new appointment
• Copy the categories, body, and subject
• Copy the attachments
• Add the sender as a meeting participant
• Add each email recipient as a meeting participant
• Each To: participant will be required
• Each CC: or BCC: participant will be optional

If you would like more detailed instructions, then please refer to the original blog posts about how to get this done:

Creating an Outlook Appointment based on an Email message
Adding a VBA Macro to Outlook

Enjoy!

Google Apps Script - Save Gmail attachments to Salesforce

I recently signed up for Salesforce's force.com developer account to play around with it. It's a rather impressive platform and they recently announced full REST API support. I wanted to see how easy it was to integrate something using this API and decided to create an utility that would save some important Gmail attachments to Salesforce. Seemed like a good fit for using Google Apps Script as well, which recently added support for Gmail.

Disclaimer: There may already be better ways to do this - I never checked because it is not my intent to create an utility for general public use, I merely want to learn the usage of the APIs. However, if it seems like something you could use, feel free to copy. Of course use it at your own risk and the usual yada-yada applies.

Ok, first of all I don't want all my attachments being saved in SF and hence I'm going to filter only those that are sent to "username+Salesforce@gmail.com" - the idea being if it's something important then I would forward it to my address adding the +Salesforce tag. The +tag in Gmail address is one way to do this, you can setup some labels, filters, etc. if you like.

The steps to do this automation are:

1. Register a Remote Application in Salesforce
2. Create a new Folder in Salesforce for saving the documents
3. Create a Google Apps Script  
4. Do a dry run (unit test!) 
5. Set it up on a timer trigger

Now, lets go through it step by step:

Step 1: Register a Remote Application Salesforce
Follow the instructions here to register a remote application - https://na12.salesforce.com/help/doc/en/remoteaccess_define.htm.
Be sure to name it "Gmail2Salesforce" for consistency. I guess it doesn't matter what you call it, you just need the keys.

Step 2: Create a new Folder in Salesforce for saving the documents
Create a Folder under Documents tab called "From Gmail" - if you use something else be sure to update it in the script.

Step 3: Create a Google Apps Script
Like all things Google, it is still evolving but there is plenty of documentation at http://code.google.com/googleapps/appsscript/
a. Go to docs.google.com
b. Create new Spreadsheet
c. Go to Tools -> Script Editor
d. Copy paste the source code below and save it as "Gmail2Salesforce"
e. Open the Properties dialog from File menu and create two new Project Properties named ConsumerKey and ConsumerSecret. The values for which have to come from the screen in step 1 where you registered this app in Salesforce.

Step 4: Do a dry run (unit test!)
To do a dry run first send a test email to your Gmail address with the +tag, i.e., username+Salesforce@gmail.com - with some attachment obviously.

a. In the "Select function" drop-down in Script Editor, select "myFunction" and click the run icon.
b. Click on Authorize for the first popup - which authorizes Google Docs to execute the script on your behalf.
c. Once you do that, the next step will require authorization for Gmail. You should click "Grant Access".
d. Now this sets the script up, ready for actual execution.
e. Go ahead and click run again.
f. Another window should popup requesting authorization, Authorize it.
g. A new popup should open Salesforce page where you have to Allow this application to connect to Salesforce.
h. Once the window automatically closes and you are back to the script page, you should see that your attachment was saved to the "From Gmail" folder in Salesforce.
i. After this step, subsequent executions of the script should not require any additional authorizations, unless you changed something in the source and saved it.

Step 5: Set it up on a timer trigger
To run the script automatically, set it up on a timer. Click Triggers and select Current Project's Triggers from the menu. Add a new row and select the Events as "Time-driven", "Minutes timer" and "Every 30 minutes" (or 10 or 5, depending on your need obviously). You can add a notification if the script failed for some reason.

Save everything and that's it!

You will notice that the script also creates a new label in Gmail called Salesforce and tags the processed emails with it. It also marks them as Read.

Here is the full source code for the script, it should be really easy to follow along.
// begin

// get the consumer key and secret - this should be from the Remote Access setup in SF
var consumerKey = ScriptProperties.getProperty("ConsumerKey");
var consumerSecret = ScriptProperties.getProperty("ConsumerSecret");;

// add the OAuth service
var oAuthConfig = UrlFetchApp.addOAuthService("salesforce");
oAuthConfig.setAccessTokenUrl("https://login.salesforce.com/_nc_external/system/security/oauth/AccessTokenHandler");
oAuthConfig.setRequestTokenUrl("https://login.salesforce.com/_nc_external/system/security/oauth/RequestTokenHandler");
oAuthConfig.setAuthorizationUrl("https://login.salesforce.com/setup/secur/RemoteAccessAuthorizationPage.apexp?oauth_consumer_key="+encodeURIComponent(consumerKey));
oAuthConfig.setConsumerKey(consumerKey);
oAuthConfig.setConsumerSecret(consumerSecret);

// login to Salesforce to get a session id
var sessionLoginUrl = "https://login.salesforce.com/services/OAuth/u/21.0";
var options = { method : "POST", oAuthServiceName : "salesforce", oAuthUseToken : "always" };
var result = UrlFetchApp.fetch(sessionLoginUrl, options);
var txt = result.getContentText();
var accessToken = txt.match(/<sessionId>([^<]+)/)[1];
var serverUrl = txt.match(/<serverUrl>([^<]+)/)[1];
var instanceUrl = serverUrl.match(/^https?:\/\/[^\/]+/)[0];

function myFunction() {
    // setup the Gmail label name, creating it if it doesn't exist
    var FOLDER_FROM = "Salesforce";
    var label = GmailApp.getUserLabelByName(FOLDER_FROM);
    if (!label) {
        GmailApp.createLabel(FOLDER_FROM);
        label = GmailApp.getUserLabelByName(FOLDER_FROM);
    }

    // get the Salesforce folder name and id
    var FOLDER_TO = "From Gmail";
    var FOLDER_TO_ID = getFolderId(FOLDER_TO);

    // Fetch unread emails sent to +Salesforce
    var threads = GmailApp.search("is:unread to:+" + FOLDER_FROM);
    if (threads) {
        for (var i in threads) {
            var messages = threads[i].getMessages();
            for (var j in messages) {
                if (messages[j].isUnread()) {
                    var attachments = messages[j].getAttachments();
                    for (var k in attachments) {
                        saveAttachment(FOLDER_TO_ID, attachments[k]);
                    }
                }
            }
        }
        label.addToThreads(threads);
        GmailApp.markThreadsRead(threads);
    }
}

function saveAttachment(folderId, blob) {
    var docName = blob.getName();
    var contentType = blob.getContentType();
    var encodedDoc = Utilities.base64Encode(blob.getBytes());

    var docUrl = instanceUrl + "/services/data/v20.0/sobjects/Document/";
    var queryStr = { "Name" : docName, "FolderId" : folderId, "ContentType" : contentType, "Body" : encodedDoc };

    // Salesforce REST API expects the methods to submit JSON requests - set contentType and payload accordingly
    response = UrlFetchApp.fetch(docUrl, {
                            method : "POST",
                            contentType : "application/json",
                            headers : { "Authorization" : "OAuth "+accessToken },
                            payload : Utilities.jsonStringify(queryStr) }
                      );
}

function getFolderId(folderName) {
    var soql = "SELECT Id,Name FROM Folder WHERE Name='" + folderName + "'";
    var queryUrl = instanceUrl + "/services/data/v21.0/query?q="+encodeURIComponent(soql);
    var response = UrlFetchApp.fetch(queryUrl, { method : "GET", headers : { "Authorization" : "OAuth "+accessToken } });
    var queryResult = Utilities.jsonParse(response.getContentText());
    var folderId = (queryResult && queryResult.records[0]) ? queryResult.records[0].Id : null;

    return folderId;
}

// end

The timers are setup to execute in Google's cloud infrastructure, so you don't have to be logged in to either Gmail or Google Docs even though you create the Apps Script from Spreadsheet. Neat!

The REST API developer documentation for Salesforce is available at: http://developer.force.com/REST

Also, I should mention that I built upon the code from the blog posts Gmail Snooze with Apps Script and Connecting to Salesforce from Google Apps Script via OAuth and RESTful API.

Gmail Search Operators

Recently I had to find a certain email message (which had an attachment) and after couple of quick scans I checked to see if Gmail had an option to search for messages with attachments. Sure enough, there was one: "from:xyz has:attachment". It shows all conversations with messages from xyz having an attachment.

You can also search for a specific filename using "filename:abc.txt". One thing I noticed was if you are looking for an attachment named abc2.txt or 4abc.txt, then searching with "filename:abc" doesn't bring it up; you have to search for "filename:abc2" or "filename:4abc" - really??

For the full range of search options in Gmail, check out their help page: https://mail.google.com/support/bin/answer.py?hl=en&answer=7190

JDK 7: Strings in switch Statement

Another useful functionality added in JDK7 is the ability to use Strings in switch statements. The Strings are compared using their equals method (hashcode) and as such are case-sensitive.

public String getSeasonFromMonth(String month) {
    String season;

    switch (month) {
        case "December":
        case "January":
        case "February":
            season = "Winter";
            break;

        case "March":
        case "April":
        case "May":
            season = "Spring";
            break;

        case "June":
        case "July":
        case "August":
            season = "Summer";
            break;

        case "September":
        case "October":
        case "November":
            season = "Fall";
            break;

        default:
            throw new IllegalArgumentException("Invalid Month - " + month);
    }

    return season;
}


The compiler also generates more efficient byte-code from the switch statement instead of chained if-then-else statements.

JDK 7: Multi-catch and final rethrows

Another neat feature in JDK 7 is the multi-catch feature for exception handling.

If you have code that reads like:

try {
    doSomething();
} catch (ParseException pe) {
    logger.error("Parsing Failed", pe);
    throw pe;
} catch (IOException ioe) {
    logger.error("IO Failed!", ioe);
    throw ioe;
}

It can now be written more concisely as follows:

try {
    doSomething();
} catch (ParseException | IOException e) {
    logger.error("Some exception", e);
    throw e;
}

This really cuts down the boiler plate code for handling the individual exceptions, especially if all you are doing is to simply log and re-throw it. The problem however manifests more generally when we see code that doesn't bother to catch the individual exception but handles it using a generic Exception type.

try {
    doSomething();
} catch (Exception e) {
    logger.error("Some exception", e);
    throw e;
}

In this case the calling method must handle the more generic Exception type and we lose the specific exception. In JDK 7, we could add a keyword, final, to the catch block and specify that the specific type of exception be thrown instead.

try {
    doSomething();
} catch (final Exception e) {
    logger.error("Some exception", e);
    throw e;
}

The "final" is optional, however it adds clarity that the specific exception will be thrown instead of the generic Exception. Here is a more rounded example that explains this:

public class TryCatch {
    public static void main(String ... args) throws Exception {
        doSomething();
    }

    private static void doSomething() throws IOException, ParseException {
        try {
            if (Math.round(Math.random()) %2 == 0) {
                doStuff();
            } else {
                doSomeOtherStuff();
            }
        } catch (final Exception e) {
            out.println("Omg, there was an exception!");
            throw e;
        }
    }

    private static void doStuff() throws IOException {
        throw new IOException("Oops, IO failed!");
    }

    private static void doSomeOtherStuff() throws ParseException {
        throw new ParseException("Uh no, Parsing failed!", 0);
    }
}

Another neat-feature that I think should have been added a long time ago.

JDK 7: Diamond Operator <>

JDK 7 is finally here and comes with it (among other niceties) the much needed syntactic sugar for type inference in generics declaration - the diamond operator (<>).

A declaration such as
Map<String, List<String>> map = new HashMap<String, List<String>>(); 

can now simply be written as
Map<String, List<String>> map = new HashMap<>();

The new operator <> automatically infers the type of the object we are creating based on the type declaration. If you leave out the <> it is still valid syntax of course, however, you will get a warning from the compiler for unchecked conversion like before.

Refer to the Java Tutorial page for more info on Generics Type Inference: http://download.oracle.com/javase/tutorial/java/generics/gentypeinference.html

Time to clean up some code!