[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

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

Thanks
0
Comment
Question by:kosenrufu
4 Comments
 
LVL 58
ID: 35506389

  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.

JimD.
0
 
LVL 12

Expert Comment

by:danishani
ID: 35506589
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 35509291
This code works to format up and create email messages through Outlook from within Access.
You need a reference to Outlook set

***BUT***

OlOriginator (ie the FROM address can't be specified anymore.)
See here
http://kbalertz.com/168022/Field-Using-VBScript.aspx

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
    Err.Clear
    WasOpen = False
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    
Else
    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
    Folder.Display
    'done opening
End If

AppActivate "Microsoft Outlook"
    'For x = 1 To objOutlook.Explorers.count
        'objOutlookExplorers.Item(x).WindowState = olMaximized
        'objOutlookExplorers.Item(x).Activate
   ' 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
       objOutlookRecip.Resolve
   Next

   ' Should we display the message before sending?
    DisplayMsg = True
   If DisplayMsg Then
       .Display
   Else
       .Save
       .Send
   End If
End With

Set objOutlook = Nothing

End Sub

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35580915
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

867 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