Using VBA to send Email

VBA Code Example: How to send an email from VBA, passing through the current user's Outlook session.

Using Visual Basic for Applications (VBA), you can send an email from within your MSOffice application via a locally installed copy of Outlook. This code example is from an MSAccess button event. Since it took me nearly a day of research to get this working properly, I decided to upload the code here.

1. In your VBA code, add this recommended statement at the top of the code, above all other procedures:
Option Explicit

2. In the VBA code, select the Top Menu: Tools, References
In the list, scroll until you find "Microsoft Outlook 12.0 Object Library"
Select the library; click OK (Your version may differ)

3. Create a button in your Form (e.g. btnSendEmail)

4. In the button's CLICK event, add this code:

Important Note: I have tried and given up! I cannot get the copy-and-paste to work correctly with Internet Explorer -- When it copies this text, it looses track of the carriage-returns. With Firefox, this works properly. See below for work-around. Suggestions are welcome. Email me if you would like a copy sent directly to you.

Private Sub btnSendEmail_Click()
Dim strmsg as string
Dim OutlookObj As Outlook.Application
Dim OutlookMsg As Outlook.MailItem
Dim OutlookRecip As Outlook.Recipient
'Dim OutlookAttach as Outlook.Attachment

Set OutlookObj = CreateObject("Outlook.Application")
Set OutlookMsg = OutlookObj.CreateItem(olMailItem)

With OutlookMsg
Set OutlookRecip = .Recipients.Add("tim wolf")
'OutlookRecip.Type = otTo

.Subject = "TEST MESSAGE"
.Body = strmsg & vbCrLf
.Importance = olImportanceNormal

'If using Attachments, uncomment this and the line near the top of this routine
'If Not missing(AttachmentPath) Then
' Set OutlookAttach = .Attachments.Add(AttachmentPath)
'End If

'Resolve each recipent's name:
For Each OutlookRecip In .Recipients
OutlookRecip.Resolve
Next

.Display
'or use .Save and .Send to automate the process
End With

Set OutlookObj = Nothing
End Sub

Internet Explorer Users: Follow these steps:

• Copy the text
• Open Microsoft Word; paste text
• Re-highlight text in Word
• Paste into your code-editor (Excel)

Comments:

• In the source-code, you must use the keyword "SET", as illustrated. This seems redundant, but the word is required. Without it you will see "Run-time error '91': Object variable or With block variable not set".
  
  
• If you have troubles with the Class or Library not defined, see step 2.
  
  
• Comment-out the .Display line and uncomment .SEND and .SAVE if you want the email to send automatically.

Related Keyliner Articles:
Excel UDF (User Defined Functions)
Using Excel for Raffle-Ticket Drawing: Prizeorama
Excel VLookup - a complete tutorial
Excel Coloring Alternate Rows
Excel Parsing City-State-Zip
Excel Importing Text with Leading Zeroes
VB - Return First Word, Last Word, Supertrim
Using VBA to Send Email
Using Excel to select Raffle Tickets - Prize-orama