Can this be done and how?

[echo0001]

Hello all,

I have a query with the following code....

SELECT NRTS.[Item], NRTS.[Description], NRTS.[NRTS No], NRTS.[Serial Number], NRTS.[Cal Due], NRTS.[Allocated To], NRTS.[Image]
FROM NRTS
WHERE [Cal Due]=DateAdd("D",30,date());

What I need is when the query returns a record from the "WHERE" Clause (because it wont most of the time) it runs a marco or somehow activates another part of the Db, basically I have items in need of calibration at different points in the year and this query returns records with their calibration date 30 days from Systems date. now when it return a record i need to send an email automatically since the Db will be running on a sever.

What I dont want is for an email to be sent of a blank record.

Thank you for your time.

 

[khawar]

You can use dcount to check whether there are some records in query or not

 

[echo0001]

OK im not an expert with acces or VBs script could you give me some help on writing the code.

 

[khawar]

if Dcount("Item","Your_Query_Name")>0 then
      [COLOR="Red"]Write code to send mail here[/COLOR]
else
End if

 

[echo0001]

OK thanks i will try and let you know how it goes, thanks

 

[echo0001]

ok now this is the code i want to use to send the email but can someone show me how i get it to send a report as a rich text file (or any other viewable format) in this code.

Sub sbSendMessage(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
On Error GoTo ErrorMsgs
' 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. Substitute
' your names here.
Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
objOutlookRecip.Type = olCC
' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "Last test." & vbCrLf & vbCrLf
.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
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display

End If
Next
.Send
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing

End If
End Sub



Thank you

 

[khawar]

Use this code for mailing in snapshot format you can also use rtf, excel or Html format

DoCmd.SendObject acReport, "[B]Your_Report_Name_Here[/B]", "SnapshotFormat(*.snp)", "", "", "", "[B]Subject Of Mail Here[/B]", "Body of Mail Here", True, ""

 

[echo0001]

cant use sendobject as that requires a person to click allow access to send email. when the code i want to use gets round that.

 

[khawar]

Than what do you want

 

[khawar]

You can first save report in rtf format save it at some specific location and then attach file from there

 

[echo0001]

basically when the query returns the record an email is automatically sent out as a report of the record. so i have to find a way where no user interaction would send this email.

 

[MSAccessRookie]

I found this when I was searching the AWF forums. It sounds like the person posting at the time had a similar situation to the one that youi have.

http://www.access-programmers.co.uk/forums/showthread.php?t=141517&highlight=e-mail

 

[echo0001]

Thats a bit difficult for me i dont know where to put what and linking them together is hard for my head.lol