[AccessD] Off Topic - Excel VBA

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________


Pedro

Have a look here

<<http://www.excelyourbusiness.com.au/EmailAndZipData.htm#EmailFromExcel>>

You solution can be found there.

Cheers
Darryl. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pedro Janssen
Sent: Friday, 27 August 2010 7:50 AM
To: AccessD
Subject: [AccessD] Off Topic - Excel VBA

Dear list,

i use the code below to send a whole Workbook by mail.

'Working in 2000-2010
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim I As Long

    Set wb1 = ActiveWorkbook

    If Val(Application.Version) >= 12 Then
        If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file, there will" & 
vbNewLine & _
                   "be no VBA code in the file you send. Save the" & 
vbNewLine & _
                   "file first as xlsm and then try the macro again.", 
vbInformation
            Exit Sub
        End If
    End If

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    'Make a copy of the file/Open it/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "copie van" & wb1.Name & "_" & Format(Now, "dd-mmm-yy")
    FileExtStr = "." & LCase(Right(wb1.Name, _
                                   Len(wb1.Name) - InStrRev(wb1.Name, 
".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

    With wb2
        On Error Resume Next
        For I = 1 To 3
            .SendMail "@", _
                      "onderwerp"
            If Err.Number = 0 Then Exit For
        Next I
        On Error GoTo 0
        .Close SaveChanges:=False
    End With

    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub



I use the following code for separate worksheets, where linked values of 
the active excelworksheet are copied and special pasted as values, 
before send by mail.
I would like to ajust the code below so that it is working on a whole 
workbook

With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False



Who can help me?

Pedro

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended 
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review, 
retransmission, dissemination or other use of, or taking of any action in reliance 
upon this information, by persons or entities other than the intended recipient is 
prohibited.

If you have received this in error, please contact the sender and delete this e-mail 
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute 
the information contained in this e-mail and any attached files, with the permission 
of the sender.

This message has been scanned for viruses.
_______________________________________________________________________________________