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)