Sending an Email from MS Access No third party dll allowed

Question

I need to send a series of email notifications from an MS Access Database.

• No third party dll's like Redemption
• Cannot trip the outlook security warnings
• The email will have a pdf attachment

I know to do this I need to use MAPI, but I can't seem to find a way to do this with VBA.

Any help would be appreciated

Thanks,

Scott

Answer 1

If the user has outlook installed:

Dim strErrMsg As String 'For Error Handling
Dim olApp As New Outlook.Application
Dim olNameSpace As Outlook.NameSpace
Dim olMail As Outlook.MailItem
Dim oleGrf As Object
Dim strFileName As String

Set olNameSpace = olApp.GetNamespace("MAPI")
Set olMail = olApp.CreateItem(olMailItem)
Set oleGrf = Me.OLEchart.Object
strFileName = "c:\temp\Graph.jpg"
oleGrf.Export FileName:=strFileName

With olMail
    .To = "[email protected]"
    .Subject = "Graph Info " & Format(Now(), "dd mmm yyyy  hh:mm")
    .Attachments.Add strFileName
    .ReadReceiptRequested = False
    .Send
End With
Kill strFileName

Also check out Tony Toews's Microsoft Access Email FAQ

Answer 2

If you can live with requiring CDO to be present on the machine, and you don't mind a user-provided SMTP server, you can use that. Just google for some example code, but for you convenience I'll paste some below from www.rondebruin.nl :

Sub CDO_Mail_Small_Text()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
'    Dim Flds As Variant

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

'    iConf.Load -1    ' CDO Source Defaults
'    Set Flds = iConf.Fields
'    With Flds
'        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
'                       = "Fill in your SMTP server here"
'        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
'        .Update
'    End With

strbody = "Hi there" & vbNewLine & vbNewLine & _
          "This is line 1" & vbNewLine & _
          "This is line 2" & vbNewLine & _
          "This is line 3" & vbNewLine & _
          "This is line 4"

With iMsg
    Set .Configuration = iConf
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .From = """Ron"" <[email protected]>"
    .Subject = "Important message"
    .TextBody = strbody
    .Send
End With

End Sub

Adding an attachment would be done using .AddAttachment "C:\files\filename.pdf" on the iMsg.

Answer 3

See the page Microsoft Access Email FAQ - Directly via the Winsock I haven't tried those myself but you should be able to adapt the VB6 code to send the emails directly.

Answer 4

DoCmd.SendObject , , , "[email protected]", , , "This is subject", "This is msg"

That's it !