Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mailing filtered data from Excel (2000 sr.1)

    Subject edited by HansV - please give your posts meaningful subjects so that others have an idea what it is about

    My problem:

    Step by step.

    TIPE OF FILTRE
    Example for your experiment is SO.FARMA.MORRA S.P.A.

    Filtre from column C.
    Send the filtered sheet.

    The db of email is in the sheet E_MAIL.
    Add the summ of record in D1 of the sheet filtered

    TIPE OF EMAIL
    Example for experiment is SO.FARMA.MORRA S.P.A.
    subject of email:
    INSOLUTI & SO.FARMA.MORRA S.P.A. & string in RANGE A & string in RANGE B & string in RANGE I & date sended sheet

    Final result of subject:
    INSOLUTI SO.FARMA.MORRA S.P.A. 235 04500 40260 25/07/2004

    NAME OF SHEET
    Final result Name of sheet sended same option of the subject:
    INSOLUTI SO.FARMA.MORRA S.P.A. 235 04500 40260 25/07/2004

    Before the send email make a copy in C:INSOLUTI_SENDED... put here the file sended

    Note: the joint string from column A and column B and column I is the index

    Nothing INTESTAZIONE are equal from this joint

    Note1: the strucure of the file to send is the same from the master shet IMPGATI

    hope i am clear....


    http://www.gssitaly.com/filtre_and_send.zip

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    I am not at all clear on what you want to do, but if you are looking for code to send email (which it seems you are), try playing with the code from Ron DeBruin's site for sending email from excel. It has examples for sending sheets, ranges, etc.

    Steve

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    Tks, sdckapr, for suggestion, but i am not a big programmer...
    I hope HansV see this post, it know my knoledge in VBA.
    Tks ...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    It's not going to work this way. The name of a worksheet can be at most 31 characters long. Moreover, it is not a good idea to include periods <big>.</big> (such as in the name of the company) and slashes <big>/</big> (in the date format) in the name of a worksheet or of a workbook. So you will have to think of a much simpler name for the worksheet and of the file.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    In the first time tks, as usual... (i have hoped good! only you...)

    ok, for the name of file joint only column A, B and I & date of sended file final result:
    235_04500_40260_ 25072004

    But for the subject of e-mail is possible my old indication?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    Sal,

    I have attached a zip file with a workbook to serve as template for the file to be sent, and a text file with the code.

    You must do the following things:

    - Put the workbook MailTemplate.xls in a convenient folder.
    - Make a backup copy of FILTRE AND SEND.xls.
    - Create a module in FILTRE AND SEND.xls.
    - Copy the code from the text file into the module.
    - Modify the path in the constant at the beginning of the code to match the location of MailTemplate.xls
    - Right-click the SEND button in the IMPAGATI worksheet.
    - Select Assign Macro (Assegna Macro) and select Send_Click from the list of macros.
    - Save the workbook.
    - Test the Send button (use your own e-mail address as a test in the E-MAIL worksheet.)

    Good luck!

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    NOT POSSIBLE!!! (But i am sure)
    FEW MINUT FOR BIG WORK...
    IT WORK ON THE FIRST TEST.
    TKS FOR YOUR TIME
    Sal

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    3 little modify:

    1- unselect the filtre and return in "show all" mode, after to send e mail.
    2- if i resend the same e-mail in the same day not make to apper the message "file existis do want to over writhe? yes/no" but directly to save without this message.
    3- Make appear a msg box if is selceted a INTESTAZIONE that is not present in the db E-MAIL

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    See modified code.

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    UHMMM...
    New macro.
    I have selcted from the filtre A. E I. PORTOLANO GUANTI
    Note: in the sheet E-MAIL is present only the old 2 INTESTAZIONE and not A. E I. PORTOLANO GUANTI
    and start the new macro i recieve a msg error( SEE JPG)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    Change the last part of the code to this:

    ErrHandler:
    If InStr(Err.Description, "VLookup") Then
    MsgBox "E-mail address not available.", vbExclamation
    Else
    MsgBox Err.Description, vbExclamation
    End If
    Resume ExitHandler
    End Sub

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    MEGA STAR...

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    Doubt...
    The sheet IMPAGATI import new data at every day and add to the bottom of this the new record.
    my Doubt is:

    If i send in the date 25/07/2004 a file to SO.FARMA.MORRA S.P.A. all is ok!
    The next day i import a new record and add this to bottom of IMPAGATI.
    I i send the e-mail in the date 26/07/2004 to this INTESTAZIONE i re-send the old and new record present in already in the date 25/07/2004 plus the new.
    I thing to insert a control code in the column M for example "SENDED 25072004" when the e-mail in date 26/07/2044 in this mode (from advanced filtre) i send only a new record and not the new and old record already just sended innthe date 25/07/2004...
    I hope be clear..

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    Make a backup copy again, to be on the safe side.

    In cell M2 of IMPAGATI, enter Sent.
    Click in cell A2.
    Turn the AutoFilter off, then on again (select Dati | Filtro | Filtro automatico, then select it again.)
    This will include column M in the AutoFilter.
    Replace the code for Send_Click by the attached code.

  15. #15
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mailing filtered data from Excel (2000 sr.1)

    accccccc!!!!!!!!!

    Your last modify dont work (i have difficult, not you) , all is ok but not inserted the code of controll... in M
    The last work is in module2

    http://www.gssitaly.com/filtre_and_send.zip

    OH!OH! It work only if i insert SENT in M3 (?)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •