email a report automatically

[grapevine]

I have set up a task scheduler to automatically run a macro to call a VBA script to email a report.
I have set up the task scheduler as follows

Action - Start a program
Program/Script - "C:\Users\User\Desktop\CIS Database.mdb"
Argurments -/x ReviewDate

This appears to be working correctly as it does open the database

I have then set up a macro and dont know how to link the macro to the VBA
Macro as the macro keeps saying it does not recognised lupin so it is not seeing the email address

Action  - RunCode
Function Name = ReviewDate ([EMAIL="lupin@uwclub.net"]lupin@uwclub.net[/EMAIL], Overdue or DueReview Dates, Please find attached Review Report)

The VBA code which I found online I have placed in module 1 and is as below. I do not know whether I have adapted correctly and maybe this is why the macro cannot see the arguments.

public Function ReviewDate( _
    MessageTo As String, _
    Subject As String, _
    MessageBody As String)
    ' Define app variable and get Outlook using the "New" keyword
    Dim olApp As New Outlook.Application
    Dim olMailItem As Outlook.MailItem  ' An Outlook Mail item
 
    ' Create a new email object
    Set olMailItem = olApp.CreateItem(olMailItem)
    ' Add the To/Subject/Body to the message and display the message
    With olMailItem
        .To = MessageTo
        .Subject = Subject
        .Body = MessageBody
        .Send       ' Send the message immediately
    End With
    ' Release all object variables
    Set olMailItem = Nothing
    Set olApp = Nothing
End Function

I have found the following code to attach a report, but I dont know whether I have adapted correctly or where to place it

DoCmd.SendObject acSendReport, "RptTrainingDueReviewDate", acFormatXLS

I am very familiar with VBA in Excel but this is new territory for me with Access. I would be grateful is someone would highlight where I have gone wrong.

 

[pbaldy]

Your function arguments are strings, so need to be in quotes. Try:

ReviewDate ("lupin@uwclub.net", "Overdue or DueReview Dates", "Please find attached Review Report")

 

[grapevine]

Thank you for the information about the quotes for the strings, the macro is now passing information to the VBA code BUT the code that I have is now giving a compile error.
User-defined type not defined for the line

Dim olApp As New Outlook.Application

The full code which I placed in module 1 is as follows

Public Function ReviewDate( _
    MessageTo As String, _
    Subject As String, _
    MessageBody As String)
    ' Define app variable and get Outlook using the "New" keyword
    Dim olApp As New Outlook.Application
    Dim olMailItem As Outlook.MailItem  ' An Outlook Mail item
 
    ' Create a new email object
    Set olMailItem = olApp.CreateItem(olMailItem)
    ' Add the To/Subject/Body to the message and display the message
    With olMailItem
        .To = MessageTo
        .Subject = Subject
        .Body = MessageBody
        .Send       ' Send the message immediately
    End With
    ' Release all object variables
    Set olMailItem = Nothing
    Set olApp = Nothing
End Function

Many thanks, you help is really appreciated

 

[grapevine]

Now that you put me in the right direction for getting the email to work I could experiment a bit further. I have since found the following code that sends the email.

Public Function ReviewDate()
DoCmd.SendObject acSendReport, "RptTrainingDueReviewDate", acFormatPDF, "abc@emailaddress", , , "Outstanding Review Dates", "Please find list of outstanding or due review dates for various training courses", False
End Function

I would like the report to only be send if records actually exist in the report and have tried the following

Public Function ReviewDate()
Dim TDRD As AccessObject
TDRD = RptTrainingDueReviewDate
If TDRD <> "" Then
DoCmd.SendObject acSendReport, "RptTrainingDueReviewDate", acFormatPDF, "abc@emailaddress", , , "Outstanding Review Dates", "Please find list of outstanding or due review dates for various training courses", False
End If
End Function

But i am getting an error Runtime error 91 - Object variable or With block variable not set.
Any guidance or advice gratefully received

 

[grapevine]

"problem solved"
I have now finally managed to get the code to work - so for anyone who may have a similar problem in the future. This was my final solution

Public Function ReviewDate()
Dim TR As String
Dim criteria As String
Dim trnum As Integer
trnum = DCount("[Date for Review]", "qryTrainingDueReviewDate")
If trnum > 0 Then
DoCmd.SendObject acSendReport, "RptTrainingDueReviewDate", acFormatPDF, "[EMAIL="abc@emailaddress"]abc@emailaddress[/EMAIL]", , , "Outstanding Review Dates", "Please find list of outstanding or due review dates for various training courses", False
Else
DoCmd.SendObject , , , "[EMAIL="abc@emailaddress"]abc@emailaddress[/EMAIL]", , , "Review Dates", "There are no training records needing reviewing", False
End If
End Function

 

[pbaldy]

And all while I slept!

FYI, your first code required a reference to Outlook in Tools/References.

 

[grapevine]

Am I correct in thinking that if I revert to the first code then this will bypass the Outlook Security issue. I have posted another question about this and the reply mentions CDO. If yes, I will have another go at my first macro, if not then I will try the CDO route (haven't got a clue what that means at the moment though!)
If this does not solve the security issue, can you explain the advantages/disadvantages between the first code and the second code I used.
Thank you for your help

 

[pbaldy]

The first code would not bypass the Outlook warnings, because they're coming from Outlook, not Access. I use CDO when I want to bypass the warnings.

The first code gives you more control than the second, but the second is obviously simpler and easier to use. The first lets you attach files other than the report being sent in the second, and you can attach multiple files.