0

So I have decided to step a little bit into programming and ease my workloads; I'm really amateur. Most of my learning has come from Internet research (google), YouTube and Udemy, so please be patient. I'm really enjoying this but I need to understand it deeper.

This is my issue: I have to send a report to several suppliers. I have the emails list in one worksheet and a dynamic array in another sheet. Basically I'm commanding the Macro to filter through the suppliers list, create a file and email the file to them. The issue in here is that everything was working smoothly until I changed my If for a For i. I did this since when my parameter hit an empty cell it showed an error and I decided to optimized the code for "lastrow" usage.

So, I made all the changes, hope you're able to spot the error, since this is my first time and I'm running out of ideas.

Sub Enviarcorreo()
    Dim NomProveedor As String 'names
    Dim CorProveedor As String 'email address
    Dim MsgProveedor As String 'message
    Dim nwsh As Workbook
    Dim Tempru As String
    Dim FilNam As String
    Dim Outlookapp As Outlook.Application
    Dim Oitem As Outlook.MailItem
    Dim i As Long 'variable created for looping the emails range list
    Dim x As Long 'variable created for looping beneath others worksheets; different _
                    letter used for easier reading
    Dim lastrow As Integer
    Dim rang As Range
    
    
    lastrow = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(3).Range("A:A"))
    CorProveedor = ThisWorkbook.Sheets(3).Cells(x + 2, 3).Value
    NomProveedor = ThisWorkbook.Sheets(3).Cells(x + 2, 2).Value
    MsgProveedor = ThisWorkbook.Sheets(4).Range("P3").Value
    
    ThisWorkbook.Sheets(3).Select 'selects email addresses worksheet
    Range("A1").Activate 'emails range list
    rang = ActiveCell.Offset(x, 0) 'this is the variant for the last argument used _
                                    so it keeps looping
    
    Set rang = ActiveCell.Offset(x, 0)
            
        For i = 2 To lastrow
        x = 1
            Do Until rang = ""
            ActiveCell.Copy
            ThisWorkbook.Sheets(3).Cells(i, 1).Copy
            Sheets(1).Select
            Range("$K$3").Select
            ActiveSheet.Paste
            Call FilAv
        
            Sheets(1).Range("A6").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
            Set nwsh = Application.Workbooks.Add
            Range("A1").PasteSpecial xlPasteValues
            Range("A1").PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
            
            Tempru = VBA.Environ("Temp") & "\"
            FilNam = Tempru & "Reporte Ordenes de compras abiertas DRAEXLMAIER." & ".XLSX"
            nwsh.SaveAs FilNam

            Set Outlookapp = New Outlook.Application
            Set Oitem = Outlookapp.CreateItem(olMailItem)

            With Oitem
                .To = ThisWorkbook.Sheets(3).Cells(x + 1, 3).Value
                .Subject = "Reporte - " & ThisWorkbook.Sheets(3).Cells(x + 1, 2).Value
                .Body = MsgProveedor
                .Attachments.Add FilNam
                .Send
            End With
            x = x + 1
        
            ActiveWorkbook.Close savechanges:=False
            VBA.Kill FilNam
        
        Next i
        
End Sub
marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
Víctor Herrera
  • 1
  • 1
  • 4
    Missing the `Loop` to close the `Do Until`. But get rid of the `Do Until` loop and use `i` instead of `x`. – BigBen Dec 15 '20 at 01:04
  • Show `FilAv` also, as it might modify thnigs – John Alexiou Dec 15 '20 at 01:15
  • 3
    If you were a little more careful with indentation of code you would have noticed the mismatch in the `Do` loop. – John Alexiou Dec 15 '20 at 01:18
  • @BigBen Actually I placed the next i for closing the looop, though it seems it is not working. I also placed the "Loop" at the end of my code, but didn't run, keeps showing the same error. – Víctor Herrera Dec 15 '20 at 15:08
  • As I mentioned in my comment, drop the `Do Until` loop entirely. You need one loop here, the `For` loop. – BigBen Dec 15 '20 at 15:10
  • @JohnAlexiou Actually FilAv is an advanced filter in my data source that will help only the correct information to be sorted by supplier in NomProveedor – Víctor Herrera Dec 15 '20 at 15:35
  • Thanks @BigBen. Therefore I have to do something like: For i <> "" xxxx xxxx End Sub Though my issue in here would be to Debug the code so when it hits an empty cell it stops sending emails and not showing me errors like "Email addres not found". – Víctor Herrera Dec 15 '20 at 15:38
  • You can test if a cell is empty using `IsEmpty`. Also, [this is a better way to find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Dec 15 '20 at 15:52
  • @BigBen thanks a lot for your help and patience man; I really appreciate it. – Víctor Herrera Dec 15 '20 at 16:04

0 Answers0