XL EMail Documentation - Big Red Consulting



XL Email Manager

Introduction

Thank you for your interest in this tool by Big Red Consulting.

We’ve written this document to ensure you have a smooth start when using the tool, and to address frequently asked questions. This document contains several sections, listed below.

This document assumes you have successfully installed the tool using the readme instructions. The read me provides procedural steps for loading the Tool into MS Excel

Please note:

MS Excel is a registered trademark of Microsoft, Inc.

The XL Email Manager and this document are copyrighted by Big Red Consulting.

Overview

The XL Email Manager enables you to use Excel as an email client and send email to lists of email recipients based on an excel spreadsheet. You also can use the tool to define the emails you want to send, complete with mail merge fields and both text and HTML formatted content.

You access the tool from within Excel. It connects directly to your mail server, or can also use a Pickup folder for integration with MS Exchange.

Table of Contents

Overview 1

Essential Activities 2

Installing the tool 2

Loading the tool in Excel 2

Creating a list of names 2

Creating your email 4

Email Merge fields 5

Importing Text 6

Creating HTML Email using MS Word 7

Attachments 7

Email settings 8

Exclude list 9

Sending Email Progress & Results 10

Using the XL Email Manager with the Donor Statements addin 11

Common Error messages and how to resolve 12

Essential Activities

This is an overview of various utility operations you may want to complete when using the tool. The later sections in this document cover the steps for using the tool.

Installing the tool

If you downloaded the installer and ran it, and are reviewing this document from the Start menu or your disk, installation is complete.

Loading the tool in Excel

When Installation is complete, you may load this tool from within Excel. This is a one-time process. Once loaded, each time Excel starts the tool will be available until you unload it:

To load the tool, first start Excel & make sure a workbook is open (one usually opens when you start Excel.)

Excel 2010 - 2016:

1. Select File, then Options from Excel's menu.

2. In the Options dialog, select Add-ins.

3. At the bottom of the add-ins pane, select Excel Add-ins and press Go.

4. When the Add-ins dialog opens, click Browse and navigate to the folder where you installed the addin.

5. Select the *.xla file and then OK to close the dialogs.

Excel 2007:

1. Click the round Microsoft Office Button and then click Excel Options.

2. Click Add-Ins and then in the Manage box select Excel Add-ins. Click Go.

3. In the Add-Ins available box, click Browse... & browse to the folder to which you installed this tool (see below.)

4. Select the *.xla file & Press OK to close the dialogs.

Excel 2000 - 2003:

1. Launch Excel and then choose Tools | Add-ins... from Excel's menu.

2. Click Browse... & browse to the folder to which you installed this tool.

3. Select the *.xla file & Press OK to close the dialogs.

At this point, you should see this tool listed in the Add-In dialog, with a checkmark next to it. Press OK to close the dialog, which will load the Add-In.

In Excel 2007 - 2016, you’ll see the tool’s access points from the Add-Ins tab. In Excel 2000 – 2003 you’ll see a new menu item appear just to the right of Excel’s help menu.

Creating a list of names

Excel can read data from many sources including text files and many tools capable of writing spreadsheet data. This is one of its great strengths.

For your first use of the tool, we suggest making up a quick list of names, perhaps to email addresses you actually own. All you need to use the tool is a list of email addresses. However, you may want to add some data like Name, customer balance, or other data related to each name.

Here’s a simple example:

[pic]

Fig. 1: Note that the column titles are not needed; they’re just here for illustration purposes.

Creating your email

To create an email, choose the first menu option on the tool’s menu, Create email from Active Worksheet.

This is your primary interaction point with the tool’s features.

You’ll get a dialog that looks like this:

[pic]

Fig 2

To create your email, enter data on the various fields in the four tabs seen above.

Notes:

• You can save multiple email templates. To save an email, enter a nickname for it, then press either Save, Save & Close, or Save & Send Mail.

• Each time you use the tool, your last selections are remembered, and they become your defaults the next time you use it.

• Be sure to check your Email Settings. Many customers don’t need to change anything, but others will receive a configuration error unless they enter data in the Email Settings dialog window (see below.)

• Note the dialog doesn’t support right-click paste, but you can use ctrl-v to paste text & html from the Windows clipboard. This allows you to use any text editor or html editor to create your email source.

• If your To, CC, or BCC addresses are in more than one column, enter the column letters separated with a + or ; symbol, like A+B+C or A;B;C

Email Merge fields

When creating an email to send, you can specify many merge fields, to replace text in each individual email with text from cells on each row in Excel (where each row becomes an email.)

For example, you might want to insert your customers name into the email, or their last purchased product or open balance.

You can create an many email merge fields based on data on your worksheet in columns A to FZ (that’s a lot of columns) and those fields can hold short or long text, numbers, dates, and almost anything you can put in an Excel cell.

How to:

To use mail merge capabilities, enclose column labels OR your titles in [Brackets].So if the first column in your source spreadsheet is the recipient's first name, and titled “First Name” in row 1, then to include it in your email, use either [A] or [First Name] in the text of your email. Note that the XL Email manager only uses titles if you tell indicate that the first row is to be used in this way on the Settings/Options tab as you create email. There, pick the option near the end of the list titled “Worksheet row 1 is a title row…”

For example, you can include something like this in your email: "Dear [A]," and the [A] will be replaced by the value in column A for each row, or “Dear [First Name] where a column has the title ‘First Name’ in row 1.

Replacement values can be column labels from [A], [B], [C], … to [Z] and then also [AA] to [AZ] on up to [FA] to [FZ].

When using titles, the first matching title is found and used.

Supported Fields:

You can enter merge fields in the Subject, Body, HTML Body, From Name, and From Email fields as well as the file attachment fields.

Importing Text

It is often convenient to import text entered elsewhere, especially when using HTML email. For example, you can use an HTML editor to create an email to send.

Importing your HTML email

To import HTML, select the HTML file using the Pick File button, if it is already selected, press "Reimport HTML":

[pic]

Fig 3

Importing your text email

To import text, select the text or HTML file using the Pick File button, if it is already selected, press "Reimport Text":

[pic]

Fig 4

When importing text, the tool will read most any *.txt or other plain text file directly. It will also read HTML files, form which it will extract the text portions of the files. So, you can write one email in HTML and import it into both the text and HTML versions of your email.

Tip: You can use MS Word (and many other HTML editors) to write HTML email. To use Word, create a new Word document, format it as desired, then Save As and choose HTML. The resulting file can be imported into the tool as both HTML and text. See below for more details.

Creating HTML Email using MS Word

You can use MS Word to create highly formatted HTML messages to send with this tool.

To do this, create your email in Word, include any formatting and graphics you desire, and then choose Save As… from the File menu. Choose a filename and the file type of Web Page, Filtered. The resulting html file will contain formatting and images you can send using the tool.

To import the file into the tool, close the file in Word and then switch to the tool and use the Importing Text and html features discussed above. The images you pasted into your Word document will be included in your email.

Attachments

You can also include file attachments with your email. To do so, click on the Attachments tab on the Email to Send tab. Click the Pick File buttons to browse and select files to send.

You can also use mail merge fields in the file list and mix literal text and merge field data together. For example, you can specify files like:

|Example |Comment |

|C:\Temp\testfile.txt |No Mail merge fields used. Each recipient gets the same attachment. |

|[A] |Each recipient gets the file specified in column A. The entire path and |

| |filename is specified in the spreadsheet cell. |

|C:\Temp\[A] |Just the file name in A is replaced. |

|C:\Temp\[A].doc |Part of the filename, specified in column A is replaced. |

|C:\Temp\[A]\[B].txt |Part of the path and part of the filename is replaced, from two columns on |

| |the spreadsheet. |

Note that if a file you specify is not found, it is not attached and you may not receive a warning unless you have enabled warnings on the Settings/Options tab.

Email settings

You can load this settings dialog from the XL Email Manager’s menu. Or from the Send mail dialog’s Settings/Options tab.

Here’s a screenshot of the dialog with our default settings:

[pic]

Fig 5

Field-by-field comments:

Quick-Defaults: You can choose from a short list of email providers whose settings are known. If you would like us to add your provider to this list, send us an email.

SMTP Mail Server: If you’re not sure, check the server name in your mail configuration for your email client application, like Outlook.

Anonymous Login: Use this if your servers do not require a login for sending mail.

Username: The format of what to enter varies here. It could just be your username, just as your email client displays it, or it could be something like att\username or yourname@.

Server Port: Port 25 is a commonly used port. Your regular email client will show the port under advanced settings for your account.

Send Method: 2 is the default

Get Machine Defaults: This button will load your default email configuration, which the tool uses to send mail.

Exclude list

From the Send mail dialog, you can access your exclude list using the Edit List button (see Fig 2.)

To use the exclude list, you may enter or paste email names you do not wish to send mail to. The addresses on this list will be skipped if they appear on your source worksheet.

[pic]

Fig 6

Note the dialog doesn’t support right-click paste, but you can use ctrl-v to paste addresses from the windows clipboard.

Sending Email Progress & Results

When you choose to send email, you’ll see a progress window. This window is updated as each email is sent and confirmation is returned from the server.

If there are errors, those are logged as well as any rows that are skipped for other reasons, such as a missing email address or because that address already received an email (based on your settings.)

After all email is sent, you can write the results to a worksheet for analysis using the Send results button. Depending on how your email server responds, you can use the Results to add email to your no-send list or to get updated email from your customers. For example, sometimes the server will refuse to send email to specific invalid domain names and will return an error you can use to update your email address lists.

[pic]

Fig 7

Using the XL Email Manager with the Donor Statements addin

The XL Email Manager works with output from the Donor Statements addin. You can use the two tools together to create annual statements for Donors that include text thank you messages as well as a table of each Donor’s giving history for the period.

To do this, get the latest build of the Donor Statements addin from our site (there is no charge for an update) and then use the option to create a mail-merge table. With this option a worksheet that will work with the XL Email Manager is created. It includes customer names and email addresses as well as several special fields that include each Donor’s detailed history for the period.

Most of the fields are self-explanatory and you can use other instructions in this document to use them as email merge fields.

Several fields are special. These contain the detailed donation history for each Donor based on the data from QuickBooks and the accounts and data field selected in the interview.

donation_details_HTML – This field contains an HTML table of the Donor’s detailed history, similar to what appears when you create statements to print. If you include the field in your HTML email body description, you’ll see a formatted table when you review the email in an email program or web service.

pledge_details_HTML – This field contains an HTML table of the Donor’s pledges, similar to what appears when you create statements to print. If you include the field in your HTML email body description, you’ll see a formatted table when you review the email in an email program or web service.

donation_details_TEXT – This field contains a text table of the Donor’s detailed history. It is useful for text email, which is not usually seen by customers unless you don’t setup an HTML email body or the customer has a text-only email reader.

pledge_details_TEXT – This field contains a text table of the Donor’s pledges. Similar the field above, it is useful for text email, which is not usually seen by customers unless you don’t setup an HTML email body or the customer has a text-only email reader.

Common Error messages and how to resolve

While sending email, or attempting to send email, various errors can occur, depending on your mail server and your configuration for it. This section is a work in progress, and attempts to cover common errors customer have reported. If you receive an error, and it’s not listed here or the solutions here don’t work, please send us an email and we’ll attempt to help further.

Gmail:

If you’re attempting to use gmail, which will work well with the XL Email Manager, you may get a login error unless you “enable less secure apps”. Currently the page to do this is at which you can access after logging into your account.

-2147220958 The pickup directory path is required and was not specified.

What to do: This means you’ve selected the option to drop email into a pickup directory but none was specified in the configuration dialog. A pickup directory is usually on your email server, and often associated with using MS Exchange. To resolve this, open the email configuration dialog from the tool’s menu, and either switch to send method 2 (server/internet) or specify the pickup directory.

-2147024893 The system cannot find the path specified.

What to do: This means you’ve selected the option to drop email into a pickup directory but the specified folder can’t be found. To resolve this, open the email configuration dialog from the tool’s menu, and enter the correct pickup directory.

-2147220960 The ""SendUsing"" configuration value is invalid."

What to do: This means the email login or send using name is either incorrect or they don’t match and your server wants them to match. To resolve this, open the email configuration dialog from the tool’s menu, and enter the correct (or matching) send email address and verify the login name.

-2147220973 FAILED_TO_CONNECT: The transport failed to connect to the server

What to do: This usually means that the server address, the Port, or the connection method is incorrect in the mail configuration dialog. It can also mean that the XL Email Manager cannot reach your server for other reasons.

To resolve this, open the email configuration dialog from the tool’s menu, and verify that the SMTP mail server address is correct. The setting can either be a name or an IP address. Also, verify that the port is correct. 25 is a common port, but many ISPs use other ports and have disabled 25.

If the server and port are correct, then check for a firewall on your system or network that may be blocking Excel from using the server port specified. Some firewalls block ports by individual application, and will allow your mail program while blocking Excel.

Also, if your login method is wrong, this error may be issued as the server rejects the connection before even testing your login. For example, if you need to login with a user name but you choose Anonymous, this could happen. Similarly, if you select Use SSL/StartTLS when you should not, or don’t select it when your server requires it, you may encounter this error.

-2147220975 SMTP_SEND_FAILED: The message could not be sent to the SMTP server. The transport error code was 0x80070057. The server response was not available

What to do: This usually means that the XL Email Manager actually connected to your server, but the server rejected the connection. This could be because your server requires a login and password and yours was incorrect.

-2147220975 SMTP_SEND_FAILED: The message could not be sent to the SMTP server. The transport error code was 00x80040217 LOGON_FAILURE. The server response was not available

What to do: This means that the XL Email Manager actually connected to your server, but the server rejected the connection because the login name or password was incorrect.

-2147220977 RECIPIENTS_REJECTED: The server rejected one or more recipient addresses. The server response was: 553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1)

What to do: This means that you are connected to your server, but the server rejected one or more of the email addresses you are sending to. The initial error number can be followed by various other comments to explain the rejection. In this example, the domain was not allowed. In other examples, the specific recipient or domain may be known as invalid or otherwise black-listed. To find out the exact meaning of the returned text, contact your ISP.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download