Solved

calling outlook macro from vbscript

Posted on 2013-05-30
17
3,501 Views
Last Modified: 2013-05-31
I am attempting to use a vbscript to send an email in my drafts folder (outlook 2010).  The outlook macro works fine (sends the message) when I run it in outlook.  However, when I attempt to call it via vbscript I get this message:  "object does not support this property or method 'app.senddrafts'

Here is the vbscript:

Set app = CreateObject("Outlook.application")
call app.SendDrafts

I'm new to vbscript with outlook, any help is appreciated
0
Comment
Question by:JohnGlick
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
17 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 39208567
Try if
   app.Run("SendDrafts")
works for you.
0
 

Author Comment

by:JohnGlick
ID: 39208589
it still gives me an error,
---------------------------
Windows Script Host
---------------------------

Error:      Object doesn't support this property or method: 'app.run'
Code:      800A01B6
Source:       Microsoft VBScript runtime error
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39208743
I forgot that Outlook is different ... I'm afraid that it is not possible to execute a macro in Outlook from VBS (or any other language). I cannot see how you should be able to access anything allowing to get to macros.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:JohnGlick
ID: 39208988
What I really need to do is email an excel sheet unattended.  If I use the .send method in excel I get the security prompt.  I tried MAPI Labs advanced security plug in but that causes outlook to slow down when adding attachements.  
Any other solutions?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39209113
Hi, JohnGlick.

If you can share the Outlook macro you want to run, then I can probably convert it to run outside of Outlook in VBScript.
0
 

Author Comment

by:JohnGlick
ID: 39209121
here's what I have:

Public Sub SendDrafts()

Dim lDraftItem As Long
Dim myOutlook As Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim myFolders As Outlook.Folders
Dim myDraftsFolder As Outlook.MAPIFolder

Set myOutlook = Outlook.Application
Set myNameSpace = myOutlook.GetNamespace("MAPI")
Set myFolders = myNameSpace.Folders

Set myDraftsFolder = myFolders("John Glick").Folders("Drafts")

For lDraftItem = myDraftsFolder.Items.Count To 1 Step -1

If myDraftsFolder.Items.Item(lDraftItem).Subject Like "Punch Pair Detail Report" Then

myDraftsFolder.Items.Item(lDraftItem).Send

End If
Next lDraftItem

Set myDraftsFolder = Nothing
Set myNameSpace = Nothing
Set myOutlook = Nothing

End Sub
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39209354
Try this.

Dim lDraftItem, myOutlook, myNameSpace, myFolders, myDraftsFolder

Set myOutlook = CreateObject("Outlook.Application")
Set myNameSpace = myOutlook.GetNamespace("MAPI")
myNameSpace.Logon "Outlook"
Set myFolders = myNameSpace.Folders

Set myDraftsFolder = myFolders("John Glick").Folders("Drafts")

For lDraftItem = myDraftsFolder.Items.Count To 1 Step -1
    If myDraftsFolder.Items.Item(lDraftItem).Subject Like "Punch Pair Detail Report" Then
        myDraftsFolder.Items.Item(lDraftItem).Send
    End If
Next lDraftItem

Set myDraftsFolder = Nothing
Set myNameSpace = Nothing
Set myOutlook = Nothing
 

Open in new window

0
 

Author Comment

by:JohnGlick
ID: 39210644
This code gives me the following error:

---------------------------
Windows Script Host
---------------------------
Script:      \\dcserver\Secretary Docs\EMPLOYEE DOC\Overtime  Calculators\Send Punches.vbs
Line:      15
Char:      6
Error:      Expected end of statement
Code:      800A0401
Source:       Microsoft VBScript compilation error
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39210668
Replace line 14 by Next (no loop var).
0
 

Author Comment

by:JohnGlick
ID: 39210763
that returns sub or function not defined,
if I make line 1 "sub sendmail()"
and last line "end sub"

I don't get any errors but it does not send my email.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39210789
If you add that as a function, you'll have to call the function you defined. But there is no need to do that - the error message does not provide a line number?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39210797
Probably it is line 8, and we need to use Item:
Set myDraftsFolder = myFolders.Item("John Glick").Folders.Item("Drafts")

Open in new window

0
 

Author Comment

by:JohnGlick
ID: 39210803
---------------------------
Windows Script Host
---------------------------

Line:      12
Char:      5
Error:      Sub or Function not defined
Code:      800A0023
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 39210813
Try this version.

Dim lDraftItem, myOutlook, myNameSpace, myFolders, myDraftsFolder

Set myOutlook = CreateObject("Outlook.Application")
Set myNameSpace = myOutlook.GetNamespace("MAPI")
myNameSpace.Logon "Outlook"
Set myFolders = myNameSpace.Folders

Set myDraftsFolder = myFolders("John Glick").Folders("Drafts")

For lDraftItem = myDraftsFolder.Items.Count To 1 Step -1
    If InStr(1,myDraftsFolder.Items.Item(lDraftItem).Subject,"Punch Pair Detail Report") > 0 Then
        myDraftsFolder.Items.Item(lDraftItem).Send
    End If
Next

Set myDraftsFolder = Nothing
Set myNameSpace = Nothing
Set myOutlook = Nothing

Open in new window

0
 

Author Comment

by:JohnGlick
ID: 39210817
changing the code to:
Set myDraftsFolder = myFolders.Item("John Glick").Folders.Item("Drafts")

returns same error as above.
0
 

Author Comment

by:JohnGlick
ID: 39210850
bluedevilfan,
your latest version works, however I still am getting the annoying security prompt.  So I guess I'm back to square one.  I didn't think about it that using vbs in this way would essentially be doing the same thing that I was doing in my excel workbook.  

any possible solutions to get around the security prompt?
security-prompt.png
0
 
LVL 76

Assisted Solution

by:David Lee
David Lee earned 500 total points
ID: 39210892
If you have an anti-virus product installed and it's up to date, then you shouldn't get a security prompt from Outlook.  Click File > Options > Trust Center > Trust Center Settings > Programmatic Access.  The setting should be "Warn me about ...".  Also check the "Antivirus status" setting.  Hopefully it will say "Valid".
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question