Outlook Email using VBA (Updated)

This is an update to the code supplied here.  The previous article enabled you to send an email though Outlook using early binding.  The code below uses late binding, this means you do not need to worry about Outlook versions.
As before it includes the subject and message body fields as well as the To, Cc and Bcc fields within a standard email format. 
The code could easily be changed to loop through arrays or collections of values for many of these fields.
Private Sub SendOutlookEmail()
'-- Creates and sends a new e-mail message with Outlook --

    ' These are members of the referenced object model which _
      are unavailable due to late binding.  They have been _
      replaced with the numbers they represent
    Const olMailItem    As Integer = 0
    Const olTo          As Integer = 1
    Const olCc          As Integer = 2

    Dim oOutlook        As Object 'Outlook Application
    Dim oMailMsg        As Object 'Outlook MailItem
    Dim oRecipient      As Object 'Outlook Recipient
    Dim oRecipType      As Object 'Outlook Recipient Type

    On Error GoTo ErrTrap

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

    ' Create the message
    Set oMailMsg = oOutlook.CreateItem(olMailItem)

    With oMailMsg
        ' Request a receipt ?
        .ReadReceiptRequested = False
        ' Keep copy in 'Sent Items' ?
        .DeleteAfterSubmit = False
        ' Your email address or 'team mailbox' address
        .SentOnBehalfOfName = "YourEmail@Address.com"
        ' Message subject
        .Subject = "Subject Here"
        ' Add message body
        .Body = "Email Message Here"

         'Add 'To' recipient(s)
        Set oRecipient = .Recipients.Add _
            ("SomeoneElses@EmailAddress.com; AnotherPerson@Email.com")
        oRecipient.Type = olTo
         'Add another 'To' recipient
        Set oRecipient = .Recipients.Add _
            ("SomeoneElses@EmailAddress.com")
        oRecipient.Type = olTo
        ' Add 'Cc' recipient(s)
        Set oRecipient = .Recipients.Add _
            ("SomeoneElses@EmailAddress.com")
        oRecipient.Type = olCc

        ' Loop through an array to attach files
        For l = 0 To UBound(varFile)
            If Not IsEmpty(varFile(l)) Then
                .Attachments.Add varFile(l)
            End If
        Next l

        ' Display to user or Send email
        '.Send
        .Display

    End With

ErrTrap:
    Set oOutlook = Nothing
    Set oMailMsg = Nothing
    Set oRecipient = Nothing
    Set oRecipType = Nothing

    Select Case Err.Number
        Case Is = 0
            ' All okay, continue
        Case Is = -284147707
            MsgBox "You have exceeded the storage limit on your mailbox. " _
                & "Delete some mail from your mailbox or contact your " _
                & "system administrator to adjust your storage limit." _
                 , vbInformation, "Error Message"
        Case Else
            MsgBox "An error has occured:" & vbCrLf & vbCrLf _
                & Err.Number & " - " & Err.Description, "OutlookEmailFunction"
    End Select
End Sub

As always, if you have any questions do let us know via the comments section.

Lotus Notes Email using VBA

The code I’m sharing with you today will enable you to send an email using VBA through  Lotus Notes.  It uses late binding so you do not need to use references to Lotus Notes, which in turn means you do not need to worry about your users Lotus Notes version.  I say this, though in practise this may not always be the case.  Lotus Notes is often rather frustrating, as many users will testify :)

As well as the subject and message body the routine allows you to specify the To, Cc and Bcc fields within a standard email format. 

The example below shows the code to attach a single file. This could easily be amended to loop through an array or collection of file addresses.

Sub SendAnEmailViaLotusNotes()

    Dim WS As Object
    Dim Session As Object
    Dim DB As Object
    Dim uiDB As Object
    Dim NotesAttach As Object
    Dim NotesDoc As Object
    Dim RichTextBody As Object
    Dim RichTextAttachment As Object
    Dim StyleBold As Object
    Dim StyleNorm As Object
    Dim StyleUnderline As Object
    Dim StyleFont10 As Object
    Dim Server As String
    Dim MailFile As String
    Dim TheUser As String
    Dim UserSig As String

    Dim strEmailTo As String        ' email To field
    Dim strEmailCc As String        ' email Cc field
    Dim strEmailBcc As String       ' email Bcc field
    Dim strEmailSbj As String       ' email Subject
    Dim strEmailBdy As String       ' email Body (message text)
    Dim strEmailAtt As String       ' email attachment

    Application.DisplayAlerts = False   ' turn off Excel alerts

    On Error GoTo ErrorMsg              ' on error goto ErrorMsg section...

' --- Set-up connection to Lotus Notes and Create Email object
    Set WS = CreateObject("Notes.NotesUIWorkspace")
    Set Session = CreateObject("Notes.NotesSession")

    TheUser = Session.UserName
    UserSig = Session.CommonUserName
    Server = Session.GetEnvironmentString("MailServer", True)
    MailFile = Session.GetEnvironmentString("MailFile", True)

    Set DB = Session.GetDatabase(Server, MailFile)
    Set uiDB = WS.CURRENTDATABASE
    Set NotesDoc = DB.CreateDocument

    Set RichTextBody = NotesDoc.CreateRichTextItem("Body")

' --- Set-up dist list, message and attachments
    strEmailTo = ""
    strEmailCc = ""
    strEmailBcc = ""
    strEmailSbj = ""
    strEmailBdy = ""
    strEmailAtt = ""

    NotesDoc.SendTo = strEmailTo        ' To...
    NotesDoc.CopyTo = strEmailCc        ' Cc...
    NotesDoc.BlindCopyTo = strEmailBcc  ' Bcc...
    NotesDoc.Subject = strEmailSbj      ' The subject
    NotesDoc.Body = strEmailBdy         ' Any text to be in the email

    ' Attach a file
    If strEmailAtt <> "" Then
        Set RichTextAttachment = NotesDoc.CreateRichTextItem("Attachment")
        Set NotesAttach = RichTextAttachment.EmbedObject(1454, "", strEmailAtt)
    End If

' --- Send the email / save the message in 'Sent' items
    ' False would not save the sent email to the sent items folder
    NotesDoc.SAVEMESSAGEONSEND = True
    ' Not sure why, but false send the eamil ?
    NotesDoc.SEND False

' --- Close connection to free memory
    Set Session = Nothing
    Set DB = Nothing
    Set NotesAttach = Nothing
    Set NotesDoc = Nothing
    Set WS = Nothing

    ' Turn on Excel alerts
    Application.DisplayAlerts = True

    Exit Sub

' --- if an error occurs display a message... then exit the macro
ErrorMsg:
    If Err.Number = 7225 Then
        MsgBox "The file " & strEmailAtt & " cannot be found in the specified location", vbOKOnly, "Error"
    Else
        MsgBox Err.Number & Err.Description
    End If

    Application.DisplayAlerts = True

End Sub

As always, if you have any questions do let us know via the comments section.

Access, Email, Excel, Lotus Notes, VBA

Outlook Email using VBA

The code I’m sharing with you today will enable you to send an email using VBA through  Outlook.  It uses early binding so you will need to use a reference to Outlook.  You may need to look into what version(s) Outlook will be available to your users. 

As well as the subject and message body the routine allows you to specify the To, Cc and Bcc fields within a standard email format. 

The example below shows the code to attach four files using four different formats: Insert the file, insert a shortcut to the file, Embed and OLE.

The code could easily be changed to loop through arrays or collections of values for many of these fields.

Sub SendAnEmailWithOutlook()
'-- Requires a reference to the Microsoft Outlook 8.0 Object Library or higher --

    ' Creates and sends a new e-mail message with Outlook
    Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
    Dim ToContact As Outlook.Recipient

    Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    Set olMailItem = OLF.Items.Add ' creates a new e-mail message
    With olMailItem
        ' message subject
        .Subject = "This where the subject text goes..."

        ' add a recipient
        Set ToContact = .Recipients.Add("name@org.net")
        ' add another recipient
        Set ToContact = .Recipients.Add("name@org.net")
        ' set latest recipient as CC
        ToContact.Type = olCC
        ' add another recipient
        Set ToContact = .Recipients.Add("name@org.net")
        ' set latest recipient as BCC
        ToContact.Type = olBCC

        ' the message text with a line break
        .Body = "email message"

        ' insert attachment
        .Attachments.Add "C:\FolderName\Filename.txt", olByValue, , "filename goes here"
        ' insert shortcut
        .Attachments.Add "C:\FolderName\Filename.txt", olByReference, , "Shortcut to Attachment"
        ' embedded attachment
        .Attachments.Add "C:\FolderName\Filename.txt", olEmbeddeditem, , "Embedded Attachment"
        ' OLE attachment
        .Attachments.Add "C:\FolderName\Filename.txt", olOLE, , "OLE Attachment"

        .OriginatorDeliveryReportRequested = False ' True would be delivery confirmation
        .ReadReceiptRequested = False ' True would be read confirmation
        '.Save      ' saves the message for later editing
        '.Display   ' displays the email message for editing
        .Send       ' sends the e-mail message (puts it in the Outbox)

    End With

    Set ToContact = Nothing
    Set olMailItem = Nothing
    Set OLF = Nothing

End Sub

Hope the text wrapping doesn’t make it too awkward to read.
As always, if you have any questions do let us know via the comments section.

Edit (18-Dec-2010): Due to a ‘technical’ error this blog entry has changed from it’s original version.  The original explained how to send an email through Outlook using late binding, this post will be re-written and posted again soon.  Apologies for any inconvenience this may cause.