Using Automation to Send a Microsoft Outlook Message
Retired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q161088
Advanced: Requires expert coding, interoperability, and multiuser skills.
Expand all | Collapse all
-------------------------------------------------
SUMMARY
-------------------------------------------------
This article shows you how to use Automation to create and send a Microsoft Outl...
This article shows you how to use Automation to create and send a Microsoft Outlook message in Microsoft Access 97.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.

For more information about using Automation to send a Microsoft Exchange message, please see the following article in the Microsoft Knowledge Base:
153311 (http://support.microsoft.com/kb/153311/EN-US/ ) Using Automation to Send a Microsoft Exchange Message
Back to the top
-------------------------------------------------
MORE INFORMATION
-------------------------------------------------
NOTE: The following code may not work properly if you have installed the Outlook...
NOTE: The following code may not work properly if you have installed the Outlook E-mail Security Update. For additional information about this update, please see one of the following articles in the Microsoft Knowledge Base, depending on which version of Outlook you have:
262631 (http://support.microsoft.com/kb/262631/EN-US/ ) OL2000: Information About the Outlook E-mail Security Update
262617 (http://support.microsoft.com/kb/262617/EN-US/ ) OL98: Information About the Outlook E-mail Security Update
The SendObject method provides a way to send a MAPI mail message programmatically in Microsoft Access. However, the SendObject method does not give you access to complete mail functionality, such as the ability to attach an external file or set message importance. The example that follows uses Automation to create and send a mail message that you can use to take advantage of many features in Microsoft Outlook that are not available with the SendObject method.

There are six main steps when you use Automation to send a Microsoft Outlook mail message: * Initialize the Outlook session * Create a new message * Add the recipients (To, CC, and BCC) and resolve their names * Set valid properties, such as the Subject, Body, and Importance * Add attachments (if any) * Display/Send the message
To send a Microsoft Outlook mail message programmatically, follow these steps: 1. Create a sample text file named Customers.txt in the C:\My Documents folder. 2. Start Microsoft Access and open the sample database Northwind.mdb. 3. Create a module and type the following line in the Declarations section if it is not already there:
Option Explicit 4. On the Tools menu, click References. 5. In the References box, click the Microsoft Outlook 8.0 Object Model and then click OK.

NOTE: If the Microsoft Outlook 8.0 Object Model does not appear in the Available References box, browse your hard drive for the file Msoutl8.olb. If you cannot locate this file, you must run the Microsoft Outlook Setup program to install it before you proceed with this example. 6. Type the following procedure in the new module: 7. Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath) 8. Dim objOutlook As Outlook.Application 9. Dim objOutlookMsg As Outlook.MailItem 10. Dim objOutlookRecip As Outlook.Recipient 11. Dim objOutlookAttach As Outlook.Attachment 12. 13. ' Create the Outlook session. 14. Set objOutlook = CreateObject("Outlook.Application") 15. 16. ' Create the message. 17. Set objOutlookMsg = objOutlook.CreateItem(olMailItem) 18. 19. With objOutlookMsg 20. ' Add the To recipient(s) to the message. 21. Set objOutlookRecip = .Recipients.Add("Nancy Davolio") 22. objOutlookRecip.Type = olTo 23. 24. ' Add the CC recipient(s) to the message. 25. Set objOutlookRecip = .Recipients.Add("Michael Suyama") 26. objOutlookRecip.Type = olCC 27. 28. ' Add the BCC recipient(s) to the message. 29. Set objOutlookRecip = .Recipients.Add("Andrew Fuller") 30. objOutlookRecip.Type = olBCC 31. 32. ' Set the Subject, Body, and Importance of the message. 33. .Subject = "This is an Automation test with Microsoft Outlook" 34. .Body = "This is the body of the message." &vbCrLf & vbCrLf 35. .Importance = olImportanceHigh 'High importance 36. 37. ' Add attachments to the message. 38. If Not IsMissing(AttachmentPath) Then 39. Set objOutlookAttach = .Attachments.Add(AttachmentPath) 40. End If 41. 42. ' Resolve each Recipient's name. 43. For Each ObjOutlookRecip In .Recipients 44. objOutlookRecip.Resolve 45. Next 46. 47. ' Should we display the message before sending? 48. If DisplayMsg Then 49. .Display 50. Else 51. .Save 52. .Send 53. End If 54. End With 55. Set objOutlook = Nothing 56. End Sub 57. To test this procedure, type the following line in the Debug window, and then press ENTER.
SendMessage True, "C:\My Documents\Customers.txt"
Note that a new message is displayed in Microsoft Outlook with an attachment.

To send the message without displaying it in Microsoft Outlook, call the procedure with a False value for the first argument:
SendMessage False, "C:\My Documents\Customers.txt"
To send the message without specifying an attachment, omit the second argument when calling the procedure.
SendMessage True
Back to the top

TOPIC: How to automate Outlook from Excel to send emails with optional attachment
How to automate Outlook from Excel to send emails with optional attachment 8 months, 1 week ago #13 Start by creating a new Excel VBA project, to do this from Excel hit ALT+F11 to open the VBA editor. Then In the properties window right click on the VBA project and add a new module. Go to preferences in the tools menu and add a reference to the Microsoft Outlook object library.

Copy and paste the code below into this module, the code been commented with explanations on what it does:

Sub runMail() 'this is the main macro that will call the OutlookMailSender sub
'you can call OutlookMailSender without an attachment
Call OutlookMailSender
'or specify an optional attachment to the email
'Call OutlookMailSender("c:\pathtoyourattachment.txt")
End Sub

Sub OutlookMailSender(Optional attachment)
'this is where the real work is done

'first declare the objects and variables we will use later
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.recipient
Dim objOutlookAttach As Outlook.attachment
Dim bodytext As String
Dim recipient As String

'this is the text inside the email bodytext = "Hi " & vbNewLine & vbNewLine & vbNewLine & _
"This email was sent by Excel automation"

recipient = "recipient@somewhere.com"

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(recipient) objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message. You don't have to have this if you don't need it
Set objOutlookRecip = .Recipients.Add("edgarbadawy@there.com.au") objOutlookRecip.Type = olCC

' Add the BCC recipient(s) to the message. You don't have to have this if you don't need it
Set objOutlookRecip = .Recipients.Add("edgarbadawyboss@there.com.au") objOutlookRecip.Type = olBCC

' Set the Subject, Body, and Importance of the message.
.Subject = "your email subject"
.Body = bodytext
.Importance = olImportanceHigh 'High importance, if you need it

' Add attachments to the message, if one was specified from the calling function in runMail()
If Not IsMissing(attachment) Then
Set objOutlookAttach = .Attachments.Add(attachment)
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
'this returns false if Outlook could not resolve the email address
If Not objOutlookRecip.Resolve Then MsgBox "Could not resolve the email for " & objOutlookRecip
Next

' Should we display the message before sending? You can use a Boolean here instead of true. As is the email will be displayed all the time.
If True Then
.Display
Else
'if false above then the email is saved in the sent items folder and sent
.Save
.Send
End If

End With

Set objOutlook = Nothing

End Sub | * roller * * OFFLINE * Administrator * * Posts: 42 * Karma: 0 * | Reply Quote | |
Re: How to automate Outlook from Excel to send emails with optional attachment 8 months, 1 week ago #14 For those of you who don't know much about VBA or would like to receive a working Excel file with all the coding done I have an option where you can download just that for AU$3.50.

Obviously paying $3.50 dollars isn't what many would like to do but it's only a small amount, you can think of it as a donation.

Some might want to save time doing the coding or might like to improve their VBA skills then it is worth the money. Payment is via Paypal...

The link to download the file is here:

fracta.net/fractor/products/index.php?ma...=1&products_id=3

And before you download it you might like to take a look at the operating instruction and some screen shots below:

Excel to Outlook email automation

This VBA Excel macro is provided as a skeleton so you can send multiple emails from an Excel list.
Edit the code to suit your needs. This macro was written and tested in Excel 2007.

Instructions:

Open the Excel file and enable macros
On sheet 1 enter the email details; from email address in column A, Subject in column B, etc...

If you want to add an attachment to your email enter a valid attachment path in column F otherwise leave it blank. If you enter an attachment path then he attachment path must be valid otherwise your macro will crash; you can add some error handling if you like to the code.

In column G select one of three options; send, toDraft or display.
Send will have the email sent without you seeing it on the screen. It can be found in your sent items.

toDraft will save the email in the draft folder in Outlook so you can send it later.

Display will generate and display the email, you can click send manually to send it later.

To run the macro click on the blue button to the right of the columns.

Depending on your Outlook setting, Outlook may prompt you with a message that another program is trying to send emails. You should click "Allow", this is a security warning built into Outlook so that no an-authorised emails are send via a third party application. If you want to avoid these messages then you can create a digital certificate on your machine and sign the VBA project with it, and then add this certificate to your trusted publishers. Or you can read this post on how to disable the warning:

www.fracta.net/fracta/index.php/forum/2-...-programmatically#56

To report any bugs or error please contact www.frata.net. If you like any paid work done and customisation please write to us.

This program is provided as is with no warranty. No liability shall be taken for anything. By using this program you agree to these simple plain English conditions. |