What is the VBA to send email from Access using a specific Email Account?

Posted on 2011-05-02
Last Modified: 2012-06-22
What is the VBA to send email from Access using a specific Email Account?

Question by:kosenrufu
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)

      There is none.  The sendobject command is the only statement in VBA that can send an e-mail and there is no option to specify an account.

      You'd need to use a 3rd party control, something like vbSendMail or BLAT, or control Outlook via Automation if you want more control over the SMTP send process.

    LVL 12

    Expert Comment

    LVL 26

    Accepted Solution

    This code works to format up and create email messages through Outlook from within Access.
    You need a reference to Outlook set


    OlOriginator (ie the FROM address can't be specified anymore.)
    See here

    You may be able to specify .SentOnBehalfOfName = "John Doe"
    Public Sub CreateAnEmail()
    'assumes DAO 3.6
    'Assumes reference to MS Outlook
    On Error Resume Next
    Dim db As Database
    Dim rs As Recordset
    Dim ClientEmail As String
    Dim DisplayMsg As Boolean
    Dim AttachmentPath As String
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim objOutlookExplorers As Outlook.Explorers
    Dim myarray() As String
    Dim myaddresses() As String
    Dim x As Integer
    Dim fs As Object
    Dim BuiltPath As String
    Dim response As Integer
    Dim WasOpen As Boolean
    MsgBox "The email is about to be created!" & vbCrLf & _
            "If nothing appears to be happening, the Outlook security box may be hiding behind an open window." & vbCrLf & _
            "Click the Outlook icon on the taskbar to bring it to the front, if necessary."
    Set objOutlook = GetObject(, "Outlook.Application")
    'MsgBox Err.Number & " " & Err.Description
    If Err.Number = 429 Then
        WasOpen = False
        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")
        WasOpen = True
    End If
        Dim ns As Outlook.NameSpace
        Dim Folder As Outlook.MAPIFolder
        Set ns = objOutlook.GetNamespace("MAPI")
        Set Folder = ns.GetDefaultFolder(olFolderInbox)
        Set objOutlookExplorers = objOutlook.Explorers
    If WasOpen = False Then
        objOutlook.Explorers.Add Folder
        'done opening
    End If
    AppActivate "Microsoft Outlook"
        'For x = 1 To objOutlook.Explorers.count
            'objOutlookExplorers.Item(x).WindowState = olMaximized
       ' Next x
    ' Create the message.
    'Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg
        'can't do this, the mail never gets sent
        ' Add the from recipient(s) to the message.
        'Set objOutlookRecip = .Recipients.Add("John Doe")
        'objOutlookRecip.Type = olOriginator
        'Try This
        .SentOnBehalfOfName = "John Doe"
        ' Add the CC recipient(s) to the message.
        Set objOutlookRecip = .Recipients.Add("John Doe")
        objOutlookRecip.Type = olCC
       ' Add the BCC recipient(s) to the message.
        Set objOutlookRecip = .Recipients.Add("John Doe")
        objOutlookRecip.Type = olBCC
        'Add Mary as a BCC if it is an invoice
        Set objOutlookRecip = .Recipients.Add("John Doe")
        objOutlookRecip.Type = olBCC
       ' Set the Subject, Body, and Importance of the message.
       .Subject = "Test"
       .Body = "Test"
       '.Importance = olImportanceHigh  'High importance
       ' Resolve each Recipient's name.
       For Each objOutlookRecip In .Recipients
       ' Should we display the message before sending?
        DisplayMsg = True
       If DisplayMsg Then
       End If
    End With
    Set objOutlook = Nothing
    End Sub

    Open in new window

    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now