Love it or hate it, email is everywhere, so the demand for integrating email into simple applications can be pretty strong. I thought I'd share three simple methods by which you can send emails using VBA in any Microsoft office product. Thus allowing your users can annoy everyone with lovingly crafted emails at the push of a button.
I will outline two approaches to sending emails, advise as to best practice, and provide full code for implementation. Its all very simple, so all skill-levels should find it easy enough to follow (despite the horribly confusing articles on other websites).
The Two Approaches to Sending Email
The two approaches are: 1. Communicating directly with a mail server (such as Microsoft Exchange Server), or 2. using the API of a client application (such as Lotus Notes or Outlook).
Using the client API binds your code to the email application running on a user's machine. The danger of this is that if you move from Lotus Notes to Microsoft Outlook the emailing routine is no longer going to work!
The benefit of going directly to the server is that how you communicate with the server doesn't change regardless of whether you're using Microsoft Exchange, Google Apps, or Lotus Domino. However, some companies heavily lock-down their mail servers as a way to increase security, so this type of communication is not always an option (although it is in most cases if you ask the right person).
Clearly the ideal way to send an email is to communicate directly with the mail server. SMTP is the ideal protocol for doing this (Simple Mail Transfer Protocol), so that is what I'll show you.
For the sake of completeness I will also demonstrate how to send an email with both the Microsoft Outlook and IBM Lotus Notes APIs. Although let me stress again that these should NOT be used by default.
SMTP (The Best Way)
All windows distributions from Windows 2000 onwards include a COM API component called Collaboration Data Objects (CDO). This api wraps the underlying messaging API and allows us to very simply manipulate email objects.
Here is the code:
Public Sub EmailViaCDO()
Dim sentFrom As String
Dim serverAddress As String
Dim messageBody As String
Dim sendTo As String
Dim userName As String
Dim password As String
Dim subject As String
'This stuff would normally be passed into the method as parameters
sentFrom = "matthew.rathbone@example.com"
serverAddress = "server.example.com"
sendTo = "example@email.com"
messageBody = "This is a test email"
subject = "message subject"
Dim message As New CDO.message
Dim config As New CDO.Configuration
'set up the configuration for the email server
config.Fields(cdoSendUsingMethod).value = cdoSendUsingPort
config.Fields(cdoSMTPServer).value = serverAddress
config.Fields(cdoSMTPUseSSL).value = False
config.Fields(cdoSMTPConnectionTimeout).value = 10
'Only use these if the server requires authentication
' config.Fields(cdoSMTPAuthenticate).Value = cdoBasic
' config.Fields(cdoSendUserName).value = userName
' config.Fields(cdoSendPassword).value = password
'---------------
'update the config before using it
config.Fields.Update
Set message.Configuration = config
message.To = sendTo
message.from = sentFrom
message.subject = subject
message.TextBody = messageBody
message.AddAttachment ("C:\attachment.txt")
'Away goes our message!
message.Send
End Sub
Microsoft Outlook
Because Outlook also has VBA, using outlook from any VBA-enabled application is actually very easy, see below:
Public Sub EmailViaOutlook()
' requires a reference to the Microsoft Outlook 8.0 Object Library
' creates and sends a new e-mail message with Outlook
Dim outlookFolder As Outlook.MAPIFolder
Dim recipient As Outlook.recipient
Dim mailItem As Outlook.mailItem
'this gets an existing outlook
Set outlookFolder = GetObject("", _
"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
' lets create a new email message
Set mailItem = outlookFolder.Items.Add
mailItem.subject = "this is the subject"
mailItem.Body = "This is the message I'm sending!"
mailItem.Attachments.Add "C:\attachment.txt"
'SOME OPTIONAL STUFF
mailItem.OriginatorDeliveryReportRequested = True ' delivery receipt
mailItem.ReadReceiptRequested = True ' read receipt
'simply add a recipient
mailItem.recipients.Add "someoneelse@example.com"
'or add one then set some properties
Set recipient = mailItem.recipients.Add("someone@example.com")
recipient.Type = olCC
'Finally, send our email
mailItem.Send
'or save it for later
'mailItem.Save
End Sub
Lotus Notes
After using SMTP and Outlook, using the lotus API is not quite as pretty. Lets be honest, its down-right horrible. Regardless of this, here is the code:
Sub SendMailViaLotusNotes()
'Method:
' open a lotus notes session
' open the notes database
' use the db to create a new mail document
' assign the key properties to the mail document
' create a rich-text item for the mail body (this allows us to have nicer emails)
' write our email body
' send the document
'Requires reference to: "Lotus Domino Objects"
Dim dominoSession As New NotesSession
Dim mailDatabase As NotesDatabase
Dim newMemo As NotesDocument
Dim richText As NotesRichTextItem
Dim strAttachment As String
dominoSession.Initialize ("") 'Prompts user for Password
Set mailDatabase = dominoSession.GetDatabase("", "names.nsf")
Set newMemo = mailDatabase.CreateDocument
newMemo.AppendItemValue "Form", "Memo"
newMemo.AppendItemValue "SendTo", "matthew.rathbone@example.co.uk"
newMemo.AppendItemValue "Subject", "This is the subject"
Set richText = newMemo.CreateRichTextItem("Body")
richText.AppendText "This is my email text"
newMemo.Send False
End Sub
There's probably a better way to do this using the lotus API, or at least I hope there is!
Hopefully these snippets of code have proven useful, feel free to use them in anyting you wish, although whenever possible please credit me with a link. If you have any questions, feel free to contact me.