Code in one workbook to call code in another XL file

[phil.latio]

Hi,

Here's my scenario;

I have a workbook called Schedule.xls. It's purpose is to be opened on
a preferred schedule, which in turn triggers another XL file
('DailyDespatchRec.xls') to be opened & emailed as a result.
I need DailyDespatchRec.xls to be opened and automatically re-calculate
before it is sent out by email.

Within the Workbook (Schedule.xls) Open event, I have the following
code;

Dim wb As Workbook
Dim strPath As String

Dim strTo As String
Dim strCC As String
Dim strBodyTxt As String
Dim strSbj As String


strTo = "(e-mail address removed)"
strCC = "(e-mail address removed)"
strBodyTxt = "This text appears as the body of the email"
strSbj = "This text appears as the Subject line (dated " &
Format(Date, "dd/mm/yyyy")

strPath = "C:\DailyDespatchRec.xls"

Set wb = Workbooks.Open(strPath)
Application.Run "daily despatch
rec.xls!modUtility.EmailReport", strTo, strSbj, strBodyTxt, strCC,
strPath
strPath)
wb.Close True
End Sub

What it's supposed to do is run a Sub (named 'EmailReport') in another
Workbook (DailyDespatchRec.xls).
The Sub 'EmailReport' is in a standard module ('modUtility') - the code
is listed at bottom of post.

When I open Schedule.xls, I get an error 'Code execution interupted'.

When I click 'Continue' on the error msg box, I get the following
message;

'The macro 'daily despatch rec.xls!modUtility.EmailReport' cannot be
found'

When I click 'Debug' it highlights at the following line;

Application.Run "daily despatch rec.xls!modUtility.EmailReport", strTo,
strSbj, strBodyTxt, strCC, strPath
strPath)

Can anyone point me off as to how I can resolve this as I'm very close
to tears and at the very advanced stages of brain bruising!

Thanks in advance.

Phil


The EmailReport code - BTW I use this code very successfully from
within Access applications and know it works, unfortunately I cannot
accomplish my aims with Access and Excel is my vehicle.
'===================Code Start==================
Option Explicit
Public g_nspNameSpace As Outlook.NameSpace
Public g_olApp As Outlook.Application

Function InitializeOutlook() As Boolean
On Error GoTo Err_InitializeOutlook

Set g_olApp = New Outlook.Application
Set g_nspNameSpace = g_olApp.GetNamespace("MAPI")
InitializeOutlook = True
Exit_InitializeOutlook:
Exit Function

Err_InitializeOutlook:
Resume Exit_InitializeOutlook
End Function
'---------------------------------------------------------------------------------------
' Procedure : EmailReport
' Proc Type : Sub
' Date Time : 24/05/2004 16:52
' Purpose : To generate an email, add an attachment & send to the
recipient(s)
' Inputs : strTo = recipient string, strSubj = subject string,
strBody = Message string
' varCC =optional CC string, varAttch = optional filepath
string (to an attachment)
' Outputs : Email message with an attachement (if specified)
' Usage : Email reports
'---------------------------------------------------------------------------------------
Public Sub EmailReport(strTo As String, _
strSubj As String, _
strBody As String, _
Optional varCC As Variant, _
Optional varAttch As Variant)
On Error GoTo Err_EmailReport

Dim objMailItem As Outlook.MailItem

'Test initialisation of Outlook
If g_olApp Is Nothing Then
If InitializeOutlook = False Then
GoTo Err_EmailReport
Exit Sub
End If
End If

Set objMailItem = g_olApp.CreateItem(olMailItem)

With objMailItem
.To = strTo
'Test to see if a 'CC' string was supplied
If Not IsMissing(varCC) Then
.cc = varCC
End If
.Subject = strSubj
.Body = strBody
'Test to see if an Attachment string was
supplied
If Not IsMissing(varAttch) Then
.Attachments.Add varAttch
End If
.Send
End With

'Clean up on the way out!!
Exit_EmailReport:
Set objMailItem = Nothing
Exit Sub

Err_EmailReport:
Resume Exit_EmailReport
End Sub
'===================Code End====================

 

[Guest]

Phil,

I've had the same issue while trying to automate the updating of several
files. The syntax to the Application.Run command is where I (and I think you)
ran into trouble. Try out the following line in your code instead of the
current Run command:

Application.Run "'" & strPath & "'" & "!EmailReport", strTo, strSbj,
strBodyTxt, strCC, strPath

After the .Run, that is a double quote, single quote, double quote. I think
all that you need is the file name, the procedure name, and then the
parameters; the module name doesn't seem to be necessary.

-Cory

 

[phil.latio]

Hi Cory,

Firstly, apologies for the delay in responding - I view via Google
Groups at work!

Many, many thanks for pointing me off in the right direction, the
suggestion worked and it does exactly as it should!
To be truthful, I would never of considered the format suggested, so
you have prevented me from bashing my head to oblivion!

Once again a sincere thanks, it is very much appreciated!

Best regards,

Phil