Automatically generating an email from a query in Access
December 10, 2010 1:44 PM   Subscribe

I have a database in Access that is essentially a list of projects and associated tasks. I have a query that lists all projects due in the next 30 days. How can I automatically generate an email from this query? Ideally this would just magically send an email whenever the query is populated with a new project.

This is in Access 2002 (but I may be getting Access 2010 soon).
posted by desjardins to Computers & Internet (8 answers total) 1 user marked this as a favorite
 
Look at .SendObject in VBA Help. It'll take a little bit of coding, but if you put the query in a form's record source you could invoke the email when a row is inserted into the form. This assumes that you don't mind writing a little code, I can't think of way to do it without some VBA.
posted by okbye at 2:12 PM on December 10, 2010


Oh, and I'm using Lotus Notes. :/
posted by desjardins at 2:12 PM on December 10, 2010


There is a SendObject Action in Macros that may be of use to you. You can have it send any Object, so instead of running a select query, turn it into a Make Table query and write a macro that first runs the query and then sends the table in email. Sorry, I don't know how well it will work with Lotus Notes, but it worked fine for us using Outlook.
posted by soelo at 3:04 PM on December 10, 2010


Thanks, I found the SendObject action, but I don't know how to make it automagically send the email. It seems like there still has to be some user input, like ... running the macro. Is there some code like "sendobject IF updated ELSE do nothing"?
posted by desjardins at 4:36 PM on December 10, 2010


Go the form you're using to do the update, go to design view. Select the last field that someone would use to enter the project. Go to properties, then go to event. This is when you specify when you want your macros to happen. Put in your macro next to "After update". In the macro itself, make sure the first item is "Save Record", which is under "Run Command".
posted by amethysts at 5:36 PM on December 10, 2010


Holy crap, I actually mostly figured this out. For posterity:

I have a form that opens on the database startup. I also have a module with the code below. On form_load, I call the function in the module. I haven't figured out the whole file attachment part, but I'm pretty damned amazed I got this far. My next mission is to figure out how to schedule the task of opening the database.

In the form, I have this:
Private Sub Form_Load()
Call SendNotesMail("Test Subject Line", "", "Recipient Name", "Test Body Text", True)

End Sub
And in the module I have this, which I got from here:
'Public Sub SendNotesMail(Subject as string, attachment as string,
'recipient as string, bodytext as string,saveit as Boolean)
'This public sub will send a mail and attachment if neccessary to the
'recipient including the body text.
'Requires that notes client is installed on the system.
Public Sub SendNotesMail(Subject As String, attachment As String, Recipient As String, BodyText As String, SaveIt As Boolean)
'Set up the objects required for Automation into lotus notes
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
'Next line only works with 5.x and above. Replace password with your password
' Session.Initialize
'Get the sessions username and then calculate the mail file name
'You may or may not need this as for MailDBname with some systems you
'can pass an empty string or using above password you can use other mailboxes.
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
'Open the mail database in notes
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.ISOPEN = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
'Set up the new mail document
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = "exampleaddress@gmail.com"
MailDoc.Subject = "Test Subject"
MailDoc.Body = "Test Body Text"
MailDoc.SAVEMESSAGEONSEND = SaveIt
'Set up the embedded object and attachment and attach it
'If attachment <> "" Then
'Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
'Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", attachment, "Attachment")
'MailDoc.CREATERICHTEXTITEM ("Attachment")
'End If
'Send the document
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.SEND 0, Recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End Sub
If you have Outlook it's pretty easy to find code to send mail from that.
posted by desjardins at 9:45 AM on December 13, 2010


To have the email sent automatically without any user input, I set up a scheduled task in windows to run a vb script every day. (Note: the macro runs the function code above)

Const DBPath = "C:\example.mdb"
Const MacroName = "SendReport"

Set AccessApp = GetObject(DBPath, "Access.Application")
AccessApp.visible = False
AccessApp.DoCmd.RunMacro MacroName
AccessApp.Quit


Code was found here. I hope this saves someone else some time, because this has eaten my Friday.
posted by desjardins at 12:23 PM on December 17, 2010


I mashed together some of the above code with this to send the report as text in the body of the email. It (the email) is not pretty, but it works:

Public Function olSendRpt(strTo As String, strBody As String, strSubject As String, strReportName As String)
'A procedure to send report in a body of mail message
'Alex Dybenko, http://Alex.Dybenko.com

'Usage: olSendRpt "Send@To.com", "Pls see report below", "My Report", "Report1"


Dim strFileName As String, intFile As Integer, strLine As String, strTemplate As String
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim Session As Object 'The notes session
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
'You may or may not need this as for MailDBname with some systems you
'can pass an empty string
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.ISOPEN = True Then
'Already open for mail
Else
Maildb.OPENMAIL
'Lotus Notes will now request your password
'Not needed if Notes already open
End If


strFileName = Environ("Temp") & "\rep_temp.txt"

If Len(Dir(strFileName)) > 0 Then
Kill strFileName
End If
DoCmd.OutputTo acOutputReport, strReportName, acFormatTXT, strFileName

intFile = FreeFile
Open strFileName For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strLine
strTemplate = strTemplate & vbCrLf & strLine
Loop
Close #intFile
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = strTo
MailDoc.Subject = strSubject
MailDoc.Body = strTemplate
MailDoc.SAVEMESSAGEONSEND = SaveIt
'Send the document
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.SEND 0, Recipient
'DoCmd.SendObject acSendNoObject, "", acFormatTXT, strTo, , , strSubject, strBody & strTemplate
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End Function

posted by desjardins at 12:10 PM on December 20, 2010


« Older I can see half your aura.   |   Best Digital Photo Frame? Help me find a suitable... Newer »
This thread is closed to new comments.