LinkBack URL
About LinkBacks
Guest 
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 = "me@somehwere.co.uk"
strCC = "me2@somehwere.co.uk"
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====================
Register To Reply
Bookmarks