Outlook Email not opening from Excel Macro. Was previously working fine for months

[jpw443]

My file was previously working fine and when executing the macro an email would open up with an attachment. All of a sudden it stopped working and I can't figure out why. Receiving a Run-time error '13'" Type mismatch error code.



Attached is an image of where the code is erroring out. Below is the code:


Any suggestions on what to do as this has been working for a couple months now would be greatly appreciated!





Sub SendEmailWithAttachment()

Dim OutApp As Object

Dim OutMail As Object

Dim RecipientsRange As Range

Dim RecipientCell As Range

Dim EmailAddress As String

' Create Outlook objects

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)



Dim currentDate As String

currentDate = Format(Date, "yyyy-mm-dd") ' You can adjust the date format as needed



' Create the PDF file with the current date as the file name

Dim pdfFileName As String

pdfFileName = "F:\PRODUCTION\11 - SIM Meeting\DailyThroughputReports\" & currentDate & "-Target" & ".pdf"



Set ws = ThisWorkbook.Sheets("Email List")

Dim lastRow As Long

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Set RecipientsRange = ws.Range("A1:A" & lastRow)



' Compose the email

With OutMail



.Subject = "Daily Target Revenue Report"



.Body = "Please find attached the Daily Target Revenue Report by Profit Center for the day." & vbCrLf & _

"" & vbCrLf & _

"See Summary below:" & vbCrLf & _

"" & vbCrLf & _

"C1 = $" & Sheets("Target").Range("E12").Value & vbCrLf & _

"M2 = $" & Sheets("Target").Range("J12").Value & vbCrLf & _

"M3 = $" & Sheets("Target").Range("O12").Value & vbCrLf & _

"Kitline = $" & Sheets("Target").Range("E21").Value & vbCrLf & _

"Belco = $" & Sheets("Target").Range("J21").Value & vbCrLf & _

"Overlabel = $" & Sheets("Target").Range("O21").Value & vbCrLf & _

"Weight Count = $" & Sheets("Target").Range("E30").Value & vbCrLf & _

"Extrusion = $" & Sheets("Target").Range("O30").Value & vbCrLf & _

"" & vbCrLf & _

"Target = $" & Sheets("Target").Range("L33").Value & vbCrLf & _

"Goal = $" & Sheets("Target").Range("L32").Value & vbCrLf & _

"" & vbCrLf & _

Sheets("Target").Range("Y3").Value





.Attachments.Add pdfFileName 'Path to the PDF file



For Each RecipientCell In RecipientsRange

EmailAddress = RecipientCell.Value

If EmailAddress <> "" Then

OutMail.Recipients.Add EmailAddress

End If

Next RecipientCell



.Display

End With



' Release Outlook objects

Set OutMail = Nothing

Set OutApp = Nothing

End Sub

 

[Domenic]

Do any of the worksheet cells that you reference contain an error vallue, such as #N/A, #VALUE!, etc?