Automatic Reference to Outlook 9.0 Object Library

[Ranger]

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]

Sure, you can use Late Binding. How about posting your code so we can take a look at it?

[Ranger]

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]

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]

For the importance you need to use numbers instead of constants.

High = 2
Normal = 1
Low = 0

[Ranger]

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]

Can you zip and attach the workbook in question to this thread? That may help.

[Jacob Hilderbrand]

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]

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]

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