excel macro to create outlook email with text in email body

The question mark is the beginning of additional arguments which need to be place in double-quotes. We need to use an “ &” to concatenate the B1 cell reference to the existing “mailto”: & B5 argument.īecause we are trying to add text for the subject field, we must precede the additional argument with special syntax. We’ll place some text in cell B1 that says, “ Welcome to the team”. We can either type the subject line directly into the HYPERLINK formula, or we can make the subject line dynamic. Or, we could write the following formula: =HYPERLINK('mailto:' & B5 & '?subject=' & $B$1 & '&cc=' & $B$2 & '&body='&$B$3, 'Send email') If we have hundreds of email addresses, it will take a lot of time to create each hyperlink individually. The Question Mark is a character that defines the beginning of the additional attributes of the email.Įach additional attribute will need to be prefaced with an ampersand “ &” symbol. It is the above special syntax that we need to manually create in our formula. If we add text to the “ Subject:” field, Excel will again insert special syntax in the form of ?subject= to our text. If we enter a valid email address, notice how Excel automatically adds the special syntax mailto: to the email address. The downside of this method is that we can’t place cell references in the “ E-mail address:” field. From here, we can type the email address in the “ E-mail address:” field. In the Insert Hyperlink dialog box, we select the E-Mail Address option from the “ Link to:” column on the left.

If we performed this in a traditional way, we could right-click on a cell and select Link. We have a list of email addresses and we want to create dynamic hyperlinks. The HYPERLINK function requires special syntax when creating an email hyperlink, and Excel is not exactly generous in the Help Department when creating this form of hyperlink. =IF(E2 = '', HYPERLINK('mailto:' & B2 & ' ?subject=Let's do this & body = Try harder next month!', 'Send Motivation'), HYPERLINK('mailto:' & B2 & '?subject=' & F2 & ' & body=' & G2, 'Send Email')) The final “helper column” called Send Email ( starting in cell H2) will use the IF and HYPERLINK functions to generate the clickable link.

%0A will be discussed later in this post. %0A You even exceeded your goal by ' & E2 & '!') =IF(E2 = '', '', 'You achieved your goal of ' & D2 & '. The third “helper column” called Body ( starting in cell G2) will use an IF function to create the custom message that will be placed in the Body field of the email. NOTE: An alternate version of the formula using only the LEFT and SEARCH functions is as follows: =IF(E2 = '', '', 'Thank You ' & LEFT(A2, SEARCH(' ', A2) - 1)) This formula uses some of Excel’s text manipulation functions, such as LEFT, TRIM, and SEARCH, to extract the representative’s first name from column A and concatenate it with pre-defined text. The second “helper column” called Subject ( starting in cell F2) will also use an IF function to determine which subject to place in the Subject field of the email. NOTE: For more information on the IF function, check out this post. If not, we display nothing in the cell ( two sets of double-quotes). If the representative’s sales are greater than or equal to their goal, we calculate the difference ( overage). The first “helper column” called Exceeded Goal ( starting in cell E2) will use an IF function to determine if the sales representative met their sales goal. To achieve this behavior, we need to create some “helper columns” that will contribute to the construction of the final hyperlink.

Excel macro to create outlook email with text in email body