Daily Dose of Excel

18 Comments

1. sam says:

   March 08, 2009 at 8:34 pm

   The copyTo option of advance filter can be used to copy more that 8192 areas from one sheet to another.
2. Ron de Bruin says:

   March 09, 2009 at 8:45 am

   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. Ron de Bruin says:

   March 09, 2009 at 8:53 am

   Oops

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

   must be chance

   Difficult English
4. Philippe says:

   March 13, 2009 at 7:25 am

   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. Ron de Bruin says:

   March 13, 2009 at 4:19 pm

   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. Philippe says:

   March 14, 2009 at 3:00 am

   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. sam says:

   March 14, 2009 at 4:36 am

   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. Ron de Bruin says:

   March 14, 2009 at 8:58 am

   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. Philippe says:

   June 17, 2009 at 6:27 am

   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. Ron de Bruin says:

    June 17, 2009 at 1:37 pm

    Hi Philippe

    There is no option in the code to set this.
    When I have time this week I check it out.
11. Ron de Bruin says:

    August 26, 2009 at 11:11 am

    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. Philippe says:

    September 28, 2009 at 2:09 am

    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. Ron de Bruin says:

    September 28, 2009 at 9:16 am

    Hi Philippe

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

    September 28, 2009 at 11:08 am

    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. Ron de Bruin says:

    September 28, 2009 at 3:21 pm

    Hi Philippe

    Send me a private mail and I will try to make the changes for you tomorrow evening.
16. Ron de Bruin says:

    September 29, 2009 at 8:37 am

    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 Data\Microsoft\Signatures\Mysig.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") & _
                    "\AppData\Roaming\Microsoft\Signatures\Mysig.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. Ron de Bruin says:

    September 29, 2009 at 8:42 am

    Change & in &

    The VBA tags not like the &
18. Ron de Bruin says:

    September 29, 2009 at 9:36 am

    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

To post VBA code in your comment, use <code> tags, like this: <code lang="vb">Code goes here</code>.