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).
This is in Access 2002 (but I may be getting Access 2010 soon).
Response by poster: Oh, and I'm using Lotus Notes. :/
posted by desjardins at 2:12 PM on December 10, 2010
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
posted by soelo at 3:04 PM on December 10, 2010
Response by poster: 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
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
posted by amethysts at 5:36 PM on December 10, 2010
Response by poster: 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:
posted by desjardins at 9:45 AM on December 13, 2010
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
Response by poster: 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)
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
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
Response by poster: 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:
posted by desjardins at 12:10 PM on December 20, 2010
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
This thread is closed to new comments.
posted by okbye at 2:12 PM on December 10, 2010