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. Oops

   >Now with 16000+ columns the change that a user use a duplicate header is bigger.

   must be chance

   Difficult English

4. 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

5. 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

6. 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

7. 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….

8. Hi Philippe

   I believe I have problems with this a long time ago but it is also
   possible that it was with the SendMail methode.
   Good to hear that it is working for you.

   Hi Sam

   Yes there are a few problems with numbers and dates if you want filter.
   More if you als use Non US systems

   This is a usful chapter about international problems
   http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm

9. 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

10. Hi Philippe

    There is no option in the code to set this.
    When I have time this week I check it out.

11. Hi all

    FYI

    I update the PDF page.
    http://www.rondebruin.nl/pdf.htm

    There is a new argument now in the function call that you can use if you
    want to use a fixed file name instead of enter a name in the save dailog.

    Ron

12. 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

13. Hi Philippe

    Yes this is possible you must add code like this
    http://www.rondebruin.nl/mail/folder3/signature.htm

14. 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

15. Hi Philippe

    Send me a private mail and I will try to make the changes for you tomorrow evening.

16. 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

17. Change & in &

    The VBA tags not like the &

18. It also miss the not equal sign in this line I see now

    If Dir(SigString) “” Then

    Always problem when I try to post something in comments

Posting code? Use <pre> tags for VBA and <code> tags for inline.