PDA

View Full Version : [SOLVED] Automatic Reference to Outlook 9.0 Object Library



Ranger
09-28-2004, 10:23 AM
Hi All,

I have a workbook from which I e-mail one sheet, which is no problem. The sheet that is e-mailed has a command button on it to e-mail a message from it to another person. The problem is that, although there is a reference to Outlook 9.0 Object Library in the original Workbook, it seems to lose it on the sheet that is e-mailed. Is there any way to create the Reference on the sheet without having to do it manually in the VB Editor?

Thanks in advance for any help.

Regards,

Bill

Zack Barresse
09-28-2004, 10:40 AM
Sure, you can use Late Binding. How about posting your code so we can take a look at it?

Ranger
09-28-2004, 10:58 PM
Hi firefytr,

Thands for the reply. Code as follows:


Sub eMailWorksheet()
Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook
Dim FileName As String
Dim y As Long
Dim TempChar As String
Dim SaveName As String
Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
FileName = ActiveSheet.Name & " - " & ActiveWorkbook.Name
For y = 1 To Len(FileName)
TempChar = Mid(FileName, y, 1)
Select Case TempChar
Case Is = "/", "\", "*", "?", """", "<", ">", "|"
Case Else
SaveName = SaveName & TempChar
End Select
Next y
ActiveSheet.Copy
Set Wb = ActiveWorkbook
Wb.SaveAs SaveName
Wb.ChangeFileAccess xlReadOnly
With EmailItem
.Subject = "Highfield Care Stationery Order Form"
.Body = "Please find attached Stationery Order Form" & vbCrLf & _
"" & vbCrLf & _
"Please Click the Received Order button to send confirmation to LearnIT"
.To = "LearnIT2002@aol.com"
.Importance = olImportanceNormal
.Attachments.Add Wb.FullName
.Send
End With
Kill Wb.FullName
Wb.Close False
Application.ScreenUpdating = True
Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing
End Sub

This is the code on the original Workbook. The code on the command button on the e-mailed sheet is very similar, with a confirmation message rather than a Worksheet to be e-mailed back.
Regards,

Bill

Jacob Hilderbrand
09-28-2004, 11:29 PM
That looks familiar.

Try this code:


Sub eMailActiveWorksheet()
Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook
Dim FileName As String
Dim y As Long
Dim TempChar As String
Dim SaveName As String
Dim olMailItem
Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
FileName = ActiveSheet.Name & " - " & ActiveWorkbook.Name
For y = 1 To Len(FileName)
TempChar = Mid(FileName, y, 1)
Select Case TempChar
Case Is = "/", "\", "*", "?", """", "<", ">", "|"
Case Else
SaveName = SaveName & TempChar
End Select
Next y
ActiveSheet.Copy
Set Wb = ActiveWorkbook
Wb.SaveAs SaveName
Wb.ChangeFileAccess xlReadOnly
With EmailItem
.Subject = "Insert Subject Here"
.Body = "Insert message here" & vbCrLf & _
"Line 2" & vbCrLf & _
"Line 3"
.To = "User@Domain.Com"
'.Importance =
.Attachments.Add Wb.FullName
.Send
End With
Kill Wb.FullName
Wb.Close False
Application.ScreenUpdating = True
Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing
End Sub

Sub eMailActiveWorkbook()
Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook
Dim olMailItem
Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Wb = ActiveWorkbook
Wb.Save
With EmailItem
.Subject = "Insert Subject Here"
.Body = "Insert message here" & vbCrLf & _
"Line 2" & vbCrLf & _
"Line 3"
.To = "User@Domain.Com"
'.Importance =
.Attachments.Add Wb.FullName
.Send
End With
Application.ScreenUpdating = True
Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing

End Sub

Jacob Hilderbrand
09-28-2004, 11:32 PM
For the importance you need to use numbers instead of constants.

High = 2
Normal = 1
Low = 0

Ranger
09-28-2004, 11:47 PM
Hi Jacob,

Thanks for the reply.

It's not the code that is the problem, that works well. The sheet that has been e-mailed has a Command Button on it to e-mail back a reply, but it loses the Reference to Outlook 9.0 Object Library and I don't want the supplier to have to go to VBA Editor to add the Reference.

Regards,

Bill

Zack Barresse
09-28-2004, 11:52 PM
Can you zip and attach the workbook in question to this thread? That may help. :)

Jacob Hilderbrand
09-29-2004, 12:30 AM
The code that I posted does not use a reference to Outlook at all. Try the code without the reference. Email it to yourself and try to email it back. If there are any problems post them here and we will try to assist you further.

Ranger
09-29-2004, 12:48 AM
HI firefytr,

I used this code, which is in Ozgrid's 'Hey tht is Cool' Forum, by lasw10. I attached this to the command button and it adds the reference before attempting to send the e-mail.


Dim ID As Variant
On Error Resume Next
'Reference ADO Object Library using Major / Minor GUID
Set ID = ThisWorkbook.VBProject.References
ID.AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 9, 0


It also supplied the code to find the GUID No if you don't know it. Click the Reference and then run this code:


Sub Grab_References()
Dim n As Integer
Sheets.Add
ActiveSheet.Name = "GUIDS"
On Error Resume Next
For n = 1 To ActiveWorkbook.VBProject.References.Count
Cells(n,1) = ActiveWorkbook.VBProject.References.Item(n).Name
Cells(n,2) = ActiveWorkbook.VBProject.References.Item(n).Description
Cells(n,3) = ActiveWorkbook.VBProject.References.Item(n).GUID
Cells(n,4) = ActiveWorkbook.VBProject.References.Item(n).Major
Cells(n,5) = ActiveWorkbook.VBProject.References.Item(n).Minor
Cells(n,6) = ActiveWorkbook.VBProject.References.Item(n).fullpath
Next n
End Sub

It appears to have solved my problem.

Thanks to you and Jacob for your time and patience.

Regards,

Bill

Jacob Hilderbrand
09-29-2004, 12:56 AM
Glad to help.

By the way, that code requires that the user allows access to the VBA project.

Tools | Macro | Security | Trusted Sources
[] Trust access to Visual Basic Project