Excel Excel VBA - attach and send .xls and .xlsx

Joined
Jan 26, 2012
Messages
3
Reaction score
0
Hi there, am new to this forum and to VBA so be gentle :-)

I have written/borrowed some code where I attach a worksheet in a temporary file and then attach it in a mail via Lotus Notes.

The problem for me is that I have users that are on different Office versions, 2003 and 2010 - how do I write that so if recognizes both versions ?

The code is a such:

Sub email()

Dim stFileName As String
Dim vaRecipients As Variant
Dim noSession As Object
Dim noDatabase As Object
Dim noDocument As Object
Dim noEmbedObject As Object
Dim noAttachment As Object
Dim stAttachment As String

'Copy the active sheet to a new temporarily workbook.
With ActiveSheet
.Copy
stFileName = .Range("A1").Value
End With

stAttachment = stPath & "\" & stFileName & ".xls"

'delete email button
ActiveSheet.Shapes("EmailBtn").Delete

'Save and close the temporarily workbook.
With ActiveWorkbook
.SaveAs stAttachment
.Close
End With

'Create the list of recipients.
vaRecipients = VBA.Array("***@ccc.com")

'Instantiate the Lotus Notes COM's Objects.
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")

'If Lotus Notes is not open then open the mail-part of it.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

'Create the e-mail and the attachment.
Set noDocument = noDatabase.CreateDocument
Set noAttachment = noDocument.CreateRichTextItem("stAttachment")
Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)

'Add values to the created e-mail main properties.
With noDocument
.Form = "Memo"
.SendTo = vaRecipients
.CopyTo = vaCopyTo
.subject = stSubject
.Body = vaMsg
.SaveMessageOnSend = True
.PostedDate = Now()
.Send 0, vaRecipients
End With

'Delete the temporarily workbook.
Kill stAttachment

'Release objects from memory.
Set noEmbedObject = Nothing
Set noAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

MsgBox "Die E-Mail wurde erfolgreich erstellt und gesendet - Einen schönen Tag noch!", vbInformation

End Sub

Thank you in advance

Nordahl
 
Nordahl,

You may be able to replace this line of code:
stAttachment = stPath & "\" & stFileName & ".xls"

With this:
If Application.Version = "14.0" Then
stAttachment = stPath & "\" & stFileName & ".xlsx"
ElseIf Application.Version = "11.0" Then
stAttachment = stPath & "\" & stFileName & ".xls"
endif

This will select the version you are creating from(assuming you are in Excel when running the macro), I am not sure you can control the version received.

Stoneboysteve
 
Back
Top