can't find the error to send emails from excel

[tiastahl]

Good Morning/Afternoon everyone!

I have 2 macros I wrote in an excel file to make pdf's and then email them out to the people they need to be sent to. Both macro's work the last time i ran them in November. But only 1 of them works today. I've read over the code a whole bunch of times and can not figure out where the error is. I may have changed something before the holidays, i can't remember. Take a look and let me know if you can see my mistake. I'll post the working code and non-working code. Both codes are making the file folder/directory and placing the pdfs into it. The "report card" one is just not emailing them out.

This is the code that is making the directory and pdfs, but not emailing them.

Sub EmailPDFs()
    Dim FileName As String
    Dim ISAname As String
    Dim AttacmentFileName As String
        
    'hides the screen updates to keep the screen from blinking
    'and help the program run faster
    Application.ScreenUpdating = False
       
    Dim FileFolder As String
    FileFolder = "C:\Documents and Settings\" & Application.InputBox("What is your user name?") & _
        "\Desktop\Turnback Metrics"
    MkDir FileFolder
        
    'defines array to hold names with 1 or more turnbacks for the month
    Dim Num As Integer
    Num = Worksheets("LookUp").Cells(3, "I").Value
    Num = Num - 1 'accounts for array's starting with 0
    
    'sets up counter to use to loop through putting names in array, printing files, etc
    Dim counter As Integer
    counter = 0
    
    'puts names into array
    Dim Names() As String 'defines array
    ReDim Names(Num) 'sets array size
    Do While counter <= Num
        Names(counter) = Worksheets("LookUp").Cells(counter + 3, "G").Value
        counter = counter + 1
    Loop
          
    'sets up stuff for email
    Dim emailAddress As String
    Dim OutApp As Object
    Dim OutMail As Object


    
    'cycle through names in the arrary, make a pdf or the reportcard, and email it.
    counter = 0 'resets counter
    Do While counter <= Num
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        'passes name from array into cell
        Worksheets("ReportCard").Cells(2, "I").Value = Names(counter) 
        
        ISAname = Worksheets("ReportCard").Cells(2, "I").Value


        'make PDF file and store in folder on desktop
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FileFolder & "\" & ISAname, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
  
        'pull e-mail address from cell on worksheet
        emailAddress = Worksheets("ReportCard").Cells(3, "I").Value
        AttacmentFileName = FileFolder & "\" & ISAname & ".pdf"


        On Error Resume Next
        
        'Change the mail address and subject in the macro before you run it.
        With OutMail
            .To = emailAddress
            .Subject = "Parts Orders Turnback Monthly Report"
            .Body = "Attached is your detailed summary of the parts order turnbacks from the month."
            .Attachments.Add AttacmentFileName
            .Send
        End With
        
        Set OutMail = Nothing
        Set OutApp = Nothing
        counter = counter + 1 'update counter
    Loop
    
    'clears data from reportcard
    Worksheets("ReportCard").Cells(2, "I").Value = ""
        
    On Error GoTo 0


    MsgBox ("PDFs have been made and sent.  Please file and/or delete the folder on your desktop.")
    
    'unhides the screen updates
    Application.ScreenUpdating = True


End Sub

here is the code that WORKS

Sub EmailManagerPDFs()
    Dim FileName As String
    Dim ManagerName As String
    Dim AttacmentFileName As String
        
   'hides the screen updates to keep the screen from blinking
   'and help the program run faster
    Application.ScreenUpdating = False
  
    'defines array to hold names of the managers
    Dim Num As Integer
    Num = Worksheets("ManagersLookup").Cells(6, "A").Value
    Num = Num - 1 'accounts for array's starting with 0
    
    'sets up counter to use to loop through putting names in array, printing files, etc
    Dim counter As Integer
    counter = 0
    
    'puts names into array
    Dim Names() As String 'defines array
    ReDim Names(Num) 'sets array size
    Do While counter <= Num
        Names(counter) = Worksheets("ManagersLookUp").Cells(counter + 1, "B").Value
        counter = counter + 1
    Loop
          
    'sets up stuff for email
    Dim emailAddress As String
    Dim OutApp As Object
    Dim OutMail As Object


    
    'cycle through names in the arrary, make a pdf or the reportcard, and email it.
    counter = 0 'resets counter
    Do While counter <= Num
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        'passes name from array into cell
        Worksheets("ManagersReport").Cells(2, "M").Value = Names(counter) 
        
        ManagerName = Worksheets("ManagersReport").Cells(2, "M").Value


        'make PDF file and store in folder on desktop
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FileFolder & "\" & ManagerName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
  
        'pull e-mail address from cell on worksheet
        emailAddress = Worksheets("ManagersReport").Cells(3, "M").Value
        AttacmentFileName = FileFolder & "\" & ManagerName & ".pdf"


        On Error Resume Next
        
        'Change the mail address and subject in the macro before you run it.
        With OutMail
            .To = emailAddress
            .Subject = "Parts Orders Turnback YTD Manager's Report"
            .Body = "Attached is the summary of your employees' parts order turnbacks year to date."
            .Attachments.Add AttacmentFileName
            .Send
        End With
        
        Set OutMail = Nothing
        Set OutApp = Nothing
        counter = counter + 1 'update counter
    Loop
    
    'clears data from reportcard
    Worksheets("ManagersReport").Cells(2, "M").Value = ""
        
    On Error GoTo 0


    MsgBox ("Manager's PDFs have been made and sent.  Please file and/or delete the folder on your desktop.")
    
    'unhides the screen updates
    Application.ScreenUpdating = True


End Sub

Thanks for your help in advance!!
PS, i have to run to a meeting and wont be able to respond to answer questions for about 2 hours.

 

[tiastahl]

Ha! I figured out my own issue. I was so sure the problem was in the code, but the code is fine. The cell that the email address is being pulled from is empty. Someone deleted the formula that was looking up the emails from another sheet. What's interesting though is that it wasn't just making a bunch of emails with attachments and body's, but no email addresses.