EXCEL VBA PRIMER   CE En 170
Brigham Young University
Norm Jones
INTRO - [Table of Contents]
EXCEL - [Formulas] [VLOOKUP] [Validation] [Names] [Operators] [IF Function] [Goal Seek] [Charts] [Annotation] [Array Formulas]
VBA - [VB Editor] [Macros] [Controls] [Ranges] [MsgBox] [Variables] [Objects] [If Then] [Debug] [Loops] [Arrays] [Strings] [User Forms] [Functions] [Excel Funs] [Subs] [Events] [Standards]
ADVANCED - [Graphics] [E-mail] [Files] [Apps] [Google Sheets]

Sending E-Mail Using VBA in Excel

It is often useful to send e-mail using VB code. You can also send text messages using this technique. This page includes sample code and instructions.

Adding the Email Utilities Module

First of all, you need to add some code to your project. Do the following:

Unzip the file. Some e-mail clients reject attachments containing code, so I put it in a zip archive. In the archive you will see a file called email_utilities.bas.

  1. Right-click here (email_utilities.bas) to download a file containing VB code for sending e-mail.
  2. Go to your VB Editor window and right-click in the Project Explorer window on the left and select Import File.
  3. Path to the email_utilities.bas file and open it. It will then be added as a new module.
  4. Open the module and look at the functions and subs. You can now call these from anywhere in your code.

So to use the send_mail sub, you just need to call it and pass the arguments. Like this:

send_mail "myaddress@gmail.com", "TEST", "Hello world."

This will create a new message and launch your default e-mail client (Outlook, Thunderbird, etc). If you are working in the CAEDM network, there may not be an e-mail client set up on your account so the first time you run this you will probably need to go through a setup process. If you want to be able to send mail without having to hit the Send button on each message, you will need to use the Outlook version of the send_mail function.

Sending Text Messages

Next you need to decide if you want to send e-mail or text messages. To send a text message you use the same code, but you use the phone number and carrier to formulate an e-mail address for the text message. First you have to determine the correct e-mail suffix from the following list:

Company E-mail tag
Sprint messaging.sprintpcs.com
AT&T mmode.com
Cingular mobile.mycingular.com
Nextel messaging.nextel.com
T-Mobile tmomail.net
Verizon vtext.com

You can look up more complete lists on the internet. Then you take the phone number ("111-222-3333") and remove the dashes so that it is nothing but numbers ("1112223333") using the Replace string function. This creates the prefix. Then you combine the prefix and the suffix to generate the e-mail addresses as follows:

Mobile # Carrier E-mail
801-111-1111 Sprint 8011111111@messaging.nextel.com
801-222-2222 Nextel 8012222222@messaging.nextel.com
801-333-3333 Cingular 8013333333@mobile.mycingular.com
801-444-4444 Nextel 8014444444@messaging.nextel.com
801-555-5555 Nextel 8015555555@messaging.nextel.com
801-666-6666 AT&T 8016666666@mmode.com
801-777-7777 Verizon 8017777777@vtext.com
801-888-8888 AT&T 8018888888@mmode.com
801-310-9291 T-Mobile 8013109291@tmomail.net
801-209-5114 AT&T 8012095114@mmode.com

Sometimes it is easiest to formulate the addresses just using Excel formulas. You can use a VLOOKUP to get the proper suffix from the carrier. Then you use your VB code to loop through the table and send your e-mail messages.

Other Resources

For more information on sending email using VBA, see the following:

http://www.rondebruin.nl/sendmail.htm (great resource here - extensive set of sample code)

http://msdn.microsoft.com/en-us/library/office/ff458119%28v=office.11%29.aspx (Outlook sample code)

http://msdn.microsoft.com/en-us/library/office/ff519602%28v=office.11%29.aspx (Part 2 from previous link)

http://www.makeuseof.com/tag/send-emails-excel-vba/ (The CDO method - I haven't tried this, but it looks pretty slick)