Code not running on both '07 and '10; references missing on '07

**freddiethomas** · 11-08-2012, 07:26 AM

Hi,

I have written a project able to generate ppts. based on excel spreadsheet content. In addition I have some code which pulls some user data from Outlook. All the code was written in Office 2010 using Outlook 14.0 object library & Powerpoint 14.0 object library. When the document is opened on an '07 machine, the 'cannot find project or library' error is shown. When I check in the VBA references the two reference libraries I have used to write the code are marked 'MISSING'. After some research, I believe the only answer to my problem is to rewrite the code using late binding rather than early binding. I'm not exactly sure how the syntax would look, & how it differs from what I have. I've included the part of my code that references outlook below.

Option Explicit

Public username As String
Public moment As String

'Get current date/time & current user info & store it in given locations
Sub userfill(userloc As Range, dateloc As Range, timeloc As Range)

Dim shortname As Range
Dim fullname As Range
Dim primaryemail As Range
Dim ext As Range
Dim objNS As Outlook.Namespace
Set objNS = Outlook.GetNamespace("MAPI")

'Set ranges
Set shortname = Sheet10.Range("AZ6")
Set fullname = Sheet10.Range("AZ7")
Set primaryemail = Sheet10.Range("AZ8")
Set ext = Sheet10.Range("AZ9")

'Get data
username = Application.username
fullname.Value = objNS.Session.CurrentUser
primaryemail.Value = objNS.Session.CurrentUser.AddressEntry.GetExchangeUser.PrimarySmtpAddress
ext.Value = Right(objNS.Session.CurrentUser.AddressEntry.GetExchangeUser.BusinessTelephoneNumber, 4)
'Trim & format shortname
shortname.Value = Trim(Left(Split(fullname.Value, ",")(1), 2) & ". " & Split(fullname.Value, ",")(0))
'Trim & format fullname
fullname.Value = Split(fullname.Value, ",")(1) & " " & Split(fullname.Value, ",")(0)
fullname.Value = Trim(fullname.Value)

moment = Now
    
userloc = username
dateloc = DateValue(moment)
timeloc = TimeValue(moment)
        
End Sub

Many thanks,

**WasWodge** · 11-08-2012, 07:29 AM

Silly question, Have you tried ticking the checkboxes where it says missing?

**freddiethomas** · 11-08-2012, 08:01 AM

Those boxes are already ticked, I think excel ticks them automatically when the file loads, and just marks the libraries as 'MISSING'

Thanks though

**JosephP** · 11-08-2012, 08:42 AM

for late binding you need to declare all non-excel objects as simply Object and use CreateObject to initialize them

Sub userfill(userloc As Range, dateloc As Range, timeloc As Range)

   Dim shortname              As Range
   Dim fullname               As Range
   Dim primaryemail           As Range
   Dim ext                    As Range
   Dim objNS                  As Object
   Set objNS = CreateObject("Outlook.Application").GetNamespace("MAPI")

   'Set ranges
   Set shortname = Sheet10.Range("AZ6")
   Set fullname = Sheet10.Range("AZ7")
   Set primaryemail = Sheet10.Range("AZ8")
   Set ext = Sheet10.Range("AZ9")

   'Get data
   username = Application.username
   fullname.Value = objNS.Session.CurrentUser
   primaryemail.Value = objNS.Session.CurrentUser.AddressEntry.GetExchangeUser.PrimarySmtpAddress
   ext.Value = Right(objNS.Session.CurrentUser.AddressEntry.GetExchangeUser.BusinessTelephoneNumber, 4)
   'Trim & format shortname
   shortname.Value = Trim(Left(Split(fullname.Value, ",")(1), 2) & ". " & Split(fullname.Value, ",")(0))
   'Trim & format fullname
   fullname.Value = Split(fullname.Value, ",")(1) & " " & Split(fullname.Value, ",")(0)
   fullname.Value = Trim(fullname.Value)

   moment = Now

   userloc = username
   dateloc = DateValue(moment)
   timeloc = TimeValue(moment)

End Sub

you also need to declare any constants you may use from the other libraries