?
Solved

calling outlook macro from vbscript

Posted on 2013-05-30
17
Medium Priority
?
3,644 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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 1500 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 1500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

801 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