Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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==================== |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you call VBScript code from VBA? | Excel Programming | |||
Import VBA Code in Excel-File ? (Export VBA Code to file) | Excel Programming | |||
Compiling Excel VBA code increases workbook file size! | Excel Programming | |||
VBA code to call the name of current file | Excel Programming | |||
Insert VBA code with a macro in a .xls file by workbook open event | Excel Programming |