Microsoft Excel Tips & Tricks

Generate an Email inside Excel Spreadsheet

posted 19 Apr 2012, 12:09 by Gill Flynn   [ updated 9 Jan 2013, 15:56 ]

Insert a Dynamic Email Link in Excel

Want to auto-generate an email from a spreadsheet? You can do this easily using the Hyperlink() function.  I've generated the following sample email by simply clicking on a link and not having to add any text. You can see the formula below in the 2nd picture. It looks a bit tricky initially but you only have to set it up once. 

The example below shows the building of a mailto string that is the argument for Excel's Hyperlink function. 
  • the Hyperlink function takes 2 arguments (email mailto link, text to display in cell)
  • Mailto takes the form of e.g. mailto:bob@email.com?subject=Nice to see you&body=Hello Bob
The example below is referring to other cells in the spreadsheet to fill in the email address (D4 for Bob), A4 for Bob's first name and C4 for the deposit amount. I could go further and have a separate column for the actually body of the email. The ampersands (&) are used to concatenate the string portions that make up the mailto string for the Hyperlink function. The mailto text should be in quotes unless you are going to refer to a cell reference. The %0A (percent zero A) characters create a new line in the resulting email.

Now click the link to auto generate your email! (Note you will have to have your email client open otherwise Excel will be unable to launch a new email.

Auto email generated from Excel hyperlink

Insert Email Link into Excel

Copy Formats to Multiple Cell Ranges

posted 10 Nov 2011, 12:10 by Gill Flynn   [ updated 11 Nov 2011, 17:10 ]

Microsoft Excel Format Painer

Select a cell or range of cells with formats you want to copy and click the Format Painter. Now "paint" the formats over the cell(s) to repeat with the mouse. Note that if you copy the formats from a column of 8 cells with different formats you can copy this to another column of 8 cells (see picture).

To copy to several non-contiguous cells - double-click the format painter. Press <Esc> when finished.

1-2 of 2

Comments