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

    why error in.... send xls (2000 sr-1)

    The macro in this wbook filtre and send a xls via e mail....
    but have error "object not defined... " into code

    "Sub mainInvia(strCodice As String)"

    line:


    '''''''''''ERROR HERE
    Set rng = Sheets("A.T.CAMPANIA").Range(strRange)
    '''''''''''ERROR HERE

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

    Re: why error in.... send xls (2000 sr-1)

    No idea, the code runs without errors on my PC. However, the sheet Tabella doesn't seem to contain valid e-mail addresses.

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

    Re: why error in.... send xls (2000 sr-1)

    ... Solved the old prob about range
    Is possible to save the sheet in wbook already created with the name of var strCodice and insert into wbook only one sheet named with the value of var strCodice, an not 3 sheet?

    ...........

    Application.DisplayAlerts = False
    wrk.SaveAs "C:STORICO_INPS" & strNomeFile & "_" & strData & ".xls"
    ThisWorkbook.Activate
    Selection.Copy
    wrk.Sheets(1).Activate
    ActiveSheet.Paste
    ActiveSheet.Columns("A:M").AutoFit
    ActiveSheet.Range("A2").Select
    '''''''HERE THE NAME OF SHEET
    ActiveSheet.Name = strCodice
    '''''''HERE THE NAME OF SHEET
    wrk.Save
    vntEmail = Split(strDest, "|")
    For intIndex = 0 To UBound(vntEmail)
    If vntEmail(intIndex) <> "" Then
    wrk.SendMail vntEmail(intIndex), strOggetto
    End If

    ..........

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

    Re: why error in.... send xls (2000 sr-1)

    Change the line

    Set wrk = Application.Workbooks.Add

    to

    Set wrk = Application.Workbooks.Add(xlWBATWorksheet)

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

    Re: why error in.... send xls (2000 sr-1)

    good for Set wrk = Application.Workbooks.Add(xlWBATWorksheet)

    but error on see the image the var ActiveSheet.Name = strCodice is blank i have insert this var in a Global statement but not work...

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

    Re: why error in.... send xls (2000 sr-1)

    You don't need the global variable. Change

    ActiveSheet.Name = strCodice

    to

    ActiveSheet.Name = strNomeFile

    because strNomeFile is the name of the argument in INVIA.

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

    Re: why error in.... send xls (2000 sr-1)

    PERFECT!

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

    Re: why error in.... send xls (2000 sr-1)

    assuming after i have sends the various wbook via e-mail, (see the attached files) and the user can slelect from the column M a value, from this wbook, is possible to update into column M of master_inps(closed and into dir my serverdirmaster_inps.xls) into same column M?
    (note: in L of sheet sended and into master_inps are present a unique id)

    If you have a suggestion for best way.... are you narurally welcome.
    Tks. Sal.

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

    Re: why error in.... send xls (2000 sr-1)

    Sorry, I don't understand your question. Can you try to explain again?

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

    Re: why error in.... send xls (2000 sr-1)

    with the macro into wbook master_inps i send the piece of this wbook to many user via email. (see the attech file of variuos file sended)
    All wbook sended have the same structure of master_inps.

    Into column L is present a unique ID (in master and in all wobbok sended)
    Into column M the user can insert a note. (in master and in all wobbok sended)

    after i have sended the variuos email with the filtred data i close the master wbook
    in myserverdirdirmaster_inps.xls

    If the user that have the new wbook 4500.... insert a value in M is possible to update the column M of the wbook master_inps.xls with the value inserted from wbook 4500...?

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

    Re: why error in.... send xls (2000 sr-1)

    Are the recipients of the e-mails all on the same network, and do they have access to the folder myserverdirdir?

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

    Re: why error in.... send xls (2000 sr-1)

    yes, the same...

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

    Re: why error in.... send xls (2000 sr-1)

    hummmmm why the macro filtre, create, save and send, a wbook with a blank data...
    In effect if you see into range A of master_unps.xls for example, the code 6552, 4562 not is present...

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

    Re: why error in.... send xls (2000 sr-1)

    I don't think this is a good idea. Excel is not a multi-user program, so if several users update the workbook they received at the same time, there would be a conflict in updating the master workbook.

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

    Re: why error in.... send xls (2000 sr-1)

    Because the code loops through the values in column A of the Tabella worksheet without checking if the filter on the A.T.CAMPANIA worksheet leaves any records. You could check in the mainInvia procedure:

    ...
    Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeV isible).EntireRow
    On Error Resume Next
    Range("A3:A689").SpecialCells xlCellTypeVisible
    If Err = 0 Then
    rng.Select
    Call INVIA(strDest, "RATE INPS DA SISTEMARE - TAB. T8327", strCodice)
    End If
    End Sub

Page 1 of 3 123 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
  •