Email macro formula error

[danb127]

Hello all-

I am attempting to set up an excel sheet with a macro that will send an email to a client for a specific date. I am using two workbooks, one as a client directory and one for the formulas and macro. The issue that i am having is that when the emails populate from the formula, the macro does not work, however, when i imput the email addresses manually, the macro works pefectly. any help would be greatly appreciated. Thanks!

Dan

 

[Smitty]

Welcome to the Board!

You'll need to post your code.

 

[danb127]

The formula i am using is.

=VLOOKUP(C2,'I:\CLC\[Package Email List Directory.xlsx]Sheet1'!$A$4:$C$251,3,FALSE)

and then the code that i am using for the email is

Sub Email_package()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "E").Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Package"
.Body = "Dear " & Cells(cell.Row, "A").Value _
& vbNewLine & vbNewLine & _
"Please come pick up your package " & _
"from the leasing office."
 
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

 

[danb127]

I have sorta figured out my problem. since it is using cell value it is imputing the formula into the To field, however, I am not sure how to change/fix this. Suggestions?