Solved

calling outlook macro from vbscript

Posted on 2013-05-30
17
3,357 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 69

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 69

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 69

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 69

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 69

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

733 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