Macro to send email from Vlookup cell value [Solved/Closed]

KingTut - May 19, 2010 at 09:02 AM - Latest reply: rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen
- May 19, 2010 at 11:36 AM
Hello,

I am trying to create a macro that will among other things send an email to each address in a cell. The only problem is that the cell with the email addy contains a Vlookup formula and not the actual address. So I am wondering what I need to change in the If statement to make vb recognize or translate the vlookup into text so that the macro will send. Any help would be greatly appreciated!!



Private Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
ThisWorkbook.Save
For Each cell In Columns("I").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.CC = "email address"
.Subject = "I/C Memo"
.Body = "Hello " & Cells(cell.Row, "G") & "," _
& vbNewLine & vbNewLine & _
"I have booked " & "$" & Cells(cell.Row, "C") _
& " to Division " & C & ThisWorkbook.Sheets("Sheet2").Range("B5").Value & " " & "for " & ThisWorkbook.Sheets("Sheet2").Range("C9").Value & "." & vbNewLine & _
"Please also book this amount to your " & ThisWorkbook.Sheets("Sheet2").Range("B6").Value & "." _
& vbNewLine & vbNewLine & _
ThisWorkbook.Sheets("Sheet2").Range("C2").Value & vbNewLine & _
"Capabilities Accounting" & vbNewLine & _
ThisWorkbook.Sheets("Sheet2").Range("C3").Value
.Attachments.Add ActiveWorkbook.FullName
.ReadReceiptRequested = True
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
See more 

5 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 19, 2010 at 09:23 AM
2
Thank you
If this vlookup is in column I, then you are saying so yourself
Columns("I").Cells.SpecialCells(xlCellTypeConstants)

Shouldn't you be using xlCellTypeFormulas?

Thank you, rizvisa1 2

Something to say? Add comment

CCM has helped 1676 users this month

That was it!! I had been banging my head against the wall for 30 mins...

Such a simple error grrrr....

One more question that maybe you could answer for me?? If I want to return a value in a cell that has been formatted in the body of my message is there a certain naming convention to use?

i.e.- I have a cell with 25455.43 in it formatted to show $25,455.43.

Using the line ThisWorkbook.Sheets("Sheet2").Range("B5").Value only seems to input the original number without formatting. Is there a way to correct this easily?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 19, 2010 at 10:41 AM
In that case use .Text instead of .Value
Sorry I pasted the wrong line

"I have booked " & Cells(cell.Row, "D") _

That should have it read: I have booked $254,455.43

How do I use the .text function there?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 19, 2010 at 11:36 AM
Same thing. when you say nothing, .value is implied

"I have booked " & Cells(cell.Row, "D").text is how you would code it