Solved

calling outlook macro from vbscript

Posted on 2013-05-30
17
3,087 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
  • 8
  • 5
  • 4
17 Comments
 
LVL 68

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 68

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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 68

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 68

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 68

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now