Update VBA code page for Tables and Create/Mail PDF files

Hi all

I update two pages for Excel 2007 on my website.

Create and mail PDF files with Excel 2007
http://www.rondebruin.nl/pdf.htm

VBA code examples for Tables in Excel 2007 or a List in Excel 2003
http://www.rondebruin.nl/tablecode.htm

Maybe useful for the readers of the blog
If you have Suggestions or find bugs let me know

Thanks

Ron de Bruin
http://www.rondebruin.nl/tips.htm

Posted in Uncategorized

18 thoughts on “Update VBA code page for Tables and Create/Mail PDF files

  1. The copyTo option of advance filter can be used to copy more that 8192 areas from one sheet to another.

  2. Hi Sam

    Yes i know
    But for Autofilter in a Table this is not a option if you
    want to copy the visible data.

    Another reason is that there is a bug in xlFilterCopy when you have duplicate headers in your data.
    Now with 16000+ columns the change that a user use a duplicate header is bigger.

  3. Hello Ron,

    Your marvelous pdf apps is exactly what I need since a several months and what I imagine to develop if didn’t find such a sample.
    Could you just tell me if it’s possible to use a pre-defined OL mailing list instead of a single To address ?

    Thanks by advance

    Best regards

    Philippe

  4. Hi Philippe

    We must add this to the function

    Normal you use this in the mail macro for a Outlook group
    .Recipients.Add “GroupName”

    It is possible to add a argument for this in the function
    But maybe you can call another macro from my site and attach the pdf

    See
    http://www.rondebruin.nl/sendmail.htm

    If you want to use the function on the pdf page let me know then I change it for you
    this weekend

  5. Hi Ron,

    In France, there is an Ad saying “100% of the winners tried their luck” : In other words, when you pass the mailing list name in the .To parameter, OL “understands”, in a split second, that this the mailing list found in the address book.

    So, without change, it works !

    Another time, many thanks for your code,

    Have a nice weekend,

    Philippe

  6. Ron,

    There is another bug you need to keep in mind when filtering

    For Numbers stored as Text the Not Equal to Operator will not work with Filters (Both Auto and Advanced)

    Example
    Clien ID
    001
    003
    002
    001
    003
    002

    If you filter for Client ID Not Equals 002 you will still see the complete list….

  7. Hi Ron,

    I use your proc with happyness since several weeks and I just see that the pdf file created by VBA is not in a standard A4 format.

    Do you know how to change this format ?

    Another time, thanks a lot

  8. Hi Ron,

    To complete my customized vba procedure largely based on your guidelines, I would like to know if it’s possible to include in the mail a pre-set signature ?

    Thanks by advance for your help.

    Philippe

  9. Thanks Ron !

    Just one more question (for that time !) :

    In the “RDB_Mail_PDF_Outlook” Function, do I have to adapt the strbody argurment ?

    Philippe

  10. Try this for Vista
    Replace the RDB_Mail_PDF_Outlook function with this one and add the GetBoiler function from Dick
    Be sure that you change Mysig.txt to your sig name

    In XP use this string

    SigString = “C:Documents and Settings” & Environ(“username”) & _
    “Application DataMicrosoftSignaturesMysig.txt”

    Function RDB_Mail_PDF_Outlook(FileNamePDF As String, StrTo As String, _
                                  StrSubject As String, StrBody As String, Send As Boolean)
        Dim OutApp As Object
        Dim OutMail As Object
        Dim SigString As String
        Dim Signature As String

        Set OutApp = CreateObject(“Outlook.Application”)
        OutApp.Session.Logon
        Set OutMail = OutApp.CreateItem(0)

        SigString = “C:Users” & Environ(“username”) & _
                    “AppDataRoamingMicrosoftSignaturesMysig.txt”

        If Dir(SigString)  “” Then
            Signature = GetBoiler(SigString)
        Else
            Signature = “”
        End If

        On Error Resume Next
        With OutMail
            .To = StrTo
            .CC = “”
            .BCC = “”
            .Subject = StrSubject
            .Body = StrBody & vbNewLine & vbNewLine & Signature
            .Attachments.Add FileNamePDF
            If Send = True Then
                .Send
            Else
                .Display
            End If
        End With
        On Error GoTo 0

        Set OutMail = Nothing
        Set OutApp = Nothing
    End Function

    Function GetBoiler(ByVal sFile As String) As String
    ‘Dick Kusleika
       Dim fso As Object
        Dim ts As Object
        Set fso = CreateObject(“Scripting.FileSystemObject”)
        Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
        GetBoiler = ts.readall
        ts.Close
    End Function

Leave a Reply

Your email address will not be published. Required fields are marked *