Running an Excel macro via Outlook reminder

[Excel2006]

Hi

How do you send e-mail using a reminder in Outlook. Please see below, I want to send an e-mail message with an attachment

Sub Application_Reminder(ByVal Item As Object)

Select Case Item.Class
Case olAppointment
If Item.Subject = "Test" Then

'Set EApp = CreateObject("excel.Application")
'With EApp
'       Change file name to suit
'    .Visible = True
'    End With

EmailTest ' An excel macro **
End If
End Select


End Sub

 

[DonkeyOte]

Your thread title and message don't really tie out -- when a reminder pops up you want to either:

a) [based on subject of this thread] run an Excel macro
b) [based on above message]: send an email with an attachment

the below would be an example of the latter:

Private Sub Application_Reminder(ByVal Item As Object)
Dim oMI As MailItem
Select Case Item.Class
    Case olAppointment
        If UCase(Item.Subject) = "TEST" Then
            Set oMI = Application.CreateItem(olMailItem)
            With oMI
                .To = "thebigcheese@zyx.com"
                .Subject = "overdue"
                .Attachments.Add "C:\FolderX\SubFolderY\FileName.xls"
                .Send
            End With
            Set oMI = Nothing
        End If
End Select
End Sub

 

[Excel2006]

Thx. What I want to do, is based on the Outlook reminder message e.g. "test"
I want Outlook to run an excel macro ( EmailTest ) which will send a message and attachemnt to various users.
The excel macro contains code to send e-mail ( see below
Hope that makes sense.

excel macro code

Dim olApp As Outlook.Application

Dim fdrContacts As Outlook.MAPIFolder
'Set fdrContacts = Application.GetNamespace("MAPI") _
'    .GetDefaultFolder(olFolderContacts)

Set olApp = CreateObject("Outlook.Application")
Dim olMail As MailItem
Set olMail = olApp.CreateItem(olMailItem)

Application.ScreenUpdating = False

'Msgbox "XL file will be sent to jon"

With olMail

.To = "jon@mail.com"

.Attachments.Add "c:\test" & ".xls"
.Send
End With

 

[DonkeyOte]

I can only presume you're doing things other than just emailing via your XL macro else it makes most sense to simply action out of OL directly in the first instance (per the prior note)... to invoke an XL macro from within OL you could use something along the lines of:

Dim xl As Excel.Application
Set xl = New Excel.Application
xl.Run "'C:\Folder\SubFolder\Filename.xls'!MacroName"
xl.Quit
Set xl = Nothing

You must be sure though that the machine on which this routine is invoked has requisite macro security levels in place -- if running on Very High / High Security the above would fail, on Medium Security end-user will be prompted to enable macros when the code tries to execute the xl command, on Low it should run automatically though this is not the recommended security setting.

 

[RoryA]

Doesn't make a lot of sense (to me) to use Outlook to automate Excel to run a macro that automates Outlook. Why not cut out the middleman?

 

[Excel2006]

Donkey, you were correct, i do need to run a macro in excel before invoking Outlook. Thx everyone for your help.