Solved

VB code to Create Excel file from Outlook 2007 part 4

Posted on 2009-03-30
17
766 Views
Last Modified: 2012-05-06
I need this code to run base on the rule I created. I tried to find in the rules Wizard, the "run a script" option is empty.
What should I do?

Note:
This question is related to these question

VB code to Create Excel file from Outlook 2007 part 3.1 - http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_24279744.html#a24025641

VB code to Create Excel file from Outlook 2007 part 3 - http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_24237135.html#a24017516

VB code to Create Excel file from Outlook 2007 part 2 - http://www.experts-exchange.com/Programming/Misc/Q_24163884.html#a23699562
Option Explicit
 
Sub CreateExcel3()
    Dim objExcel As Object
    Dim objFSO As Object
    Dim objFolder As Object
    Dim sExcelFolderPath As String, sExcelFileName As String
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    sExcelFolderPath = "e:\temp\vba\"   'Folder path
    sExcelFileName = "test.xlsx"        'File name
    Set objExcel = CreateObject("excel.application")
    
    On Error Resume Next
    Set objFolder = objFSO.GetFolder(sExcelFolderPath) 'Set the folder path
    On Error GoTo 0
    If objFolder Is Nothing Then objFSO.createfolder (sExcelFolderPath) 'Create the folder if not present
 
    
    objExcel.Workbooks.Add
    objExcel.DisplayAlerts = False
    objExcel.ActiveWorkbook.SaveAs (sExcelFolderPath & sExcelFileName)
    objExcel.Quit
    objExcel.DisplayAlerts = True
    
    Set objExcel = Nothing
    Set objFSO = Nothing
    Set objFolder = Nothing
End Sub

Open in new window

0
Comment
Question by:0ffa
[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
  • 9
  • 7
17 Comments
 
LVL 28

Expert Comment

by:peakpeak
ID: 24025891
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24026077
The rule wizard will only display code with a mailitem or contactitem reference therefore:

Sub CreateExcel3(mai as mailitem)

Cannot see how to use it in your sub though ... since there does not seem to be a need for a mailitem.  However if the sub is to run for any receipt then that will do it .. once the header is changed the wizard should see the sub.

Chris
0
 

Author Comment

by:0ffa
ID: 24026970
Maybe I'm not clear enough in my description.

I'm going to set up a rule which will process incoming email base on the sender then the rule should execute that code.

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:0ffa
ID: 24027075
chris_bottomley,

I changed my line 3 to "Sub CreateExcel3(mail As MailItem)" and it showed in the script list as "CreateExcelProject.CreateExcel3". So I setup the rule base on this script.

However, when I tried this rule by receiving an email to trigger the rule there was an error saying 'The script "CreateExcelProject.CreateExcel3" doesn't exist or is invalid'.

What is wrong?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24027109
Not sure offhand but check if there is another sub of the same name and if not try moving the cursor to an email in an outlook folder then setting a breakpoint on the first line of your CreateExcel3 sub, >>>
Set objFSO = CreateObject("Scripting.FileSystemObject")

Now type in the immediate window:
CreateExcel3 application.ActiveExplorer.Selection.Item(1)

If it works stop the macro and let me know the settings on the rule as if it runs the rule declaration would seem to be at fault.

Chris
0
 

Author Comment

by:0ffa
ID: 24027432
chris_bottomley,

I have to clear the brackets in Sub CreateExcel3(mail As MailItem) first right? If not cleared, I couldn't ran the code coz it'll ask for which macro to run.

When I change it to Sub CreateExcel3(), here is the message I got:

Compile error:
Wrong number of arguments or invalid property assignment
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24027525
NO, don't change the sub, the whole idea is to test it as it would be called by the rule.  Re-instate the sub as Sub CreateExcel3(mail As MailItem) ... i'm not sure mail is a good choice for the varname though ... i usually use mai - No L.

Then press enter on the line in the immediate window as suggested~:
CreateExcel3 application.ActiveExplorer.Selection.Item(1)

Chris
0
 

Author Comment

by:0ffa
ID: 24036850
I did as directed and got a compile error: expected variable or procedure, not module.

Here is my rule:
Apply this rule after the message arrives
from offa
  and on this machine only
run CreateExcelProject.CreateExcel3

Then I tried to sent an email to myself to test whether the rule is working. When the email arrived the rule error I described above came out even though I put a breakpoint on the Set objFSO = CreateObject("Scripting.FileSystemObject"). Then I press enter on the immediate window.

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24037526
SUb CreateExcel3 does exist in a normal code module called "CreateExcelProject" in outlook?

Chris
0
 

Author Comment

by:0ffa
ID: 24038305
What do you mean by normal code?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24038332
CLutching at straws ... the sub could be in the outlooksession code module, a class module or a code module.

I haven't tested to be sure though I assume it must be in a normal code module to be visible to the riules wizard but I ask teh question as I may have missed another option so seeking conformation that it's in a suitable module.

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24038365
Can you supply your sub as modified as well looking for what might be the problem as the code should run ok as far as I can see.

Chris
0
 

Author Comment

by:0ffa
ID: 24047432
This CreateExcel3 is in the module under CreateExcelProject . I'll attach the print screen for you to see.
If I used Sub CreateExcel3() the code will run straightaway but won't show in rule wizard's script option.
If I changed it to Sub CreateExcel3(mai As MailItem) when I ran it'll ask me to choose which Macro to run.

CreateExcelProject.jpg
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 24047494
That's a first for me though I understand the logic.

Since the rule struggles as well as the immediate window try renaming your CreateExcel3 module as for instance Module_CreateExcel3.  This will keep the correspondence but remove the conflict ... I hope!

Chris
0
 

Author Closing Comment

by:0ffa
ID: 31564693
Chris,
I'm really happy when I tried the code again and it works like a charm. :)
0
 

Author Comment

by:0ffa
ID: 24120589
Chris,

Could you please explain more regarding the naming conflict?

Thanks again.
0
 

Author Comment

by:0ffa
ID: 24120859
Continue to "VB code to Create Excel file from Outlook 2007 part 5 - Getting email sender name and change the worksheet name"

http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_24314098.html
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Lost emails in Outlook 18 34
Modification to nested formula needed 2 24
Modify Array formula - Reduce Calculation Time 4 29
Exchange 2007 6 19
Find out what you should include to make the best professional email signature for your organization.
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 …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

762 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