Run query from open and send email

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to run a query whenever the DB is opened. The query is named
"RemQry". It returns records where the days transpired is greater than 2. It
contains the owners of the records and the email addresses. Once "RemQry"
runs I would like to programatically send an email for each record to the
corresponding email in the record. I have a module to send an email but it is
not connected to the query in any way.

Option Explicit


Sub SendMessage(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("(e-mail address removed)")
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("(e-mail address removed)")
objOutlookRecip.Type = olCC

' Set the Subject, Body, and Importance of the message.
.Subject = "CRC Escalated Case Reminder"
.Body = "It has been at least 2 days since this case has been
escalated. There is no record that a call back has been made. If this is an
error please notify the sender to update the database accordingly. If a call
back is still outstanding please place the call as soon as possible and
notify the sender to update the database. Thank You. "
.Importance = olImportanceHigh 'High importance

' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
 
I would like to run a query whenever the DB is opened.
Click to expand...

Then call it from an AutoExec macro: see help. If you need to call VBA
code, you'll need the RunCode action.
Once "RemQry" runs I would like to programatically send an
email for each record to the corresponding email in the record. I have
a module to send an email but it is not connected to the query in any
way.
Click to expand...

' get the records into recordset
set rs = querydefs("remqry").openrecordset( _
dbopensnapshot, dbforwardonly)

' don't do anything if no records returned
do while not rs.eof

' call your message sending subroutine
SendMessageTo rs!subjectemailaddress

' move to the next record. The loop will exit
' when there are no records left
rs.movenext

' go round again for the next one
loop

' tidy up
rs.close


Hope that helps


Tim F
 
Back
Top