Question Trigger Email Macro from Query

[froggiebeckie]

Hi all.
I'm working on a db for Preventative Maintenance on equipment, and would like to add a little pizzazz.

Other than the obvious tracking of PMs and problems, I would like to send the reminder notices (along with the necessary PM .doc) by using a macro to generate the emails. These PM documents are stored on a shared drive.

I have the query that returns all Required PMs that are due within the next 10 days

I know how to code the macro to get it to attach specific files and send the email, although I've always run it from a button.

Here are my questions:

1.Is there any way to automatically trigger the email macro from within the query?

2.If so, is there a way to tie the query results to which forms get attached to the email?
For Example, if the query runs and shows Machine A needs General PM done and Machine B needs Electrical PM done, can it specify the General PM form and the Electrical PM form both as attachments to the email?

Here's the code I'll use to attach a single form and send the email, if I have to do it one at a time.

Option Explicit
 
Public Sub SendMessage(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim str_Source_Path As String
Dim str_File_Name As String
 
' 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("Tom Thomson")
objOutlookRecip.Type = olTo
Set objOutlookRecip = .Recipients.Add("Sue Shadow")
objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Billy Boy")
objOutlookRecip.Type = olCC
 
' Set the Subject, Body, and Importance of the message.
.Subject = "These PMs are due"
.Body = vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance
 
'Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add("N:\MyStuff\SendFiles\Machine A General PM.doc")

 
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

Hope this is enought info without being too much.

Thanks for looking at this for me.

BeckieO

 

[froggiebeckie]

Thanks for your input, Pat.

I'm thinking I'm trying to make this thing wayyyyyy too complicated, or at least way above my skill level. (I'm prone to do that.)
I think I'll just push button a macro that runs the query, prints the results and opens the email.
Then I can manually attach the appropriate forms and send the email.

Again, thanks for taking the time to look and respond.



BeckieO