[AccessD] Off Topic - Excel VBA

Hello Daryl,

thank you.
I did found what i was looking for.

Pedro



Darryl Collins schreef:
> _______________________________________________________________________________________
>
> 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
>
>