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.