VBA Question

I am writing a vba program that will send an a excell spread sheet to different distribution lists deponding on what a user fills out in the spread sheet. The problem I am running into is that everyone has different versions of excell and outlook. Does anyone know how to load the proper library to support all these different versions.

thanks for your help!


Private Sub cmdSendMail_Click()
strver= Application.Version
'get version and how do I load the correct library deponding on version

'Save work book
' ActiveWorkbook.SaveAs "C:\ FormExcelVersion.xls"

Dim objOL As New Outlook.Application
Dim objMail As MailItem
Dim strMail, MailLen

Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)


If Sheet1.chkone.Value = True Then
strMail = strMail + "emailaddresss" & ";"
End If

If Sheet1.chkTrack.Value = True Then
strMail = strMail + "emailaddresss" & ";"
End If

If Sheet1.chkEditor.Value = True Then
strMail = strMail + "emailaddresss" & ";"
End If



MailLen = Len(strMail) - 1
MsgBox Left(strMail, MailLen)
strMail = Left(strMail, MailLen)


With objMail
.to = strMail
.Subject = "test sheet " & Range("C15")
.Body = "This is an automated message from Excel. " & _
"Body: " & _
Format(Range("A14").Value, "$ #,###.#0") & "."
.Display
End With
' objMail.Attachments.Add("C:\FormExcelVersion.xls").DisplayName = "Start UP Form"
'objMail.Send
Set objMail = Nothing
Set objOL = Nothing
End Sub

I think that for both Excel and Outlook higher version libraries always work for previous versions. So just reference the 9.0 (or 10.0 I suppose would be the 2002 office version) libraries in your project and the P&D wizard should make sure all the required components are sent out.

As far as saving the workbooks to the most recent version:

Quote:

---

FileFormat Optional Variant. The file format to use when you save the file. For a list of valid choices, see the FileFormat property. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.

---

The default file format is the version of Excel that is saving the file. So you don't have to mess with it, unless you have someone that needs to open an Excel 5 workbook and save it in Excel 7 format to "update" it. I wouldn't recommend that because it may be possible to lose some formatting when you change the versions and if the file has to go back to wherever it came from that person may not be able to open it because the file format may now be higher than the version of Excel they are using. If the file came in a 5.0, just leave it. Higher levels of Excel shouldn't have any problems dealing with a lower version file format.

The file format constant xlWorkbookNormal (-4143) may update the version. I'm not sure. You can get the version of Excel being used with Application.Version.

I am not using the package and deployment wizzard. I am doing this in the vb that comes with excell. I referenced excell 10 lib and it works fine on my pc but when some one who has 97 it will not look.

I then go into references and It will say Missing for the obj 10 lib

thanks

Sorry. I'm not sure on that one. One of the problems with VBA is you can't deploy required components. In some cases, older Office apps can't run code from newer versions. I just use really old Office apps. :rolleyes: