Programmatically sending email from Access

[scariharry]

Well with Sue's help I found a solution to the security dialogs that pop up
when I tried to send email out of Access to Outlook.


Two prong approach using both Access and Outlook in VBA

In Access:

Option Compare Database
Option Explicit
Public Sub SendMessage1()

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookAtt As Outlook.Attachment
Dim strBody As String

Set objOutlook = New Outlook.Application 'Create the Outlook session
Set objOutlookMsg = objOutlook.CreateItem(olMailItem) 'Create message

With objOutlookMsg
.To = "Dist List" 'Send To:
.CC = "" 'CC:
.Subject = "This is a Test..... this is only a test -+agmm" 'Subject:
'Msgtxt: Body is set below attachment line....
strBody = "Like I said, it's just a test."
strBody = strBody & vbCrLf & "Still testing"
strBody = strBody & vbCrLf & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'Importance
'Attachments
Set objOutlookAtt = .Attachments.Add("[path]")
strBody = strBody & vbCrLf & "That is an attachment" & vbCrLf & vbCrLf
.Body = strBody
.Save
End With
End Sub


In Outlook:
Public WithEvents sndDraft As Outlook.Items


Public Sub Application_Startup()

Set sndDraft = Outlook.Session.GetDefaultFolder(olFolderDrafts).Items

End Sub


Private Sub sndDraft_ItemAdd(ByVal Item As Object)

Dim objSubT As String
objSubT = Right(Item.Subject, 6)

If TypeName(Item) = "MailItem" Then
If objSubT = "-+agmm" Then

Dim objSub As String
Dim objSubF As String
Dim subLen As Integer
objSub = Item.Subject
subLen = Len(objSub)
objSubF = Left(objSub, (subLen - 6))

Item.Subject = objSubF
Item.Send

End If
End If

End Sub

This works with not a single security blurb.....

Thanks Sue