Skip to main content

Programmatically send email in Access using VBA and Outlook

When I first wrote my early Access system I used a function I setup in Outlook and then I called that. On upgrading I had a few problems with the older method then I found some help on the MSDN website here... and the related video.

The first thing to do before starting the code is go into access, access the VBA (press Ctl+G) then click on "Tools" The "References" and then make sure you scroll down to "Microsoft Outlook ..." the version there will be determined by what version you have installed. This the code that I ended up with after looking at the site above

Private olApp As Outlook.Application
Private olNameSpace As Outlook.NameSpace

' The basics of this from MSDN site
' http://msdn.microsoft.com/en-us/library/ee208547(v=office.12).aspx
Private Sub InitOutlook()
 ' Initialize a session in Outlook
  Set olApp = New Outlook.Application

  'Return a reference to the MAPI layer
  Set olNameSpace = olApp.GetNamespace("MAPI")

  'Let  user logon to Outlook with the Outlook Profile dialog box
  ' if not already logged in and then create a new session
  olNameSpace.Logon , , True, False
End Sub

Private Sub CleanUp()
  ' Clean up public object references.
  Set olNameSpace = Nothing
  Set olApp = Nothing
End Sub

'SendMAPIEmailto replace the older one
Public Function SendMAPIEmail(strTo As String, _
          strSubject As String, _
          strMessageBody As String, _
          Optional strAttachmentPaths As String, _
          Optional strCC As String, _
          Optional strBCC As String, _
          Optional strReplyTo As String, _
          Optional dtDTWhen As Date) As Boolean
        
  Dim mailItem As Outlook.mailItem
  Dim bSuccess As Boolean

  ' assume success but set error trap
  bSuccess = True

On Error GoTo Abort

  ' if calling in a loop perhaps remove then clean, at end of loop
  InitOutlook
  If Not olApp Is Nothing Then
   Set mailItem = olApp.CreateItem(olMailItem)

   mailItem.To = strTo
   mailItem.Subject = strSubject

   ' mailItem.Display
   mailItem.HTMLBody = strMessageBody
   '------------ add all the optional items
   ' attachments
   If Not IsMissing(strAttachmentPaths) Then
    If (strAttachmentPaths <> "") Then
     Dim myAttachements As Outlook.Attachments
     Set myAttachements = mailItem.Attachments
     ' may need a little more work here
     myAttachements.Add strAttachmentPaths
    End If
   End If
   ' CC string
   If Not IsMissing(strCC) Then
    If strCC <> "" Then
     mailItem.CC = strCC
    End If
   End If
   ' BCC string
   If Not IsMissing(strBCC) Then
    mailItem.BCC = strBCC
   End If
   ' ReplyTo string
   If Not IsMissing(strReplyTo) Then
    mailItem.ReplyRecipients.Add strReplyTo
   End If
   ' dtDTWhen string
   If Not IsMissing(dtDTWhen) Then
    mailItem.DeferredDeliveryTime = dtDTWhen
   End If
'   When debugging this is useful
'   mailItem.Display

   mailItem.Send

   GoTo EndSend
  End If

' if we get here then something wne wrong
Abort:
  bSuccess = False
' clean up and exit
EndSend:
  CleanUp
  SendMAPIEmail = bSuccess

End Function
I prefer to use MAPI, since then I can see a log of what has been sent to whom. If you do that use SMTP, then you need to place the email results in a log of some sort.

Comments

Popular posts from this blog

Bitcoin / Cryptocurrency – what is it and how can I benefit

What is it I started investigating Bitcoin when it was worth just over $1000 a bitcoin. I was interested in what it was and how it worked. A lot of people are saying we missed the boat, but I believe that everyone should at least try put a little money in now, or at least use a faucet (see below) to make a little micro-currency. You can read a Wiki article about bitcoin and its history etc. But what you need to know is that it is a currency, that is independent of country. No one really knows who invented the concept of a cryptocurrency since the person who published the paper used a nom de plume. All new cryptocurrencies work more or less the same way as Bitcoin. So as I explain below I interchange these terms. Bitcoin is the original cryptocurrency. How Bitcoin works The currency releases a coin based on a mathematical formula. There will never be more than 21 million bitcoins (other cryptocurrencies do not work like this). Each bitcoin can have divided into one hundred mil

Migrating QuickBooks to Sage One Cloud accounting - Part 1 Exporting the data

Some notes Sage means sage one online accounts, wherever we say Sage we mean Sage one. The QuickBooks we used was version 2012 Professional. But most of the information is similar. What you need > You need the templates from Sage and the data from QuickBooks, see below for how to do these. Get Import Templates from Sage To get the templates for the items go to help.accounting.sageone.co.za/en_za/accounting/from-your-previous-accounting-system.html Although you can construct the templates from the information in this post you can download samples of the templates need. Below are the links they provide: Use the following downloads which are referenced in the guide: General Ledger Accounts Import Template Customer Import Template Customer Outstanding Invoices Import Template Supplier Import Template Supplier Outstanding Invoices Import Template Item Import Template   Another useful page that is hard to find on their system is: help.accounting.sageone.co.za/en_za/ac

Mindfullness Meditation and Depression - in a pod cast

Over the last 3 years I have found that meditation has really assisted me, in resolving the depression I have had. I have planned to write about my experience for a while, and hopefully will get around to doing that. However today having listen to the latest podcast by Dan Harris on 10% happier, so many things just clicked in to place, so I want to share it. To understand what is covered in the podcast I would recommend you understand what mindfulness meditation is, and what the default mode network in the brain is (see links below). Listen to the pod cast - but here are some extracts (which I do not have permission to publish - and will remove if asked). Link to podcast: tumello.com/listen/H11a5NYJf; or itunes.apple.com/us/podcast/10-happier-with-dan-harris/id1087147821 Chuck Raison, a psychiatrist and a professor of psychiatry at the University of Wisconsin-Madison School of Medicine and Public Health, and Vlad Maletic, a clinical professor of neuropsychiatry and behavior sc