VB compatability

[flavio]

Hi All

i hope you can help on this one.

Sub Product_ON_Hold()
'Working in Excel 2000-2013
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    Dim Source As Range
    Dim Dest As Workbook
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim OutApp As Object
    Dim OutMail As Object

     Set Source = Range("A1:Z1")
    Dim cb As CheckBox
    For Each cb In ActiveSheet.CheckBoxes
        If cb.Value = xlOn Then
            Set Source = Union(Source, Range("A" & cb.TopLeftCell.Row).Resize(, 26))
        End If
    Next cb
    On Error GoTo 0

    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly
        Exit Sub
    End If

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

    Set wb = ActiveWorkbook
    Set Dest = Workbooks.Add(xlWBATWorksheet)

    Source.Copy
    With Dest.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial Paste:=xlPasteValues
        .Cells(1).PasteSpecial Paste:=xlPasteFormats
        .Cells(1).Select
        Application.CutCopyMode = False
    End With

    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Details for product on hold " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2013
        FileExtStr = ".xlsx": FileFormatNum = 51
    End If

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With Dest
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            .to = "xxxxxx@hotmail.com,zzzzzzz@hotmail.com"
            .CC = ""
            .BCC = ""
            .Subject = "Product on hold"
            .Body = "Please find attached details for On hold Product"
            '  0 = Low, 2 = High, 1 = Normal
            .Importance = 2
            .Attachments.Add Dest.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            .Display   'or use .Send
        End With
        On Error GoTo 0
        .Close savechanges:=False
    End With

    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

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

this macro works perfectly in office 2010, on attached copy in outlook it copies the first row and all pre selected rows with a checkbox as intended.


using office 2010 example:

but using the same macro in office 2007 only copy the first row and ignores any row with a check box.

using office 2007 example:

at my place of work they have these 2 versions of office and various people will use this macro in different versions of office.

any chance of getting it to work?


also how to i add more paragraphs in body text ?

.Body = "Please find attached details for On hold Product"

even if i add in macro it all seems to stay in same line.



thank you to your guys input and help as its very much appreciated.


Manny thanks

Flavio

 

[flavio]

bummpy

hummm no one has any idea? as to what could be..

1- causing this compatibility issue?

2 - how to insert more than 1 line in body?

ex: .Body line 1 = "Please find attached details for On hold Product"
line 2 = ".............."
line 3 = ".............."
line 4 = ".............."


could someone shed some light?

regards

 

[shg]

Maybe ...

    .Body = Join(Array("this is line 1", _
                       "this is line 2", _
                       "this is line 3"), vbCrLf)

 

[flavio]

Maybe ...

    .Body = Join(Array("this is line 1", _
                       "this is line 2", _
                       "this is line 3"), vbCrLf)

Brilliant!! that worked perfectly !!

Thank you so much shg for your kind help.

any idea why my scripts dont fully work in office 2007 ? at the moment is ignoring any rows that has been selected by a check box, it works fine in office 2010 but not in office 2007.


i would be greatly grateful


Best Regards

 

[shg]

You're welcome.

No idea.