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==================== |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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==================== |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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==================== |
Reply |
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 |