Code in one workbook to call code in another XL file
 

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 = "
strCC = "
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====================

Code in one workbook to call code in another XL file
 

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

" wrote:

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 = "
strCC = "
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====================

Code in one workbook to call code in another XL file
 

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

Cory wrote:
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

" wrote:

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 = "
strCC = "
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====================