Email macro formula error

danb127

New Member
Joined
Mar 7, 2011
Messages
3
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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

Code:
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
 
Last edited by a moderator:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,222,022
Messages
6,163,447
Members
451,837
Latest member
gmc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top