VBA – Create an Outlook Contact

The following procedure illustrates how easily one can use VBA to create an Outlook Contact using Late binding so no references are required. Also note, there are many more contact properties available to the VBA programmer, for a full list consult the Outlook Help file.

'---------------------------------------------------------------------------------------
' Procedure : AddOlContact
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Create a new Contact item in Outlook
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' None, I need to convert this into a proper function, but for right now I leave that
' to you.  This is a great starting point.
'
' Usage:
' ~~~~~~
' Call AddOlContact
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         Unknown                 Initial Release
' 2         2010-09-03              Website Release
' 3         2018-08-28              Added Early/Late Binding Option
'                                   Updated Error Handling Slightly
'                                   Added Picture demonstration
'                                   Updated Copyright
'---------------------------------------------------------------------------------------
Public Sub AddOlContact()
    'Ref: https://docs.microsoft.com/en-us/office/vba/api/outlook.contactitem
    On Error GoTo Error_Handler
    #Const EarlyBind = False    'True  = Use Early Binding
                                'False = Use Late Binding
    #If EarlyBind = True Then
        'Early Binding Declarations
        'Requires Ref to Microsoft Outlook XX.X Object Library
        Dim oOutlook          As Outlook.Application
        Dim olContact         As Outlook.ContactItem
    #Else
        'Late Binding Declaration/Constants
        Dim olApp             As Object
        Dim olContact         As Object
        Const olContactItem = 2
    #End If

    Set olApp = CreateObject("Outlook.Application")
    Set olContact = olApp.CreateItem(olContactItem)

    With olContact
        .FirstName = "Daniel"
        .LastName = "Alba"
        .FullName = "Alba, Daniel"
        .FileAs = "D. Alba"
        .Anniversary = #7/22/1975#
        .JobTitle = ""
        .CompanyName = "MINI CARDA"
        .BusinessAddressStreet = "22 ClearPoint"
        .BusinessAddressCity = "Pointe-Claire"
        .BusinessAddressState = "Quebec"
        .BusinessAddressCountry = "Canada"
        .BusinessAddressPostalCode = "H9X 3A6"
        .BusinessTelephoneNumber = "(514) 488-0956"
        .BusinessFaxNumber = ""
        .Email1Address = "mini@mini.com"
        .MobileTelephoneNumber = ""
        .AddPicture ("C:\Users\ItsMe\Pictures\home-2955065_960_720.jpg")
        .Save    
        '.Display 'Uncomment if you wish the user to see the contact pop-up
    End With

Error_Handler_Exit:
    On Error Resume Next
    If Not olContact Is Nothing Then Set olContact = Nothing
    If Not olApp Is Nothing Then Set olApp = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: AddOlContact" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Update

Things have changed since the article was first published. If you are experiencing issues with the CreateObject(… line, then see my post entitled: CreateObject(“Outlook.Application”) Does Not Work, Now What?

VBA – Create an Outlook Task

Below is a simple function which permits you to use VBA to add a task into Outlook using late binding, so no references are required!

'---------------------------------------------------------------------------------------
' Procedure : AddOlTask
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Create a task in Outlook using late binding (no need for references)
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sSubject          : Task subject
' sBody             : Task body (the actual content message in the task)
' dtDueDate         : Task due date
' dtReminderDate    : Task reminder date/time
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' ? AddOlTask("Meeting with Daniel","Meeting w/ Daniel to discuss the database.", _
'             #5/31/2010#, #5/29/2010 9:00 AM#)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-May-20                 Initial Release
'---------------------------------------------------------------------------------------
Function AddOlTask(sSubject As String, sBody As String, _
                    dtDueDate As Date, _
                    dtReminderDate As Date)
On Error GoTo Error_Handler

    Const olTaskItem = 3
    Dim OlApp As Object
    Dim OlTask As Object
    
    Set OlApp = CreateObject("Outlook.Application")
    Set OlTask = OlApp.CreateItem(olTaskItem)
    
    With OlTask
        .Subject = sSubject
        .DueDate = dtDueDate
        .Status = 1                 '0=not started, 1=in progress, 2=complete, 3=waiting,
                                    '4=deferred
        .Importance = 1             '0=low, 1=normal, 2=high
        .ReminderSet = True
        .ReminderTime = dtReminderDate
        .Categories = "Business" 'use any of the predefined Categorys or create your own
        .Body = sBody
        .Save   'use .Display if you wish the user to see the task form and make
                'them perform the save
    End With

Error_Handler_Exit:
    On Error Resume Next
    Set OlTask = Nothing
    Set OlApp = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: AddOlkTask" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Update

Things have changed since the article was first published. If you are experiencing issues with the CreateObject(… line, then see my post entitled: CreateObject(“Outlook.Application”) Does Not Work, Now What?

VBA – Send HTML Emails Using Outlook Automation

Creating HTML Emails From Access using VBAVery similarily to the VBA Outlook Automation procedure, one need only make a slight modification.

Simply replace the line:

.Body = strBody

with

.HTMLBody = strBody

In which case, the sBody must now be in HTML format “Your content goes here” . Do not forget the HTML and BODY tags. The .HTMLBody can use most basic HTML Tags so go wild creating your messages (<i></i>, <b></b>, <br />, <p>, <h1>…<h6>, <a href>, <img>, <src>, …)

So our new HTML Email procedure would be:

Continue reading

VBA – Send Emails Using Outlook Automation

---------------------------------------------------------------------------------------
' Procedure : SendEmail
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Automate Outlook to send emails with or without attachments
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strTo         To Recipient email address string (semi-colon separated list)
' strSubject    Text string to be used as the email subject line
' strBody       Text string to be used as the email body (actual message)
' bEdit         True/False whether or not you wish to preview the email before sending
' strBCC        BCC Recipient email address string (semi-colon separated list)
' AttachmentPath    single value or array of attachment (complete file paths with
'                   filename and extensions)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2007-Nov-16             Initial Release
'---------------------------------------------------------------------------------------
Function SendEmail(strTo As String, strSubject As String, strBody As String, bEdit As Boolean, _
                   Optional strBCC As Variant, Optional AttachmentPath As Variant)
'Send Email using late binding to avoid reference issues
   Dim objOutlook As Object
   Dim objOutlookMsg As Object
   Dim objOutlookRecip As Object
   Dim objOutlookAttach As Object
   Dim i As Integer
   Const olMailItem = 0
   
   On Error GoTo ErrorMsgs

   Set objOutlook = CreateObject("Outlook.Application")

   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
   With objOutlookMsg
      Set objOutlookRecip = .Recipients.Add(strTo)
      objOutlookRecip.Type = 1
      
      If Not IsMissing(strBCC) Then
        Set objOutlookRecip = .Recipients.Add(strBCC)
        objOutlookRecip.Type = 3
      End If
      
      .Subject = strSubject
      .Body = strBody
      .Importance = 2  'Importance Level  0=Low,1=Normal,2=High
      
      ' Add attachments to the message.
      If Not IsMissing(AttachmentPath) Then
        If IsArray(AttachmentPath) Then
           For i = LBound(AttachmentPath) To UBound(AttachmentPath)
              If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then
                Set objOutlookAttach = .Attachments.Add(AttachmentPath(i))
              End If
           Next i
        Else
            If AttachmentPath <> "" Then
                Set objOutlookAttach = .Attachments.Add(AttachmentPath)
            End If
        End If
      End If

      For Each objOutlookRecip In .Recipients
         If Not objOutlookRecip.Resolve Then
            objOutlookMsg.Display
         End If
      Next
      
      If bEdit Then 'Choose btw transparent/silent send and preview send
        .Display
      Else
        .Send
      End If
   End With
   
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
   Set objOutlookRecip = Nothing
   Set objOutlookAttach = Nothing

ErrorMsgs:
   If Err.Number = "287" Then
      MsgBox "You clicked No to the Outlook security warning. " & _
      "Rerun the procedure and click Yes to access e-mail " & _
      "addresses to send your message. For more information, " & _
      "see the document at http://www.microsoft.com/office" & _
      "/previous/outlook/downloads/security.asp."
      Exit Function
   ElseIf Err.Number <> 0 Then
      MsgBox Err.Number & " - " & Err.Description
      Exit Function
   End If
End Function

Update

Things have changed since the article was first published. If you are experiencing issues with the CreateObject(… line, then see my post entitled: CreateObject(“Outlook.Application”) Does Not Work, Now What?

Outlook Automation – Bypass Outlook’s Security Prompt

Once you finally get your Outlook automation up and running you will notice that your users receive a security prompt each time your database attempts to send an automated e-mail. Obviously, as a programmer this is not the ideal situation and there are a couple of options available to you.

What you need to know before going any further
The security prompt appears for a reason. MS added this prompt to try and stop malicious usage of peoples email without their knowledge and/or consent. Many virii were propagating themselves through peoples email. This type of added security will hinder this type of attack. As a developper you must weigh this carefully when considering implementing any of the following workarounds.

 

Outlook Redemption

This solution is often mentioned in numerous Forums. For more information refer to http://www.dimastr.com/redemption/home.htm

 

vbMAPI

Another alternative is vbMAPI which you can review at https://www.everythingaccess.com/vbmapi.asp.  I have no personal experience with this product, but it has been highly recommended to me by multiple very reliable source.

 

CDO

Another commonly refered to solution found in numerous Discussion Forums. The following website provides several example of programming with CDO.

http://www.devhut.net/2014/11/29/vba-cdo-mail/

 

Thrid Party Emailing Utilities

In my past life, when I was working for a multinational company, the work around that I implemented was to use BLAT to send the emails. I used VBA to produce a text file based on this information from within the database and it in turn was utilized by BLAT to send the email. It worked seemlessly! Best of all it is free.

 

VBScript?

Someone once mentioned that they could send emails using VBScripts without getting the security prompt. This said, I have never validated this yet.

 

Third Party Application to Automate the Confirmation

There are several little utilities available online that you can install on your clients computers that will simply automatically Click the Yes button to authorize that the email be sent. One of these programs is ClickYes. This however, bypasses security completely and could be considered a serious security issue.

VBA – Automating Outlook

Automating Outlook from Access has become a more and more requested feature. The link provided below is the best source of information on this subject with complete code examples. However, before launching into automating Outlook to send emails from Access make sure that the DoCmd.SendObject method does not suffice your needs as it is much simpler to code.

https://web.archive.org/web/20061218070602/http://msdn2.microsoft.com/en-us/library/aa159619(office.11).aspx (Web Archive version as Microsoft has removed the original page)

MS Access – Splitting and Deploying Your Database

Splitting and Deploying Your Database

So you’ve developed a database and now you are finally ready to deploy it to your users! There are a couple things to consider to simplify your life as much as possible.

 

Split Your Database

Split? Yes, if you have not done so, and you are setting up a network or multi-user database, it is time to split your database. This is a simple process by which the tables are placed into one database (called the Back-End) and the remaining database objects (queries, form, report, code) are placed into another database (called the Front-End). The Back-End is then placed on the office server and a copy of the Front-End is given to each user and setup on their PC. DO NOT allow your users to all connect using the same Front-End file, they should each have their own copy on their machine.

Continue reading

MS Access – Multiple Criteria DLookup

A common question in many a newsgroup/forum is how can I perform a DLookup with more than one criteria? Well, the fact of the matter is that the Criteria permits you to specify as many criterion as you please.

For instance, lets say we have a contact table named ‘tbl_contacts’ and we want to retrieve the Telephone Number (field TelNo) for Daniel (Field FirstName) Pineault (field LastName), then the code would look something like:

DLookup("[TelNo]", "tbl_contacts", "[FirstName]='Daniel' AND [LastName]='Pineault'")

Now let’s push this example a little further. Let’s assume instead of wanting to hard code the individual’s name, we want to pull the value from form controls. So let assume we have a form name ‘frm_contacts’ with controls named ‘txt_FirstName’ and ‘txt_LastName’, in that case the code would look something like:

DLookup("[TelNo]", "tbl_contacts", "[FirstName]='" & Forms![frm_contacts].Form.[txt_FirstName] & "' AND [LastName]='" & Forms![frm_contacts].Form.[txt_LastName] & "'")

Special Note

One important thing to properly grasp when building a criteria is how and if they need to be surrounded by any special characters.

  • Date values need to be surrounded by #Your Date Value#
    • “[DateField]=#10/17/2009#”
  • Text values need to be surrounded by ‘Your Text Value’
    • “[TextField]=’Daniel'”
  • Numeric values do not need to be surrounded by anything
    • “[NumericField]=200”

VBScript – Create/Set Trusted Location Using VBScript

I looked high and low and had an impossible time, when I needed it, to locate an example, or explanation, of how I could create a Trusted Location for Access, Excel, Word,… using a simple vbscript.

If you manually make an entry in the Trusted Locations and then inspect your registry, you’ll see something similar to the following image (in this case for MS Access, but the same principal applies to almost all MS Office applications)

MS Office Trusted Location Registry Keys

Continue reading

VBA – Converting Between Decimal and Binary

Quite some time ago I had a particular need to convert Decimal to Binary and vice versa. It took a little digging but eventually came across this little gem and thought it was worth posting for others to use.

'Decimal To Binary
' =================
' Source: http://groups.google.ca/group/comp.lang.visual.basic/browse_thread/thread/28affecddaca98b4/979c5e918fad7e63
' Author: Randy Birch (MVP Visual Basic)
' NOTE: You can limit the size of the returned
'              answer by specifying the number of bits
Function Dec2Bin(ByVal DecimalIn As Variant, _
              Optional NumberOfBits As Variant) As String
    Dec2Bin = ""
    DecimalIn = Int(CDec(DecimalIn))
    Do While DecimalIn <> 0
        Dec2Bin = Format$(DecimalIn - 2 * Int(DecimalIn / 2)) & Dec2Bin
        DecimalIn = Int(DecimalIn / 2)
    Loop
    If Not IsMissing(NumberOfBits) Then
       If Len(Dec2Bin) > NumberOfBits Then
          Dec2Bin = "Error - Number exceeds specified bit size"
       Else
          Dec2Bin = Right$(String$(NumberOfBits, _
                    "0") & Dec2Bin, NumberOfBits)
       End If
    End If
End Function

'Binary To Decimal
' =================
Function Bin2Dec(BinaryString As String) As Variant
    Dim X As Integer
    For X = 0 To Len(BinaryString) - 1
        Bin2Dec = CDec(Bin2Dec) + Val(Mid(BinaryString, _
                  Len(BinaryString) - X, 1)) * 2 ^ X
    Next
End Function