Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Call Outlook procedure using VBScript

I have a procedure in Outlook that sends all the saved messages in Drafts folder.
Below is the code:

Public Sub SendMail()

Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olFolder As Outlook.MAPIFolder
Dim olDraft As Outlook.MAPIFolder
Dim strfoldername As String
Dim i As Integer

Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olFolder = olNS.GetDefaultFolder(olFolderInbox)

strfoldername = olFolder.Parent

Set olDraft = olNS.Folders(strfoldername).Folders("Drafts")

If olDraft.Items.Count <> 0 Then
    For i = olDraft.Items.Count To 1 Step -1
        olDraft.Items.Item(i).Send
    Next
End If

End Sub

Above code works fine.

Question:

I want to use Task Scheduler to fire this procedure as a specified time.
1. Where will I put the procedure in Outlook, Module or ThisOutlookSession?
2. I am not good in vbscript so I also don't know how to code it to call the Outlook Procedure. I've done calling Excel Procedure but Outlook doesn't support .Run property.

So this doesn't work:

Dim olApp

Set olApp = CreateObject("Outlook.Application")
olApp.Run "ProcedureName"

Set olApp = Nothing

I've also read about the Session.Logon like this:

Dim olApp

Set olApp = CreateObject("Outlook.Application")
olApp.Session.Logon
olApp.ProcedureName

Set olApp = Nothing

But it throws up error saying object ProcedureName is not supported.
Hope somebody can shed some light.

SOLUTION:

Ok, I've figured out 2 work around to Avoid or get pass this pop-up.

popup

1st one: is as KazJaw Pointed out.

Assuming you have another program (eg. Excel, VBScript) which includes sending of mail via Outlook in the procedure.
Instead of using .Send, just .Save the mail.
It will be saved in the Outlook's Draft folder.
Then using below code, send the draft which fires using Outlook Task Reminder.

Option Explicit
Private WithEvents my_reminder As Outlook.Reminders

Private Sub Application_Reminder(ByVal Item As Object)

Dim myitem As TaskItem

If Item.Class = olTask Then 'This works the same as the next line but i prefer it since it automatically provides you the different item classes.
'If TypeName(Item) = "TaskItem" Then
    Set my_reminder = Outlook.Reminders
    Set myitem = Item
    If myitem.Subject = "Send Draft" Then
        Call SendMail
    End If
End If

End Sub

Private Sub my_reminder_BeforeReminderShow(Cancel As Boolean)

Cancel = True
Set my_reminder = Nothing

End Sub

Above code fires when Task Reminder shows with a subject "Send Draft".
But, we don't want it showing since the whole point is just to call the SendMail procedure.
So we added a procedure that Cancels the display of reminder which is of olTask class or TaskItem Type.

This requires that Outlook is running of course.
You can keep it running 24 hours as i did or, create a VBscript that opens it to be scheduled via Task Scheduler.

2nd one: is to use API to programatically click on Allow button when the security pop-up appears.
Credits to SiddarthRout for the help.
Here is the LINK which will help you programmatically click on the Allow button.
Of course you have to tweak it a bit.

like image 643
L42 Avatar asked Jan 17 '14 07:01

L42


1 Answers

Tried & Tested!

Assuming that you have Outlook Application always running (according to comment below your question) you can do what you need in the following steps:

  1. add a new task in Outlook, set subject to: "run macro YourMacroName" and set time (plus cycles) when your macro should start.

  2. go to VBA Editor, open ThisOutlookSession module and add the following code inside (plus see the comments inside the code):

    Private Sub Application_Reminder(ByVal Item As Object)
    
    If TypeName(Item) = "TaskItem" Then
        Dim myItem As TaskItem
        Set myItem = Item
        If myItem.Subject = "run macro YourMacroName" Then
    
            Call YourMacroName    '...your macro name here
    
        End If
    End If
    End Sub
    
like image 168
Kazimierz Jawor Avatar answered Sep 20 '22 15:09

Kazimierz Jawor