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:
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)
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)
'Resolve each recipent's name:
For Each OutlookRecip In .Recipients
'or use .Save and .Send to automate the process
Set OutlookObj = Nothing
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)
- 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