PDA

View Full Version : Send as Attachment - Change coding from Excel 2003 to Excel 97



feathers212
04-16-2007, 12:25 PM
I ened up writing all of my coding using Excel 2003. Everything was working perfectly. Then I tried using my program on a user computer. PROBLEM! The end-user computers only have Excel 97. Now I'm stuck.....I'm trying to send my active workbook as an Outlook e-mail attachment (to multiple recipients):
'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = "Scheduling; Pressroom Manager; Pressroom Engineer"
.Subject = "Pressroom Pulled Job Ticket"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With

Also, I have this code used during another step in the process (haven't tested it on the end-user Ecel 97 computers yet):
'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = "Supervisor1; Supervisor2; Supervisor3"
'.CC = ""
.Subject = "Verify Pressroom Pulled Job Counts"
.HTMLBody = "A pull ticket has been submitted. Please check the <a href=""V:\press\Forum\Count Control\Pull Tickets\Verification Needed\"">Verification Needed</a> folder."
.Importance = 2
.Display
End With

I'm SO close to getting this project completed....just need to get it "97 friendly"

Bob Phillips
04-16-2007, 12:34 PM
Have you set a reference to Outlook in the project?

feathers212
04-16-2007, 12:45 PM
This is what I have for the email with a hyperlink:
Private Sub Submit_Ticket_Click()
Dim oApp As Object
Dim oMail As Object

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = "Supervisor1; Supervisor2; Supervisor3"
'.CC = ""
.Subject = "Verify Pressroom Pulled Job Counts"
.HTMLBody = "A pull ticket has been submitted. Please check the <a href=""V:\press\Forum\Count Control\Pull Tickets\Verification Needed\"">Verification Needed</a> folder."
.Importance = 2
.Display
End With

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing

End Sub

And this is what I have for the attachment email:
Private Sub OK_Button_Click()
Dim oApp As Object
Dim oMail As Object
'Create and show the outlook mail item

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = "Scheduling; Pressroom Manager; Pressroom Engineer"
.Subject = "Pressroom Pulled Job Ticket"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing


It crashed when I reached
Set oApp = CreateObject("Outlook.Application")

Ken Puls
04-16-2007, 10:18 PM
It crashed when I reached

Fair enough, but would you care to share the exact error message you get? And have you checked that you have a reference set to Outlook as xld asked?

feathers212
04-17-2007, 05:55 AM
I'm sorry for not posting the error. I was only just able to get back on the Excel 97 computer this morning. I am getting "Run-time error '429': ActiveX component can't create object".

Also, I'm not sure what you mean by setting a reference to Outlook. Is this something specific to my workbook or is it Excel in general?

So frustrating:banghead: Like I said, everything worked perfectly on my computer.......

Ken Puls
04-17-2007, 09:25 AM
Sorry, feathers...

Your code is late bound, so references aren't required. That was a red herring.

I found the following article on Microsoft's site that may be of use: http://support.microsoft.com/kb/q244264/

Bob Phillips
04-17-2007, 09:33 AM
No it isn't, because if ihe has set the reference, then using on an eralier version will still produce an error trying to resolve the reference.

feathers212
04-17-2007, 11:38 AM
I tried out my coding using another computer with Excel 97 and didn't appear to have any problems with creating the email with attachment. Ken, I will try to check out things in the article when I get a chance to get on the other computer that was causing me all of my problems.