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

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

Thanks
kosenrufuAsked:
Who is Participating?
 
Nick67Connect With a Mentor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  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
 
danishaniCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.